Posts Tagged ‘SQL Server’

Using SQL Logins with “AlwaysOn” Availability Groups

I had some fun today configuring a SQL 2012 “AlwaysOn” Availability Group working with a database that was configured to use SQL Logins.  I am working with the vendor to see if we can use Integrated Authentication instead, but in the meantime I managed to get failover functional.

The problem I was experiencing was that on failover, the database users defined in the database lost their mappings to the SQL Login on the server.  I had created SQL Logins on both nodes of the Availability Group with identical usernames and passwords, but the mapping still failed.  Turns out this was happening because SQL Logins (like AD and NT accounts) have SIDs, and these SIDs are used to map database users to logins.  To correct the problem, I needed to create SQL Logins with identical SIDs on the two servers.


  1. Create SQL Logins on one node of the Availability Group and perform mappings.
  2. Lookup the SIDs of the users using the following query:
    SELECT SUSER_SID (‘[SqlLogin]‘)
  3. Script out creation of the matching logins:
    USE [master]
  4. Failover the Availability Group and verify that user mappings are working as planned.  Verify that passwords are working, too.

Another useful tidbit here is a script to re-map SQL database users to local SQL logins:

USE myDatabase
sp_change_users_login @Action=’update_one’, @UserNamePattern=’databaseUserName’, @LoginName=’SqlLoginName’;

Really I am surprised that I did not run into this problem with our SQL 2008 mirrored databases.  The problem should have been present there as well.

SQL Server 2012, Transparent Data Encryption, and Availability Groups

We are looking into using Microsoft Bitlocker Administration and Monitoring (MBAM) 2.0 to manage BitLocker in our environment. One requirement for MBAM is a SQL Server database instance that supports Transparent Database Encryption (TDE).  (Update 2013-06-04:  Microsoft now claims that TDE is “optional” with MBAM 2.0, which is nice to know.  If only they had told me this before I went to the trouble of setting up SQL 2012 Enterprise just for this project!)  Currently we also are in the process of investigating the creation of a consolidation SQL 2012 Enterprise Edition “Always On” Availability Group. I wanted to see if I could create the MBAM Recovery Database in a SQL 2012 Availability Group. This proved slightly tricky… fortunately I was able to find a decent reference here:

The trick is, you need to create SQL Certificates that on each member server of the Availability Group that have the same name and are generated from the same private key. The procedure follows…

On the first server in the group, create a SQL Master Key and Certificate by running the following code. The script will create a backup file in your SQL Server data directory. Move this file to an archival location. If you lose the file and password, you will not be able to recover encrypted databases in a disaster event:


-- Create a Master Key

-- Backup the Master Key
   TO FILE = 'Server_MasterKey'
   ENCRYPTION BY Password = 'Password2';

-- Create Certificate Protected by Master Key
CREATE Certificate SQLCertTDEMaster
   WITH Subject = 'Certificate to protect TDE key';

-- Backup the Certificate
BACKUP Certificate SQLCertTDEMaster
   TO FILE = 'SQLCertTDEMaster_cer'
   WITH Private KEY (
       FILE = 'SQLCertTDEMaster_key',
       ENCRYPTION BY Password = 'Password3'

Now create a master key on any secondary servers in the availability group, and create the same cert by using the backup file from the first step, above. You will need to copy the certificate backup files to the local server data directory, or use a network share that is accessible to the account running the script:

-- Create a Master Key
-- Backup the Master Key
   TO FILE = 'Server_MasterKey'
   ENCRYPTION BY Password = 'Password2';

-- Create Certificate Protected by Master Key
CREATE Certificate SQLCertTDEMaster
   FROM FILE = 'SQLCertTDEMaster_cer'
   WITH Private KEY (
       FILE = 'SQLCertTDEMaster_key',
       Decryption BY Password = 'Password3'

To avoid needless trouble, create your new database and add it to your availability group before encrypting the database. Once the database is created, you can initiate encryption by opening SQl Management Studio, right-clicking your database, select tasks, then select “Manage Database Encryption”. Select the option to generate the database encryption key using a server certificate. Select the certificate created above, and select the option to “set database encryption on”.

Once the database is encrypted, be sure to test availability group failover to make sure the secondary servers are able to work with the encrypted database.

VMware Performance Charts broken again – fix your connection string.

Following upgrade of our Virtual Center server to the vSphere 5 version, we have been struggling with crashing services and memory exhaustion.  Well, the server got an upgrade from 8Gb to 16Gb of RAM this am, so it is now swimming in excess memory.  Despite this, the Perf Charts in vCenter have gone dead again.

We were seeing errors similar to those described here:

We got the “”perf charts service experienced an internal error” when looking at the Performance tab for any object in the vSphere Client.  A look at the latest “vctomcat-stderr[...].log” file in “C:\Program Files\VMware\Infrastructure\tomcat\logs” reveals JDBC connection errors.  The damn service is trying to connect to the standby partner of our SQL database mirrored pair!  So where are the connection strings stored for this service.  Well, no thanks to VMware documentation, I discovered the connection string stored here:
C:\ProgramData\VMware\VMware VirtualCenter\

All I had to do was append “;failoverPartner=[hostName]” to the line starting with “url=”, then restart the tomcat service (the “VMware VirtualCenter Management WebServices” service).  Viola… performance reports are back.

Now back to fixing everything else that is broken… also known as “everything”.

SharePoint and Mirrored Databases

Un-fun thing about Windows SharePoint Services 3.0:  Support for mirrored databases.  I did my testing of SharePoint and our mirrored SQL 2008 servers using our SharePoint 2010 beta instance.  That worked great!  Unfortunately, WSS 3.0 does not have the same native support for mirroring.  Instead, we have to go though a bunch of rigmarole using SQL database aliases, some ugly SQL jobs to run the failovers (all T-SQL is ugly, if you are more used to object-oriented scripting languages), poorly documented SQL alerts to monitor mirrorg, and scheduled tasks to change the local aliases on the SharePoint web front ends. 

There is no single reference for this topic, either… just a bunch of incomplete pointers. Each one assumes that you are either an expert on SQL and know nothing about SharePoint, or that you are a SQL dunce who will have to ask the SQL DBA for help, and that you can comprehend only Sharepoint. What about the SharePoint admin who has to be the DBA because your company does not have one of those?

The most complete reference from Microsoft on SharePoint and SQL mirroring can be found here:
This is a much updated version of the doc originally published in the SharePoint team blog, and later updated and added to the TechNet Library:
This is a really poor document, though, since it spends a lot of time telling you how to set up mirroring, but only hints at how to make SharePoint deal with the new configuration.
There also is a published case study on SharePoint mirroring here:
This study is more fleshed out, but lacks details on how to create and configure SQL alerts.
Fortunately, there is a good ref on SQL alerts and database mirroring here:

While the MS guidance is good at telling you what needs to be done, it is short on the details of how to do it.  We know that we need to accomplish the following tasks to enable SharePoint database failover:

  1. On the SQL Servers, detect when any one SharePoint database has lost “Primary Mirror” status, and failover the remaining databases so that they are all running on the same host.
  2. On the SharePoint web front end servers, we need a script that will test the database server specified in the SQL Server aliases entry, and perform the following actions if it is not the current “Primary” mirror role holder:
    1. Change the alias to the current “Primary” mirror
    2. Cycle SharePoint services

Ideally, we would have the script created in step 1 also send a signal to the web front ends to perform the SQL Alias change and SharePoint services cycle.

Microsoft gives us a sample script for the database monitor and failover script (see bullt “1″, above), but they only hint the remaining scripts that will be required, and they do not teall you the best way to tie all the script together.

Here is what I have come up with so far…

T-SQL Script for Database Mirror Status Monitoring and Failover:

The following is adapted from the MS Sample script.  It has been modified to force failover of all mirrored database on the current server if any mirrored DB has failed over.

conditionalFailover.sql - J. Greg Mackinnon, 2010-04-09
SQL Script to detect condition where not all /synchronized/ mirrored databases
are in the primary role.
Script will force failover of all mirrored databases if above conditions are met.

use master;
--Declare and initialize variables
DECLARE @SyncDBCount int;
DECLARE @PrimaryDBCount int;
SET @SyncDBCount = 0
SET @PrimaryDBCount = 0

--Collect total number of mirrored databases
SET @SyncDBCount = (
  SELECT COUNT(ALL database_id)
  FROM sys.database_mirroring
  WHERE mirroring_state=4
PRINT N'Total Synchronized Mirrored Database Count: '
	+ (CAST(@SyncDBCount AS varchar(4)));

--Collect total number of databases holding the "primary" database role.
SET @PrimaryDBCount = (
  SELECT COUNT(ALL database_id) FROM sys.database_mirroring WHERE
 (mirroring_state=4 and mirroring_role=1)
PRINT N'Total Mirrored Databases holding the "Primary" state: '
	+ (CAST(@PrimaryDBCount AS varchar(4)));

-- Begin IF condition when not all synced mirror DBs are Primary.
IF ((@PrimaryDBCount > 0) and (@PrimaryDBCount < @SyncDBCount)) BEGIN
	Print N'Beginning Failover of mirrored databases...'
	FOR SELECT name FROM sys.databases WHERE database_id IN
		(SELECT database_id
			FROM sys.database_mirroring
			WHERE (mirroring_state=4 and mirroring_role=1))

		DECLARE @name varchar(255)
		DECLARE @cmd varchar(1000)
		OPEN i

		WHILE (@@fetch_status  -1) BEGIN
			IF (@@fetch_status  -2) BEGIN
				set @cmd = 'ALTER Database [' + @name + '] SET PARTNER FAILOVER;'
				PRINT N'Running command: ' + @cmd
				exec (@cmd)
			END --end if
		END --end while
END -- End IF conditional
	PRINT N'No failovers will be performed at this time.'

T-SQL Script to determine availability of a primary mirror for a given SQL Server and Database:

The following script can be run from a client system to determine if the named SQL Server and Database are online, and is the current “Primary” mirror holder.  It can be launched using the “SQLCMD.exe” utility, and requires the SQL Native Client to be present on the client system.  Syntax for launching is:

sqlcmd.exe -S [SQLHost] -i [scriptFile.sql] -v dbName=[dbName]

The script will return a text output stating that the database is in one of three possible states… “Primary”, “Secondary”, or “unknown”.  If the database or database server is not availble, then a SQLCMD error will be returned.  Here is the script:

Detect Mirroring status of a single database
Returns char with value Primary|Secondary|unknown
(detection of "noSync" has been removed as a primary with a downed partner
will also be out of sync, but that does not mean that we don't want to use
the primary)
Database to be queried can be set by changing the @DBname variable

use master;

-- Declare and initialize variables
Declare @DBname AS varchar(128);
Declare @mirState AS char(9);
Set @DBname = '$(dbname)';
-- End Declarations

-- Conditional logic to determine if @DBname is Primary or Secondary
IF (
	select mirroring_role from sys.database_mirroring where (
		database_id IN (select database_id from sys.databases where name = @DBname)
) = 1 set @mirState = 'Primary'
	BEGIN --NOTE:  THIS WILL NOT RETURN THE EXPECTED RESULT for a user in the "Public" role on the DB server.
		-- That's okay, because the "primary" switch does work as expected.
	IF (
	select mirroring_role from sys.database_mirroring where (
		database_id IN (select database_id from sys.databases where name = @DBname)
	) = 2 set @mirState = 'Secondary'
	ELSE set @mirState = 'unknown'
--End Primary/Secondary selection

-- Conditional logic to determine if database is Synchronized
/*IF (
	select mirroring_state from sys.database_mirroring where (
		database_id IN (
			select database_id from sys.databases where name = 'VMware_VCDB'
	) != 4 set @mirState = 'noSync'
--End Sync determination

-- Generate Output
Print N'Database ' + @DBname + N' has status: ' + @mirState;

 PowerShell Script to monitor validity of current DB alias, and update if no longer valid:

This script calls on the T-SQL script above to check on the status of the DB Alias, update it if necessary, and cycle SharePoint services on the web front end if the alias is updated. Be sure to update the script to use your SQL Host, Alias, and Database names before using in your environment.

#Invokes "sqlcmd" script to determine holder of current primary mirror server role for the SharePoint databases.
#Sets SQL aliases accordingly

Set-PSDebug -Strict

##### Start script variables: ###
##### End script variables: #####

##### Start script functions: ###

function mirState {
	#Determines the mirroring state of a specified database
	#REQUIRES: SQLCMD.exe in the path specified in the function,
	#	the "determinMirDBState" sql script, in the path specified.
	#	input strings "$server" (for the SQL server or instance), and "$database" (the database to test)
	#PROVIDES: returns a string with possible values Primary|notThere|stateUnknown|other
	param ([string]$server,[string]$database)

	#Build SQLCMD command:
	[string]$sqlCom = 'c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE'
	[string]$sqlScript = "c:\local\scripts\sql\determineMirDBState.sql"
	#Invoke the command and capture to string $comOut
	[string]$comOut = & $sqlCom -S $server -i $sqlScript -v dbname=$database 2>&1

	if ($comOut.contains("Primary") -eq $true) { #if RearWindow is the primary role holder then...
	# Removed "Secondary" selection as this string will not be returned in a low-privilege environment.
	#} elseif ($comOut | Select-String "Secondary") {#if RearWindow is the secondary role holder then...
	#	[string]$rc="Secondary"
	# Removed "noSync" detection as a primary with a downed partner will be out of sync, but it is not actionable.
	#} elseif ($comOut | Select-String "noSync"){ #if the mirroed database is not in sync then...
	#	[string]$rc="noSync"

	} elseif ($comOut.contains("not accessible") -eq $true) { #SQL error occured, meaning server is in some way unavailable.
	} elseif (($comOut.contains("unknown") -or ($comOut.contains("Secondary"))) -eq $true) { #db state is anything other than primary
	} else { #something else unexpected.
	return $rc
} #end mirState

function getCurServer {
	#Determines current database alias setting for the string $DBAlias.
	#Requires: An input string $DBAlias.
	#	Assumes the existance of the alias in the registry,
	#	and that the alias is using the "DBMSSOCN" (TCP/IP) connection method.
	#Returns: A string containing the host name of the current database server for the alias
	param ([string]$dbAlias)

	[string]$curAliasSetting = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo').$dbAlias
	[string]$curServer = $curAliasSetting.Replace("DBMSSOCN,","")
	return $curServer

function setAlias {
	#Sets in the regustry the host name for the provided SQL server alias
	#REQUIRES the names of the SQL host and its alias
	#PROVIDES nothing
	param ([string]$dbHost,[string]$dbAlias)
	[string]$aliasSetting = "DBMSSOCN," + $dbHost
	Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo' -Name $dbAlias -Value $aliasSetting

function cycleServices {
	#Restarts system services associated with SharePoint
	# This will expedite reconnection to the SharePoint databases in the event of a SQL mirror failover.
	& iisreset
	# We use the "if" conditional below to determine if the service is already running before attempting to restart it.
	if ((Get-Service -Name "spadmin").status -match "Running") {Restart-Service -Name "spadmin"}
	if ((Get-Service -Name "sptimerv3").status -match "Running") {Restart-Service -Name "sptimerv3"}
	if ((Get-Service -Name "spsearch").status -match "Running") {Restart-Service -Name "spsearch"}
	if ((Get-Service -Name "osearch").status -match "Running") {Restart-Service -Name "osearch"}
	Write-Output "SharePoint services cycled."
##### End script functions: #####

##### Start script body: ###
[string]$curServer = getCurServer -dbAlias $dbAlias
Write-Output "The database alias $dbAlias currently is set to: $curServer"

[string]$mirStateVar = mirState -server $curServer -database $dbName
Write-Host "Database $dbName on server $curServer is in state $mirStateVar."

if ($mirStateVar.contains("Primary")) {
	Write-Output "$curServer is already defined as the SQL Alias, and the server is functioning as expected.  Exiting."
} else {
	switch -regex ($curServer) {
		$db1 {[string]$newServer = $db2}
		$db2 {[string]$newServer = $db1}
	setAlias -dbHost $newServer -dbAlias $dbAlias
	Write-Output "Set alias $dbAlias to value $newServer."
	## Removed the "cycleServices function as the databases will get reconnected on their own.
	## You still can run "cycleServices if you want to expedite DB reconnection,
	## but keep in mind that this will cause all of the ASP.NET code to recompile.  Boo!
	#Write-Output "Cycling SharePoint services..."
	Write-Output "Done."
##### End script body: #####

Tying it all together

So now to make it all work togther…
The fastest thing to do is to implement the first T-SQL script as a SQL Server Agent job running on both SQL mirror servers. The jobs are configured to run once per minute, and have been staggered to run 30 seconds apart from each other. The script will not be triggered by databases in a “suspended”, we should be able to avoid job collision between the two servers. (I hope).

We then could implement the PowerShell script (and accompanying T-SQL script) as a scheduled task on the web front end servers. We need to run the script as an account in the PUBLIC role on the SQL servers, which can run as a batch job on the WFEs.
Easy, right? we shall see.

SQL Server 2008 – Configuration Notes For A New Mirror

With the impending release of SharePoint 2010, I have decided to try to get our backing SQL infrastructure up-to-date, and also to re-architect our system to use SQL mirroring.  This project has been easier than I expected, with a few inevitable bumps along the road:

  • The SQL documentation states that when setting up a new mirrored database connection, you must take a backup of the existing database, and restore it to the mirror server before configuring the mirror.  It is a bit vague on the particulars.  I found out the hard way that you must perform the recovery and not restore the database to a running state, otherwise the mirror setup will fail.  The worst part is that the mirroring wizard will not give a helpful error… it will claim that ‘no network endpoint is available for the mirror connection’, or some such rot, leading you to believe that you have a problem with your firewall.  So, make sure you do the following when setting up the database for mirroring:
    1. Backup the database and transaction logs separately
    2. Restore the database backup with the “WITH NORECOVER” option.
    3. Restore the transaction logs with the “WITH NORECOVER” option.
  • Concerning SQL Express:  SQL Express is approved for use as a Witness server in a SQL mirroring configuration.  However, SQL Express will not work as a Witness out of the box.  To get things running, you will need to:
    • enable the “TCP” protocol in the “SQL Server Configuration Manager” tool.
    • You also may need to configure the instance to use the static TCP port “1433” for SQL Services (also in the Configuration Manager.  On my installation, SQL Express defaulted to the use of dynamic ports for SQL, which made for a good deal of trouble in configuring the Windows firewall.
    • You may need to enable the SQL Browser service in “Configuration Manager”, and open UDP port 1434 to your management station.  Without the browser service, I could not get SS Management Studio to talk to SQL Express.  Boo
  • If configuring databases on Windows Server 2008 or 2008 R2, you will have the option to use the much-improved Windows firewall to secure your SQL instance.  I have chosen to restrict TCP access to SQL services to only those hosts that need connectivity (at least for now).  Unfortunately, SQL 2008 does not create any firewall rules or firewall rule templates for you, so you will need to do it yourself.  Here are the required ports:
    • TCP Port 1433 – For basic client/server connectivity, and remote administration
    • UDP Port 1434 – For the SQL Browser service… not needed in most cases, but required to enable connectivity to SQL Express instances in my case (see above).
    • TCP Port 5022 – The Mirroring Endpoint default port… required open on all SQL servers that will be used in a mirror configuration.  SQL clients do not require connectivity to this port… only primary, mirror, and witness servers in a cluster.
    • TCP Port 135 and Program-based access to “%ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.exe” – Required to establish SQL Server Integration Services remote connectivity.  These are DCOM-based services that use those annoying Dynamic RPC ports.  SQL clients should not be allowed to connect to this port.  I have it open only to the systems from which I plan to plan to perform SQL management operations.
  • Concerning Maintenance Plans:  MPs are made somewhat more complicated on systems using mirroring.  Clearly you want your Maintenance Plans to run regardless of where your primary mirror happens to be at any given time.  However, DBCC and backup operations can be performed only on the primary database in a mirror set.  So how do you ensure that your maintenance plans will always run on the primary?  A common solution seems to be to create identical maintenance plans for both mirror servers, but to use the “Ignore databases where the state is not online” flag in the SS Management Studio database selector dialog (“WITH SKIP” T-SQL argument).  This also brings up the question of how you can replicate those tediously configured Maintenance Plans between your different mirrors.  The answer involves SSIS packages and the SQL Server Business Intelligence Development Studio (an optional component in a SQL Server installation):
    • Use SS Management Studio to connect to “Integration Services” on the SQL instance where you have configured your maintenance plans.
    • Navigate to “Stored Packages\MSDB\Maintenance Plans”… hey look… all of your maintenance plans are just SSIS packages!
    • Right-click the plan you want to copy, then select “Export Package”.
      • Select “File System” for your package location
      • Browse to the location where you want to save your package and click “OK”.
    • You could now use the BI Development Studio to edit the package file, but the smarter thing to do is to open the file using a text editor.  I like “Notepad++”.  Find and replace all instance of the name of your source SQL server with the name of your destination SQL server.  If you fail to complete this step, your Maintenance Plans will fail with connection errors.  Why?  Because the SSIS export function preserves the local server connection strings, causing your destination system to attempt to backup databases on a remote computer instead of itself.  Boo!
    • Connect to Integration Services on the destination SQL server.  Navigate to the same “Maintenance Plans” directory, right-click and select “Import”.  Again select “File System” for the package location.  Browse to the file you just edited, and click “OK” to import.
    • Verify that the imported package is configured correctly using the Maintenance Plan editor.
    • You will need to update the schedules for your plans because schedules are not an integral part of an MP.  This is “metadata” that does not get exported.
    • Remember to repeat this procedure every time you update your MPs on either server in your mirror configuration.

Planning for SharePoint 2010

Below is a plan-in-progress of a WSS 3 to SharePoint Foundation 2010 upgrade process.

The architecture of our SharePoint farm has been in flux.  Hopefully the current plan is looking something like the final version…

Read More