Sunday, 13 November 2011

SQL Server Disaster Recovery to a Server with a Different Name Part 1– Logins

Being a DBA that values job security, you take regular backups of your databases. But have you really thought through how you will restore the backup and connect it to your application if the original server is lost in a disaster?

A common approach is to have some form of ‘standby instance’, on a server that has a different name and IP address. In the event of a disaster, the databases can be restored to the standby server and clients redirected. This may be automated using Log Shipping or Database Mirroring.

The problem is that a database doesn’t exist in a bubble. There is a heap of stuff going on at the instance level that you need to hook up to. This is stored in the ‘master’ database – the heart of the instance.

(Note: SQL Denali introduces ‘contained databases’, which offer an alternative approach.)

According to MSDN, the master database

…. includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

Restoring master onto a server with a different name isn’t a supported option, so it is important to have a method of recreating all the bits you need.

Let’s start by looking at logins, database ownership and default databases.  You may consider keeping logins up-to-date on the standby server by creating them at the same time as you create the login on the primary server.  However, even the most rigorous change control systems sometimes fail. You could automate the transfer of logins using the SSIS transfer logins task.  However, this has major disadvantage as it does not transfer passwords and will not synchronize role memberships. Finally, no matter what the approach, you can’t set the default database for a user until the database is actually online.

So, the final safeguard is to have all your logins scripted, so you can rebuild your logins at the same time as you restore your databases.

A good order for recovery is:
  1. Create the logins, without the default database;
  2. Restore the database (or, if using log shipping or mirroring, bring the database online);
  3. Set the default database for the login;
  4. Set the owner for the database;
Microsoft Support has published scripts that help with the scripting of logins. Here are much adapted versions, which filter out some logins that should not be transferred. These only work for SQL 2005 and above.

First,  create a stored procedure, sp_transfer_logins,  that scripts Windows and SQL Logins. It will not transfer logins based on certificates or asymmetric keys.

This will give an output similar to this for Windows logins:
-- Login: CORP\service-application 
CREATE LOGIN [CORP\service-application] FROM WINDOWS
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'bulkadmin '
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'dbcreator'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'diskadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'processadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'securityadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'serveradmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = N'setupadmin'
EXEC master..sp_addsrvrolemember @loginame = 'CORP\service-application', @rolename = 'sysadmin'
For SQL logins, the output will be similar to
-- Login: application
CREATE LOGIN [application] WITH PASSWORD = 0x01007618BD634A64BC0F162BD0D9B7EDA78EF461FF30CF93D893 HASHED, SID = 0x6A5A98F69F2503478556EC8A90DEA78B
ALTER LOGIN [application] WITH PASSWORD = 0x01007618BD634A64BC0F162BD0D9B7EDA78EF461FF30CF93D893 HASHED
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'bulkadmin '
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'dbcreator'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'diskadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'processadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'securityadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'serveradmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = N'setupadmin'
EXEC master..sp_addsrvrolemember @loginame = 'application', @rolename = 'sysadmin'
The procedure output contains both CREATE and ALTER statements, as your logins may already exist on your standby instances. You need to set CHECK_POLICY=OFF before you reset a hashed password and then turn it back on again if necessary.

Next, create a stored procedure, sp_transfer_logins_defaultdb, which scripts the default databases for Windows and SQL logins. The output is similar to


ALTER LOGIN [MYDOMAIN\service-application] WITH DEFAULT_DATABASE = [applicationdb]
ALTER LOGIN [application] WITH DEFAULT_DATABASE = [master]
Finally, create a stored procedure, sp_transfer_dbowner, which scripts the database owners. This has output like:

ALTER AUTHORIZATION ON DATABASE::[ApplicationDB] TO [CORP\service-application]

Now, these stored procedures should be scheduled and the output sent to a location, such as a file share on the DR machine.


It is tempting to set up a SQL Agent Job step that runs the SP as a T-SQL job step that send outputs to a file on a remote share:




However, the output is rather unhelpfully littered with [SQLSTATE 01000] messages.


ALTER AUTHORIZATION ON DATABASE::"ApplicationDB" TO "CORP\service-application" [SQLSTATE 01000]

An alternative approach is to create the job step as a CmdExec task that calls SQLCMD to run the SP:
sqlcmd -E -S LOCASERVER\DAIT -d master -Q "exec master.dbo.sp_transfer_login" -o "\\REMOTESERVER\Disaster Recovery Scripts\LOCALSERVER_INSTANCE_sp_transfer_login.sql"


Repeat for the other two stored procedures, schedule to run nightly, and your scripts will be available on your DR server for when the disaster hits!





Wednesday, 26 October 2011

Displaying Hidden Attributes in Active Directory Administrative Center

Active Directory Users and Computers for Windows Server 2003 does not display all of the properties of an object. For example, the employeeType attribute of the User object is not visible. Custom VB scripts that allow editing can be added to the power menu for an object but this requires the script to be present on each of the clients that use Active Directory Users and Computers.

Windows 2008 improves on this by integrating an Attribute Editor into the both Active Directory Users and Computers and Active Directory Administrative Center. In Active Directory Users and Computers, it must be enabled by selecting Advanced Features from the View menu.


You can then right click on an object, select Properties and you will see an additional Attribute Editor tab that shows the attributes that are not normally visible.


However, when I tested, the Attribute Editor tab was did not when I viewed the properties from the results of a Find:


The Attribute Editor is also accessible through the Active Directory Administrative Center, the all-new tool delivered with Windows 2008 R2, provided you have added the Extensions sections to the Properties Page:


To use these features on a client computer, you must install Remote Server Administration Tools (RSAT). These are only supported on Vista and Windows 7.


References AD Your Way (adding scripts to context menus in ADUC) Built-in Attribute Editor in Windows Server 2008

Technet: What's new in Active Directory Domain Services in Windows 2008

Remote Server Administration Tools (RSAT).

Thursday, 20 October 2011

Custom Backup Monitoring with Policy-Based Management and ExecuteSQL()

Configuring backups is possibly the most important task a DBA can perform. But a backup that ran perfectly yesterday and today may, for all sorts of reasons, misbehave tomorrow. To be confident that you have at least a fighting chance of recovering your database, you need a monitor your backups.
A simple approach is to have an agent job that executes a stored procedure daily and sends you the results through Database Mail. The stored procedure could list all databases that haven’t had a backup in, say, the last 24 hours. This is perfectly satisfactory if you only manage a small number of instances, but lacks scalability.

SQL 2008 introduces the Central Management Server, which allows a single action, such as executing a T-SQL command, to be performed against multiple servers. SQL 2008 also brings us Policy-Based Management, which allows the properties of many SQL Server facets (less technically known as things) to be tested, monitored and managed through a policy. The @LastBackupDate property of the Database facet bills itself as returning ‘The date and time when the database was last backed’. Feed this into a condition that expects @LastBackupDate >= DateAdd(‘hh’,-25,GetDate()), wrap it in a policy and you have the makings of a multi-server monitoring solution (see Ashish Kumar Mehta on for a blow-by-blow guide.) Throw in some automation and reporting using the Enterprise Policy Management Framework and you have a solution.

Or do you? I’ve written previously about how rows are written to msdb.dbo.backupset with is_snapshot = 1 whenever the VSS framework is invoked to make a database quiescent. In my case, this happens whenever whole-VM backups are performed by Netbackup 7.1 for VMWare.  I wanted my monitoring to disregard these backups, as I wouldn’t use them if I was performing a point-in-time recovery. However, the @LastBackupDate property of the Database facet is evaluated through a fairly straightforward query on the msdb.dbo.backupset table and makes no distinction between VSS and non-VSS backups. (And even if you are happy with the way @LastBackupDate works, eventually you’re sure to find another property that doesn’t behave how you want, or have a monitoring need that just isn’t covered by the supplied facets). If only we could write our own custom property or facet … but that option just isn’t available.

What we can do, however, is write a custom condition that can call our own piece of T-SQL, wrapped in an ExecuteSQL() function. ExecuteSQL() isn’t covered in the official documentation, but there are a number of blogs (some listed below) that describe how to use it.

To solve my particular problem, I proceeded like this:
Create a new policy and select New Condition


Name the condition and scope it to the Database facet. This means that the condition will be evaluated against each database in the instance.

Here is the magic. Create a new expression and in Field column enter:
Here we see the ExecuteSQL() function. The first argument specifies which datatype will be returned, the second argument is the SQL that will be executed. As the SQL will executed once for each database, DB_NAME() is used to capture the current database context. The ISNULL logic is there simply to deal with the possibility that the database has never been backed up.
Complete the expression by saying that the datetime value returned must be no more than a day old:clip_image001[10]
Finally, configure which databases you want the policy to be evaluated for and set the evaluation mode to ‘On-Demand’:

Once you have the policy configured, you can then run it against multiple instances registered with a Central Management Server:
Of course, this is still a manual evaluation. If you wish to automate the evaluation of policies against multiple targets, take a look at the Enterprise Policy Management Framework.

Policy-based management combined with Central Management Servers allow can be used to monitor the state of multiple servers. The ExecuteSQL() function allows custom conditions to be created when the pre-supplied monitoring facets are not suitable.

MSDN - Central Management Servers
MSDN - Policy-Based Management
Enterprise Policy Management Framework.

Sunday, 9 October 2011

When is a SQL backup not a SQL backup?

Like many, I back up SQL databases to a disk area every evening, to be picked up by tape backup software (Netbackup) in the early hours of the next day.  It's a straightforward disk-to-disk-to-tape system.

Monitoring of the disk-to-disk part is simple: I use a simple SQL script to determine whether databases have been backed up in the last 24 hours. The query looks something like this:

SELECT name,
MAX (backup_finish_date) as last_backup_date FROM
(SELECT * from master.sys.databases
WHERE state_desc !='OFFLINE'
AND name NOT IN (select secondary_database from msdb.dbo.log_shipping_secondary_databases)
AND name != 'tempdb') SD
(SELECT database_name, backup_finish_date from msdb..backupset where type = 'D') bs
ON bs.database_name =
WHERE BS.Is_Snapshot <> '1'
HAVING MAX(backup_finish_date) < GETDATE()

I encapsulate this into an stored procedure, which then runs through a SQL Agent Job and sends the results out through database mail. It's crude, so I'm moving to a solution that uses the Policy Based Management framework, but I'll come back to that later.

I created a fresh SQL 2008 R2 instance on a Windows 2008 R2 server, running as a VMWare virtual machine. This is the first time I'd run SQL in a virtual machine; all my other SQL Servers are physical.

The virtual machines are backed up using Netbackup for VMWare 7.1. This performs a 'whole-VM' backup every night. I hadn't yet configured the disk-to-disk SQL backups, but my backup script was reporting that databases were being backed up - and at around the same time as the 'whole-VM' backup was running. Could Netbackup for VMWare be doing something unexpected?

A closer look at the 'backupset' and 'backmediafamily' tables give some clues. Here is the query:

msdb.dbo.backupset.is_snapshot, AS backupset_name
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date 

On my new virtual servers, this returns rows for master, model and msdb with device_type = 7 and is_snapshot = 1. Disk-to-disk backups should generate rows with device_type = 2 (disk) and is_snapshot = 0.

So, it looked like my rogue backups were something to do with snapshots.

Microsoft KB 951288 reveals:

Consider the following scenario. You install Microsoft SQL Server on a volume. You use a Volume Shadow Copy Service (VSS) application to back up files on the volume. In this scenario, SQL Server records a backup operation in the backupset history table. This problem occurs even if you did not actually back up the database files of SQL Server. 

So, it's VSS. What's VSS?  Mr Backup provides a solid overview. Basically, SQL provides a VSS writer (known as SQL Writer) that will quiesce SQL before a snaphot is taken, allowing an open SQL instance and databases to be backed up in a consistent manner. You can see the SQL Writer by querying the installed VSS writers.

vssadmin list writers

Writer name: 'SqlServerWriter'
  Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
  Writer Instance Id: {2ffe3778-7cdb-47bb-8d86-d01084b4316c}
  State: [1] Stable
  Last error: No error

One of the first things Netbackup for VMWare does prior to backing up the VM is to take a VMWare snapshot. The snapshot enables a 'point-in-time' image of the entire virtual machine to be backed up. If you are using vSphere 4.1 and Windows 2008, VMware calls the SQL Writer VSS provider (and any other application-level VSS writers) as part of the snapshot process (see VMWare note 1028881).  This ensures that SQL is in a consistent state on the backup image, so that you don't have to worry about crash recovery of SQL if you restore the VM.

As I saw, SQL Server regards this as a 'proper' backup and records it in the backupset table. But what I really want to know is whether my disk-to-disk backup worked, not whether the VSS writer has been called as part of a VMWare snapshot. It's just a simple modification to my script:

SELECT name,
MAX (backup_finish_date) as last_backup_date FROM
(SELECT * from master.sys.databases
WHERE state_desc !='OFFLINE'
AND name NOT IN (select secondary_database from msdb.dbo.log_shipping_secondary_databases)
AND name != 'tempdb') SD
(SELECT database_name, backup_finish_date from msdb..backupset where type = 'D') bs
ON bs.database_name =
WHERE BS.Is_Snapshot <> '1'                /* Added a filter on Is_Snapshot */
HAVING MAX(backup_finish_date) < GETDATE()

That's all well and good if you 'roll your own' monitoring solution. But what if you use a monitoring tool, from either Microsoft or a third-party? My advice is to run SQL Profiler to work out exactly what the tool is reporting as a 'proper' backup.

The bad news - Policy Based Management for SQL does not take account of Snapshot backups in the provided facets. The good news - you can 'roll-your-own' condition to work around this. I'll describe how in a later post.


Wednesday, 12 January 2011

Locked out of SQL Server 2008 Express

I was approached by somebody who had installed SQL Server Express 2008 R2 in Mixed Mode, provisioned an 'sa' account and then promptly forgot the password.

As 2008 does not automatically place BUILTIN/Administrator into the sysadmin group, I had no obvious way in to reset the password and unlock the 'sa' account.

The solution was to start SQL using the '-m' flag, which places the instance in Single User. This allows anybody who is a local administrator on the server to connect with sysadmin rights.

Simple when you know how....


Thursday, 6 January 2011

Remote Desktop Connection Manager

Like many, I have to connect to and manage a large number of Windows servers. A colleague introduced me to Remote Desktop Connection Manager - a neat way of storing and categorising all of your RDC connections, as well as applying properties to groups, such as credentials or gateways.

More information can be found at this Microsoft Blog, or download it here.

Wednesday, 5 January 2011

Sharepoint 2010 Installation - Least Privilege Accounts

A while ago, I performed a quick farm installation of Sharepoint 2010 Standard on top of Windows 2008 R2 and SQL 2008 R2, all on the same server. I followed the instructions at and  

Three accounts were used:

  • My account, which is a domain account and local admin on the server. This was used for the installation.
  • A SQL Server service domain account (let's call it SQLService).
  • A Farm service account (let's call it SharepointFarmService).
The installation completed, but the Sharepoint Health Analyzer later complained that

  • Critical: The Farm Service account is being used for Search and Web Analytics Data Processing;
  • Warning: Built-in account are being used for SPTracev4 and SPSearchv4.

So, it was time to take a harder look at the documentation to see how I could improve the installation procedure.

Dan Holme, in an article on Sharepoint Pro Connections points out that installation account should be not be my account, but a separate account. He notes that the installation account becomes the db_owner on Sharepoint farm config and Central Admin content DBs, and that the config wizards won't run if that user is removed or disabled.

Enhancement 1: Create an installation/admin account (let's call it SPAdmin). Grant dbcreator and securityadmin to the account in SQL. Make the account a Local Administrator on the server.

Connect to the server as your newly-minted installation account, and run the installer.

After the installer completes, you are launched into the Configuration Wizard, where it asks for the 'Database Access Account'. Here, give the credentials of the Farm Service account.

Enhancement 2: Use a different service account for Sharepoint services.

The Initial Farm Configuration Wizard allows a different account to be specified for a number of services:

Create a new service account (e.g. SharepointServices) and specify it in the 'Create new managed account' options. 

In the Initial Farm Configuration Wizard, choose to 'Skip' the creation of the top-level web-site. This allows you to interpret the report of the Health Analyzer knowing that issues aren't related to any web sites that you have created.

Once complete, the Health Analyzer complains that 'The server farm account should not be used for other services.'

Enhancement 3: Use a new service account for Web Analytics.

Create a new service account for Web Analytics (e.g SharepointAnalyticsService). In Central Administration -> Security -> General Security --> Configure New Managed Accounts, register the new service account as a managed account. In Central Admin -> Security -> Configure Service Accounts, associated the account with the Web Analytics Data Processing Service. 

The Health Analyzer also complains that 'Built-In Accounts are used as application pool or service identities'.

Enhancement 4: Use a different service account for the Tracing Service

SPTraceV4 is the tracing service. A new service account cannot be assigned through Central Admin. Janis Norvelis has posted a solution using Powershell here, and there is a similar solution at

Create a new Domain account, e.g. SharepointTrace and place it in the Performance Log Users group on the (all?) Sharepoint servers. Add it as a managed account in Sharepoint (see above). Then run this Powershell script.
       # Get the tracing service.
$farm = Get-SPFarm
$tracingService = $farm.Services | where {$_.Name -eq "SPTraceV4"}  

# Get the "farm" managed account.
$managedAccount = Get-SPManagedAccount "DOMAIN\SharepointTrace"

# Set the tracing service to run under the managed account.
$tracingService.ProcessIdentity.CurrentIdentityType = "SpecificUser"
$tracingService.ProcessIdentity.ManagedAccount = $managedAccount

# This actually changes the "Run As" account of the Windows service.

$tracingService.Update() # Not sure if this line is needed, but it did not hurt.

Enhancement 5: Use a different service account for Search

SPSearch4 is the Foundation Search Service, used for indexing the help system. It is configured to run as 'Local Service'.

Create a new service account (e.g. SharepointSearch) and register as a managed account (see above).

In Central Admin -> Security -> Configure Service Accounts, associated the account with the Sharepoint Foundation Search service.

To be tidy and consistent, also change the Sharepoint Server Search Service to use the new managed account. 

Go to Central Admin -> System Settings -> Manage Services on Server to check whether 'Sharepoint Foundation Help Search' is running and start it if necessary.

If you wait a while for the Health Analyzer to re-run (or manually kick off rule processing), then the warnings and errors about accounts should now be gone!