ProblemI have a need to setup SQL Server Database Mirroring in my environment. I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring? Check out this tip for a basic look at how to setup this SQL Server feature.
SolutionIn this tip I am going to outline my environment and then walk through the process of setting up Database Mirroring. This will include the configurations, backups, restores and verification process. Let's jump in.
My test environment consists of two separate VM's running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.
I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.
BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';
BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn';
Below are the two files in the file system:
RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' WITH FILE = 1, MOVE N'TestMirror_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', NORECOVERY, NOUNLOAD, STATS = 10;
RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;
Select "No", then click "Next >" to continue the process.
The next screen will give you options to configure the Principal Server Instance:
Click the "Next >" button to continue.
The next screen will give you options to configure the Mirror Server Instance:
Click "Next >" and you'll see the Service Accounts screen.
If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.
Since my service accounts are using the same domain account, I'll leave this blank.
Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.
Next screen that pops up should be the Start/Do Not Start Mirroring screen:
For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.
Next, click "Start Mirroring" as shown below.
Both servers should be listening on the same port. To verify this, run the following command:
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT state_desc FROM sys.database_mirroring_endpoints;
ALTER ENDPOINT <Endpoint Name> STATE = STARTED AS TCP (LISTENER_PORT = <port number>) FOR database_mirroring (ROLE = ALL);
SELECT role FROM sys.database_mirroring_endpoints;
SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE FROM sys.server_permissions SP , sys.endpoints EP WHERE SP.major_id = EP.endpoint_id ORDER BY Permission,grantor, grantee;