{"id":1574,"date":"2012-02-03T04:42:08","date_gmt":"2012-02-03T04:42:08","guid":{"rendered":"http:\/\/www.obieta.com\/?p=1574"},"modified":"2012-02-03T04:42:08","modified_gmt":"2012-02-03T04:42:08","slug":"how-to-setup-mirroring-in-sql-server","status":"publish","type":"post","link":"http:\/\/obieta.com\/?p=1574","title":{"rendered":"How to Setup Mirroring in SQL Server ?"},"content":{"rendered":"<p><strong><span style=\"color: #ff0000;\">What is Mirroring in SQL Server ?<\/span><\/strong><\/p>\n<p>Database mirroring is an alternative high-availability solution to failover clustering in SQL Server. Database mirroring supports\u00a0<strong>automatic failover<\/strong>, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.\u00a0<strong><span style=\"color: #004080;\">In this Article, we will implement Mirroring with witness Server and will Automatic Failover too.<\/span><\/strong><\/p>\n<p><span style=\"color: #ff0000;\"><strong>What is the Hardware and software requirement for implementing Mirroring in SQL Server ?<\/strong><\/span><\/p>\n<p>There is no any such hardware and software requirement for implementing. Even SQL Server Standard Edition supports Mirroring. For More Details about feature supported by various editions, please check my previous post, &#8220;<a href=\"http:\/\/sqlserver-training.com\/does-sql-server-standard-edition-supports-mirroring\/-\"><strong>Does SQL Server Standard Edition Supports Mirroring?<\/strong><\/a>&#8221; To test the Automatic failover, you need three SQL Server Instances.<\/p>\n<p>In case there is a firewall in between Principal, Mirror and Witness Server, please ensure there endpoint ports are Opened in firewall.<\/p>\n<p><strong><span style=\"color: #ff0000;\">How to implement Database Mirroring in SQL Server 2008 R2?<\/span><\/strong><\/p>\n<p>Implementing SQL Server Mirroring is quite simple in SQL Server 2008 R2. For better understanding, Lets take an real business requirement and deploy mirroring with WITNESS Server (Automatic Failover)<\/p>\n<p>I have already posted an\u00a0<a href=\"http:\/\/sqlserver-training.com\/how-to-set-up-database-mirroring-in-sql-server-2008-video\/-\">Video Post<\/a>, where a step by step implementation of mirroring is available, but you need to have silver light installed in your system to watch this video and a good bandwidth.<\/p>\n<p>In this Article, I am going to implement a Mirroring using three SQL Server Instances, where<\/p>\n<ol>\n<li>Principal Server<\/li>\n<li>Mirror Server<\/li>\n<li>Witness Server<\/li>\n<\/ol>\n<p><strong><span style=\"text-decoration: underline;\">Principal Server Details<\/span><\/strong><\/p>\n<ul>\n<li>SQL Server Instance Name :\u00a0<strong><span style=\"color: #004080;\"><em>&lt;SERVER_NAME&gt;<\/em>SQL1<\/span><\/strong><\/li>\n<li>Version :\u00a0<span style=\"color: #004080;\">SQL Server 2008 R2<\/span><\/li>\n<li>Edition :\u00a0<span style=\"color: #004080;\">Evaluation Copy<\/span><\/li>\n<li>Database Name :\u00a0<span style=\"color: #004080;\">Test_SQLServer_Mirroring<\/span><\/li>\n<\/ul>\n<p><strong><span style=\"text-decoration: underline;\">Mirror Server Details<\/span><\/strong><\/p>\n<ul>\n<li>SQL Server Instance Name :\u00a0<strong><span style=\"color: #004080;\"><em>&lt;SERVER_NAME&gt;<\/em>SQL2<\/span><\/strong><\/li>\n<li>Version :\u00a0<span style=\"color: #004080;\">SQL Server 2008 R2<\/span><\/li>\n<li>Edition :\u00a0<span style=\"color: #004080;\">Evaluation Copy<\/span><\/li>\n<li>Database Name :\u00a0<span style=\"color: #004080;\">Test_SQLServer_Mirroring<\/span><\/li>\n<\/ul>\n<p><strong><span style=\"text-decoration: underline;\">Witness Server Details<\/span><\/strong><\/p>\n<ul>\n<li>SQL Server Instance Name :\u00a0<strong><span style=\"color: #004080;\"><em>&lt;SERVER_NAME&gt;<\/em>SQL3<\/span><\/strong><\/li>\n<li>Version :\u00a0<span style=\"color: #004080;\">SQL Server 2008 R2<\/span><\/li>\n<li>Edition :\u00a0<span style=\"color: #004080;\">Evaluation Copy<\/span><\/li>\n<\/ul>\n<p>*<strong>Note <\/strong>: In this example, all these instances are named instances hosted on a single operative case. In case you are implementing in your production environment, All these three roles should be on different machines.<\/p>\n<p><strong><span style=\"color: #004080; font-size: small;\">We are going to perform following Steps to setup Mirroring and automatic Failover Testing<\/span><\/strong><\/p>\n<ol>\n<li>Backup Primary Database (Principal Server)<\/li>\n<li>Using latest backup files, Restore database on Mirror Server with NORECOVERY Option<\/li>\n<li>Set up Mirroring using Wizard where we will define\n<ol>\n<li>Identify of Principle Server, Mirror Server and Witness Server<\/li>\n<li>Create End Points for Principle Server, Mirror Server and Witness Server<\/li>\n<li>Configure Security<\/li>\n<\/ol>\n<\/li>\n<li>Start Mirroring<\/li>\n<li>Test Manual Failover<\/li>\n<li>Test Automatic Failover<\/li>\n<\/ol>\n<p><span style=\"font-size: small;\"><strong><span style=\"color: #004080;\">STEP 1 <\/span><\/strong>&#8211;\u00a0 Backup Primary Database (Principle Server)<\/span><\/p>\n<p>On Principal Server Perform a full backup and copy this backup file to mirror server for restore. Backup is quite simple, which can be taken using the following Script on Principal Server (.<strong>SQL1<\/strong>)<\/p>\n<pre>-- This will Backup Database named Test_SQLServer_Mirroring to C:tempMirroringTEST.bak file\nBACKUP DATABASE [Test_SQLServer_Mirroring] TO  DISK = N'C:tempMirroringTEST.bak' WITH NOFORMAT, NOINIT,  NAME = N'Test_SQLServer_Mirroring-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10\nGO<\/pre>\n<p><span style=\"font-size: small;\"><strong><span style=\"color: #004080;\">STEP 2 <\/span><\/strong>&#8211;\u00a0 Restore Database on Secondary Database (Mirror Server)<\/span><\/p>\n<p>On Mirror Server, Restore the database with same name with NORECOVERY option on Server\u00a0<strong>.SQL2<\/strong>Restore is quite simple, which can be taken using the following Script or by SSMS<\/p>\n<pre>-- Restore Database name Test_SQLServer_Mirroring] on Mirror Server (.SQL2) with NORECOVERY option\nRESTORE DATABASE [Test_SQLServer_Mirroring] FROM  DISK = N'C:tempMirroringTEST.bak' WITH  FILE = 1,  MOVE N'Test_SQLServer_Mirroring' TO N'C:tempMirroringSQL2_Test_SQLServer_Mirroring.mdf',  MOVE N'Test_SQLServer_Mirroring_log' TO N'C:tempMirroringSQL2_Test_SQLServer_Mirroring_1.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 10\nGO<\/pre>\n<p><span style=\"font-size: small;\"><strong><span style=\"color: #004080;\">STEP 3 <\/span><\/strong>&#8211; <\/span>Set up Mirroring using Wizard where we will Identify of Principle Server, Mirror Server and Witness Server, Create End Points for Principle Server, Mirror Server and Witness Server and will also configure Security<\/p>\n<p>Open SSMS and connect to Principal Server\u00a0<strong>&gt;&gt;&gt; <\/strong>Database, which you wanted to enabled for mirroring<strong>&gt;&gt;&gt; <\/strong>right click\u00a0<strong>&gt;&gt;&gt; <\/strong>and Select Properties<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image70.png\"><img loading=\"lazy\" title=\"Setting up SQL Server Mirroring\" src=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image_thumb72.png\" border=\"0\" alt=\"Setting up SQL Server Mirroring\" width=\"331\" height=\"484\" \/><\/a><\/p>\n<p>which will open up a Database Properties Box like this and Click on Mirroring<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image71.png\"><img loading=\"lazy\" title=\"SQL Server Database Properties Mirroring\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/540x484ximage_thumb73.png.pagespeed.ic.c6uEP1YIiM.jpg\" border=\"0\" alt=\"SQL Server Database Properties Mirroring\" width=\"540\" height=\"484\" \/><\/a><\/p>\n<p>Once you are on Mirroring Properties page, select &#8220;<strong><span style=\"color: #008000;\">Configure Security\u2026<\/span><\/strong>&#8221; button, which will open a<strong><span style=\"color: #008000;\">Configure Database Mirroring Security Wizard<\/span><\/strong><\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image72.png\"><img loading=\"lazy\" title=\"Configure Database Mirroring Security Wizard\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484ximage_thumb74.png.pagespeed.ic.Pj8gLA2W3E.jpg\" border=\"0\" alt=\"Configure Database Mirroring Security Wizard\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p>Next screen, will ask you, do you want to include Witness Server or not ? Witness Server is useful to initiate Automatic failover. As per our requirement,\u00a0<span style=\"color: #008000;\">we will be opting to include Witness Server, so we will Select<strong>YES<\/strong><\/span><\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image73.png\"><img loading=\"lazy\" title=\"Mirroring include Witness Server or not \" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484ximage_thumb75.png.pagespeed.ic.45aVAAXoWF.jpg\" border=\"0\" alt=\"Mirroring include Witness Server or not \" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p>Next screen will ask you, where you wanted to save the configuration, In case we opted for Automatic Failover \/ Witness Server, configuration must be saved at\u00a0<strong>Witness Server<\/strong>. In case, we do not want to include Witness Server and would like to to do Manual failover, then we can have option for saving Configuration either on Principal Server or on Mirror Server.<\/p>\n<p>As per our requirement (Automatic Failover), we will opt for\u00a0<strong>Witness Server Instance.<\/strong><\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image74.png\"><img loading=\"lazy\" title=\"SQL Server Mirroring where to save the configuration\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484ximage_thumb76.png.pagespeed.ic.cGHegnR5gk.jpg\" border=\"0\" alt=\"SQL Server Mirroring where to save the configuration\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p>Next Screen where we need to specify information about the SQL instance where the database was originally located. In out example this is\u00a0<strong>.SQL1<\/strong><\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/SQLServerMirroringspecifyinformationabouttheSQLinstancewherethedatabasewasoriginallylocated.jpg\"><img loading=\"lazy\" title=\"SQL Server Mirroring - specify information about the SQL instance where the database was originally located\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484xSQLServerMirroringspecifyinformationabouttheSQLinstancewherethedatabasewasoriginallylocated_thum.jpg.pagespeed.ic.RiI_OeefAC.jpg\" border=\"0\" alt=\"SQL Server Mirroring - specify information about the SQL instance where the database was originally located\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p>Here we have option to specify the TCP \/ IP port on which endpoint will be listening to requests. In case there is a firewall in between Principal and Mirror Server, please ensure there these ports are Open.<\/p>\n<p>In our example, all these three instances are hosted on a single physical box that the reason we will opt for different port for Mirror and Witness Server End point.<\/p>\n<p>&nbsp;<\/p>\n<p>Adding to this we have an option to encrypt data, which travel between principal, mirror and witness server. In case we opt for this, we assume that you are already have certificates, otherwise, this will work without Certificates too.<\/p>\n<p><span style=\"color: #006a00;\">So In Select Principal Option, we specified\u00a0<strong>.SQL1 <\/strong>as Server name and used Windows Authentication to connect to principal server, which has sysadmin privileges. We also checked the Encrypt Data option and 5022 is the Port number which we used for Principal endpoint.<\/span><\/p>\n<p><span style=\"font-size: small;\">Next Screen where we need to specify information about the SQL instance where the mirror copy of the database will be located, in our example this is\u00a0<strong>.\/SQL2<\/strong><\/span><\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/SQLServerMirroringspecifyinformationabouttheSQLinstancewheremirrorcopyofthedatabasewillbelocated.jpg\"><img loading=\"lazy\" title=\"SQL Server Mirroring - specify information about the SQL instance where mirror copy of the database will be located\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484xSQLServerMirroringspecifyinformationabouttheSQLinstancewheremirrorcopyofthedatabasewillbelocated1.jpg.pagespeed.ic.OlgbbIOHcO.jpg\" border=\"0\" alt=\"SQL Server Mirroring - specify information about the SQL instance where mirror copy of the database will be located\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p><span style=\"color: #006a00;\">So In Select Mirror Option, we specified\u00a0<strong>.SQL2 <\/strong>as Server name and used Windows Authentication to connect to principal server, which has sysadmin privileges. We also checked the Encrypt Data option and 5023 is the Port number which we used for Mirror endpoint.<\/span><\/p>\n<p><span style=\"font-size: small;\">Next Screen where we need to specify information about the SQL instance that monitors the status of the principal and mirror server instances<\/span><\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/SQLServerMirroringspecifyinformationabouttheSQLinstancethatmonitorsthestatusoftheprincipalandmir.jpg\"><img loading=\"lazy\" title=\"SQL Server Mirroring - specify information about the SQL instance that monitors the status of the principal and mirror server instances\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484xSQLServerMirroringspecifyinformationabouttheSQLinstancethatmonitorsthestatusoftheprincipalandmir1.jpg.pagespeed.ic.c6fNYghWB9.jpg\" border=\"0\" alt=\"SQL Server Mirroring - specify information about the SQL instance that monitors the status of the principal and mirror server instances\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p><span style=\"color: #006a00;\">So In Select Witness Server Option, we specified\u00a0<strong>.SQL3 <\/strong>as Server name and used Windows Authentication to connect to principal server, which has sysadmin privileges. We also checked the Encrypt Data option and 5024 is the Port number which we used for Mirror endpoint.<\/span><\/p>\n<p>Next Screen will actually create these endpoint in the respective Server<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/SQLServerMirroringConfigureEndpointinPrincipalMirrorandWitnessServer.jpg\"><img loading=\"lazy\" title=\"SQL Server Mirroring - Configure Endpoint in Principal,Mirror and Witness Server\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484xSQLServerMirroringConfigureEndpointinPrincipalMirrorandWitnessServer_thumb.jpg.pagespeed.ic.SOx1IftqX0.jpg\" border=\"0\" alt=\"SQL Server Mirroring - Configure Endpoint in Principal,Mirror and Witness Server\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p>This is Work in progress status, Once this is being configured and running, you will get a confirmation status as shown below.<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/ConfigureEndpointinPrincipalMirrorandWitnessServer.jpg\"><img loading=\"lazy\" title=\"Configure Endpoint in Principal,Mirror and Witness Server\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/531x484xConfigureEndpointinPrincipalMirrorandWitnessServer_thumb.jpg.pagespeed.ic.6JAPzMvP0v.jpg\" border=\"0\" alt=\"Configure Endpoint in Principal,Mirror and Witness Server\" width=\"531\" height=\"484\" \/><\/a><\/p>\n<p><span style=\"font-size: small;\"><strong><span style=\"color: #004080;\">STEP 4 <\/span><\/strong>&#8211;\u00a0 Start Mirroring<\/span><\/p>\n<p>Once this is Done, you are ready to start the mirroring, the wizard, will itself ask you to do that, as shown in the following screen shot<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image75.png\"><img loading=\"lazy\" title=\"Start SQL Server Mirroring after endpoints\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/644x196ximage_thumb77.png.pagespeed.ic.L-8IBEfAMU.jpg\" border=\"0\" alt=\"Start SQL Server Mirroring after endpoints\" width=\"644\" height=\"196\" \/><\/a><\/p>\n<p>Once, you click on\u00a0<strong>YES<\/strong>, this will implement the mirroring and will take some time to revert back to old screen, Please wait, as this wait is depends on changes which has been performed on Principal Server after full backup. This could take 30 minutes too.<\/p>\n<p>Once this check all configuration and synchronized data, you will get the\u00a0<strong>YES <\/strong>button enabled as shown below.<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/SQLServerMirroringEstablished.jpg\"><img loading=\"lazy\" title=\"SQL Server Mirroring Established\" src=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/SQLServerMirroringEstablished_thumb.jpg\" border=\"0\" alt=\"SQL Server Mirroring Established\" width=\"539\" height=\"484\" \/><\/a><\/p>\n<p>Once, this is Done, you mirroring setup is completed. We can verify that by checking database status connecting to Principal and Mirror Instance via SSMS. We will get a status like below<\/p>\n<p><strong>Database Status at Principal Server<\/strong><\/p>\n<p>Database status would be\u00a0<strong><em>Principal and Synchronized<\/em><\/strong>, is (Role,Status) as shown below in the screen shot.<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image76.png\"><img loading=\"lazy\" title=\"SQL Server Mirroring, Principal Database Staus (Pricipal and Synchronized)\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/378x484ximage_thumb78.png.pagespeed.ic.ji7mShOGqH.jpg\" border=\"0\" alt=\"SQL Server Mirroring, Principal Database Staus (Pricipal and Synchronized)\" width=\"378\" height=\"484\" \/><\/a><\/p>\n<p><strong>Database Status at Mirror Server<\/strong><\/p>\n<p>Database status would be\u00a0<strong><em>Mirror, Synchronized and restoring<\/em><\/strong>, is (Role,Status) as shown below in the screen shot.<\/p>\n<p><a href=\"http:\/\/sqlserver-training.com\/wp-content\/uploads\/image77.png\"><img loading=\"lazy\" title=\"SQL Server Mirroring, Mirror Database Staus (Mirror, Synchronized and restoring)\" src=\"http:\/\/1-ps.googleusercontent.com\/x\/www.sqlserver-training.com\/sqlserver-training.com\/wp-content\/uploads\/343x484ximage_thumb79.png.pagespeed.ic.pUOPgN5vhA.jpg\" border=\"0\" alt=\"SQL Server Mirroring, Mirror Database Staus (Mirror, Synchronized and restoring)\" width=\"343\" height=\"484\" \/><\/a><\/p>\n<p>This is quite lengthy post, so Post Check Implementation and Failover Testing (Step 5 and Step 6) I will be posting in next article.<\/p>\n<p>Credits:<\/p>\n<p><a href=\"http:\/\/www.sqlserver-training.com\/how-to-setup-mirroring-in-sql-server-screen-shots\/-\">http:\/\/www.sqlserver-training.com\/how-to-setup-mirroring-in-sql-server-screen-shots\/-<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>What is Mirroring in SQL Server ? Database mirroring is an alternative high-availability solution to failover clustering in SQL Server. Database mirroring supports\u00a0automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.\u00a0In this Article, we will implement Mirroring with witness Server and will Automatic Failover too. What [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[6,1],"tags":[],"_links":{"self":[{"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/1574"}],"collection":[{"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1574"}],"version-history":[{"count":0,"href":"http:\/\/obieta.com\/index.php?rest_route=\/wp\/v2\/posts\/1574\/revisions"}],"wp:attachment":[{"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1574"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1574"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/obieta.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1574"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}