{"id":191,"date":"2012-06-13T19:49:37","date_gmt":"2012-06-13T19:49:37","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=191"},"modified":"2014-03-05T20:00:26","modified_gmt":"2014-03-05T20:00:26","slug":"steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases\/","title":{"rendered":"Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases"},"content":{"rendered":"

In this post, I am going to outline my environment and then walk through the process of patching mirrored servers.<\/p>\n

My test environment consists of two SQL Server 2005 SP2 servers named SSQL1 (principal) and SSQL2 (mirror) that contain eleven mirrored databases. The database mirroring operating mode is set to asynchronous and I’m upgrading to SP4.<\/p>\n

Here is a look at the two mirrored instances.<\/p>\n

\"SQL<\/a><\/p>\n

Step 1
\nAlways backup all system and user databases before applying patches.<\/p>\n

Step 2
\nRemote Desktop into the “Mirror” server (SSQL2 in our example) and download\/copy the patch to the server.<\/p>\n

Step 3
\nStop all SQL Services on the “Mirror” server.<\/p>\n

Step 4
\nRun the patch on the “Mirror” server.<\/p>\n

Step 5
\nOnce the patch is complete, reboot the “Mirror” server.<\/p>\n

Step 6 (optional)
\nIf your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first. To do this we will need to issue the following statement for every database on the “Principal” server that is mirrored.<\/p>\n

\n
ALTER DATABASE databasename <\/em>SET SAFETY FULL<\/pre>\n<\/div>\n
Here are the commands for the 11 databases on my server.<\/p>\n
\"SQL<\/a>Step 7
\nThe databases might change to “synchronizing” while the transactions catch up. Once all of the databases show “synchronized”, as shown below, we can perform the manual failover.
\n\"SQL<\/a><\/p>\n
We can perform the failover using the following statement on the principal server for each database:<\/p>\n
\n
ALTER DATABASE databasename <\/em>SET PARTNER FAILOVER<\/pre>\n<\/div>\n
Here are the commands for the 11 databases on my server.<\/p>\n
\"SQL<\/a>
\nWe can now see that the servers have switched roles.<\/p>\n
\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In this post, I am going to outline my environment and then walk through the process of patching mirrored servers. My test environment consists of two SQL Server 2005 SP2 servers named SSQL1 (principal) and SSQL2 (mirror) that contain eleven mirrored databases. The database mirroring operating mode is set to asynchronous and I’m upgrading to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,100],"tags":[7,23,44],"class_list":["post-191","post","type-post","status-publish","format-standard","hentry","category-database-mirroring","category-high-availability","tag-high-availability","tag-mirroring","tag-service-packs"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/191","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=191"}],"version-history":[{"count":3,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/191\/revisions"}],"predecessor-version":[{"id":199,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/191\/revisions\/199"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}