Guide to Mirroring Databases on SQL Server 2008 Enterprise

Guide to Mirroring Databases on SQL Server 2008 Enterprise

  One of the first things to make sure of is that the drive structure is the same on the principal server and the mirror server. For example, if data files are stored at S:\SqlData and log files are kept at L:\SqlLogs on the principal server, this exact same drive structure must be mimicked on the mirror server.
  Additionally, mirroring was attempted between a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise as the principal and a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise R2 as the mirror. The initial mirroring could be established and the failover could occur from the principal (SQL Server 2008 Enterprise) to the mirror (SQL Server 2008 R2 Enterprise). However, when a failover back to the principal from the mirror was attempted, the mirroring broke and could not be reestablished. The take away here is to make sure that both servers are running the exact same version of SQL Server (SQL Server 2008 Enterprise, for example).
  Lastly, keep in mind that mirroring is done at the database level as opposed to something like clustering which is done at the server level. Therefore, each database must be set up for mirroring individually.

1. Select a database for mirroring on the principal server. Right click on the database. Select ‘Tasks’ then ‘Back Up…’ Select the location to where you would like to back up the database. The backup will need to end up on the mirroring server and eventually be restored there.
2. On the mirror server in SSMS, right click on Databases underneath the server name and select ‘Restore Backup’. In the Restore Database window that opens, after you have selected a database name and the backup to be restored, click on ‘Options’ in the top left hand corner of the window. Make sure that the MDF and LDF files are named the same as they are on the principal. Also, check the ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’ option.
3. Go back to the principal server, select the same database, pick ‘Tasks’ and then ‘Back Up…’ again. On the Backup type dropdown, select ‘Transaction Log’. Make sure that the transaction log backup is in a location that can be accessed from the mirror so that it can be restored to the mirror.
4. Right click on the database on the mirror. The database should show as ‘(Restoring…)’ Select ‘Tasks’ -> ‘Restore’ -> ‘Transaction Log…’ Select the transaction log backup that you created from the principal. Select ‘Options’ in the ‘Restore Transaction Log’ window and click the radio button that states ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’. Then click the ‘OK’ button’
5. Go back to the principal server and right click on the database that you have selected for mirroring. Click ‘Tasks’ -> ‘Mirror…’ When the Database Properties window opens, click the ‘Configure Security’ button and go through the wizard.
6. If you wish to include a witness server, select the ‘Yes’ radio button in the second screen. Continue to go through the wizard. The databases that are being mirrored in this instance are all on servers on a private network behind a firewall, so DO NOT check the box for ‘Encrypt data sent through this endpoint’. Additionally, data encryption can cause failures if things are not set up properly. Unless it is absolutely necessary, do not encrypt the data.
7. The wizard will build endpoints on the principal, mirror, and witness servers. The default port for mirroring is 5022. On a Windows 2003 Server, if the Windows Firewall is not in use, no additional configuration should be necessary. However, if the Windows Firewall is in use on the server, the port may have to be specifically opened. By default on a Windows 2008 Server, all ports are locked down so 5022 will have to be specifically opened on a 2008 Servers.
8. If all goes well, there should be success messages beside each of the servers in the mirroring scheme if the endpoints get configured correctly. When the wizard concludes, select ‘Start Mirroring’ to finalize the mirroring set up. If no error messages appear, look at both the principal and mirrored databases in SSMS. The principal database should display a message stating ‘(Principal, Synchronized)’ next to the database name. The mirrored database should display a message that states ‘(Mirror, Synchronized / Restoring…)’ next to the database.

Additional Information
  If the mirrored databases are going to be used in an ASP.NET application, this information will need to be placed in the web.config file when the connection string is defined. The parameter is called 'Failover Partner' and it follows the Data Source parameter:


<add name="SqlCS" connectionString="Data Source=SqlPrincipal; Failover Partner=SqlMirror; Initial Catalog=master; Persist Security Info=False; User ID=user; Password=password123" providerName="System.Data.SqlClient" />


  Once again, Database Mirroring occurs at the database level, NOT the server level. This is important because the logins are created at the server level. Additionally, SQL Server uses a GUID (like 0xB7898239D38FA34D84F30B7404F5C67B) called an SID to identify a login, NOT the login name. So if we create the login ‘user123’ on both the principal and mirror servers and assign the same permissions to the mirrored database, when the database fails the login ‘user123’ will not work because the SIDs don't match on the servers. The following article presents a method to resolve this issue:
http://support.microsoft.com/kb/918992
On the principal server, open a new query window in SSMS and run the following query:

----------------------------------------- BEGIN QUERY ----------------------------------------------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

----------------------------------------- END QUERY -------------------------------------------------

This script will create 2 stored procedures in the master database: sp_hexidecimal and sp_help_revlogin. After the script has executed, run the following statement in a new query window:

EXEC sp_help_revlogin

The output will give you the ability to create logins with identical SIDs on the mirrored database. Here is a sample output from the script:

---- Login: User123
CREATE LOGIN [User123] WITH PASSWORD = 0x0100F6FF96F04564026C472234A73D7FD9B3CE467E6B9E066BBC
HASHED, SID = 0xCC1C8D1C6BF52640844476D7FFADB862, DEFAULT_DATABASE = [master],
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Run the above script on the mirror server and assign the same permissions to the user ID to the database that exist on the principal server and everything should work correctly.