Have you ever had one of those moments where the User Profile Sync is set to import a property from AD and you desperately need to get the old values back but don’t want to restore the entire User Profile Service?
This SQL script can be used to extract the properties from a restored copy of the User Profile DB without needing to attach it to SharePoint.
SELECT upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value,upv.SecondaryVal AS SecondaryValue
FROM [Temp-UPSA].dbo.UserProfile_Full AS upf INNER JOIN
[Temp-UPSA].dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
[Temp-UPSA].dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID
WHERE pl.PropertyID = 11
Change the value of the “PropertyID” in the last line to the value from the PropertyList table (PropertyID 11 = Office)
Please note that Microsoft doesn’t support directly accessing or updating SharePoint databases, however in this case we are extracting from a restored database only. The next step is to get the data back into SharePoint using PowerShell.
SQL Server is something that most people in IT roles will touch one way or another in the course of their working lives. It is the engine behind many line of business systems, SharePoint, ERP and CRM systems.
Having the basic skills to correctly install, configure, backup, recover and troubleshoot SQL Server issues is important and something that is often just assumed. Over the past 16 or so years I have seen many examples where this assumption is wrong. To make matters worse, troubleshooting is often focused in the wrong areas leading to time consuming and expense solutions to often simple problems.
A few things everyone should know about SQL Server:
- How disks should be configured when performance is required
- Databases and Log files should be on dedicated disks
- How to setup a maintenance plan and why you should
- SQL Agent service defaults to manual startup
- How to backup
- How to recover to a point in time
- The difference between Full and Simple Recovery models
- How to use SQL Profiler to identify poor SQL code
- Basic SQL queries
- Licensing options and SQL Server Editions
This is by no means a complete list, but is a good step in the right direction for anyone who manages SQL Server environments.
I first touched SQL Server back in the early 1990’s with version 4.21 and since then I have use ever version right up to the current 2008R2 edition. Recently I decided to formalise this experience and become a Microsoft Certified Technology Specialist: SQL Server 2008 Implementation and Maintenance. This was a rewarding experience and I learnt many new and useful things.
If are you are going to certify, I recommend reading “The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit” and a bit of hands on experience to pass this exam.
The 70-432 exam is oriented towards IT Pro’s, but I think it would look great on the CV of a developer, SharePoint consultant or specialist for any SQL Server based application.
70-432 Certification Home Page
Many applications use Microsoft MSDE or SQL Express instances to store application specific data. The default file location is usually somewhere on the C: drive, which may seem harmless until they start consuming valuable disk space. It is possible to move the databases without reinstalling the applications using this method:
- Install SQL Management Studio Express (or use SQL Management Studio if you have it)
- Locate the physical files associated with the database by Right Clicking the database in SQL Management Studio and choosing properties. Note the database and log paths may be different.
- Stop any services and websites associated with the database
- Right Click the database and choose “Detach”
- Move the database and associated log file to a new location (.MDF and .LDF)
- In SQL Management Studio, right click Databases and choose “Attach” and choose the database file (.MDF)
- Restart the services and websites stopped above
Because the application itself communicates with the database engine, moving the location of the data files doesn’t require any reconfiguration of the application itself. I have used this method to successfully move WSUS, SCE, BackupExec and WSS databases. This method works for all versions of SQL not just MSDE or SQL Express.
Note that it is a good idea to keep Databases and Logs on independent sets of disks for performance and recoverability. It is also a good idea to ensure busy databases are not stored on the system disk.
Download SQL Management Studio Express
The premium edition of Essential Business Server 2008 (EBS) includes an additional Windows 2008 Standard Server licence and SQL 2008 standard license. While the three servers that make up the core of EBS are 64bit, the forth server can be either 32bit or 64bit. Here are a few ways this server can be used to enhance the network environment.
With the included Windows 2008 Standard server license you get rights to run a single Virtual Instance of Windows 2008 Standard. The physical server can be installed as a Hyper-V host for a virtual server running a second instance of Windows. Additional Windows licenses can be purchased to run additional virtual machines.
SQL 2008 Standard Edition is a powerful database management system that can be used to host a wide range of SQL applications e.g. Microsoft Dynamics, Sharepoint content databases, SAP etc. It also features reporting, data analysis and replication technology and is a great platform for managing data company wide and making that data accessible.
SQL 2008 Home Page
In some environments it might be desirable to have a 32bit Windows server e.g. to support legacy 32bit only applications or devices such as printers that don’t have a suitable 64bit driver. Either a 32bit physical operating system or a 64bit instance running Hyper-V with a 32bit virtual machine can be useful options to get around these issues.
Client Access Licenses
When adding new users to EBS 2008 via the EBS Management Console you can choose between standard and premium client access licenses. If you have users who don’t require access to the forth server you can assign a standard CAL to those users.
EBS 2008 Edition Comparision
How many times have you had to upgrade a server and run around making changes to login scripts and group policies. How about migrating users to a new terminal server or migrating applications to a new SQL server. DNS aliases can be used to simplify these tasks and with a little bit of thought prevent the need to ever make those changes again.
File Server example
In this example we have a existing file server FS-1 and a new file server FS-2. We will create an alias called FILESERVER
Disable Strict Name checking on both file servers (needed to allow connection to SMB shares):
- Edit HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
- Add a REG_DWORD DisableStrictNameChecking = 1
- Restart the server so the setting takes affect
In DNS create a CNAME record called FILESERVER and point the CNAME at the DNS A record for FS-1.
You can now change login scripts and GPO’s to connect to FS-1 in two ways. Note this works for file shares and shared printers too:
Now lets assume you want to replace FS-1 with a new server FS-2. Simply move the data and create shares on FS-2 and when your’re ready to swap servers change the FILESERVER CNAME to point to FS-2. No changes to login scripts or GPO’s.
Before cutting over to FS-2 you can use a hosts file on a PC to connect to shares and test your shares.
The same concept can be used in many places including aliases for SQL databases, Sharepoint, mail servers, web servers and many applications that use TCP/IP to communicate.
This is a good way to present “friendly” server names to users while maintaining names that are meaningful to the IT team.
You can have multiple CNAME records pointing to the same server. Useful for application specific DNS aliases e.g. a SQL server running several databases could have CNAME for each databases so that if one is moved to another server in the future the CNAME can follow.