{"id":785,"date":"2012-12-20T20:18:17","date_gmt":"2012-12-20T19:18:17","guid":{"rendered":"http:\/\/blog.repsaj.nl\/?p=785"},"modified":"2013-05-24T07:41:43","modified_gmt":"2013-05-24T06:41:43","slug":"sp2010-whoops-i-mirrored-all-of-my-databases","status":"publish","type":"post","link":"http:\/\/blog.repsaj.nl\/index.php\/2012\/12\/sp2010-whoops-i-mirrored-all-of-my-databases\/","title":{"rendered":"SP2010: Whoops! I mirrored all of my databases&#8230;"},"content":{"rendered":"<p>So you&#8217;re building a SharePoint farm which has to have a high percentage of availability. And to get that, you decide to make your databases highly available by the techniques offered by SQL Server. Think mirroring, or AlwaysOn if you&#8217;re using SQL2012. Which technique you&#8217;re using doesn&#8217;t really matter; you have to realise that this is not supported for all databases!<\/p>\n<p>The good news; the databases which don&#8217;t support mirroring aren&#8217;t vital to keeping your sites up. It&#8217;s these three:<\/p>\n<ul>\n<li>Usage and Health Data Collection service application Logging database (typically called \u201cWSS_Logging\u201d)<\/li>\n<li>User Profile service Synchronization database (Typically known as \u201cSync DB\u201d)<\/li>\n<li>Web Analytics Staging database (typically called \u201cWebAnalyticsServiceApplication_StagingDB\u201d)<\/li>\n<\/ul>\n<p><!--EndFragment-->Also note that search databases don&#8217;t have to be mirrored, because search has it&#8217;s own high availability mechanism. It&#8217;s doesn&#8217;t matter though, when you do have then mirrored. It&#8217;s just not nessessary.<\/p>\n<p>Ok. But you didn&#8217;t consider this when you were building your farm. Eventually, you&#8217;ll run into problems; most likely when applying updates to your farm. These will fail with the message that mirroring is not supported for your databases. So then what?<\/p>\n<p>Well, just use this Powershell script below and you&#8217;ll be able to change the alias for the databases which don&#8217;t support mirroring. Make sure that you first create the alias on <span style=\"text-decoration: underline;\">all of your farm servers<\/span>!<\/p>\n<pre class=\"\u201dprettyprint\">$alias = \"sql-alias\"\r\n\r\n$uh_name = \"Usage and Health Data Collection\"\r\n$uh_database = \"SP2010_UsageAndHealthDB\"\r\n$wa_name = \"Web Analytics Service Application\"\r\n$wa_database = \"SP2010_WebAnalyticsStagingDB\"\r\n$up_name = \"User Profile Service Application\"\r\n$up_database = \"SP2010_UPSSyncDB\"\r\n\r\nWrite-Host \"Changing the database server for $uh_name\"\r\n$uh_app = Get-SPServiceApplication |? { $_.Name -eq $uh_name }\r\nSet-SPUsageApplication -Identity $uh_app -DatabaseServer $alias -DatabaseName $uh_database\r\n\r\nWrite-Host \"Changing the database server for $wa_name\"\r\n$wa_app = Get-SPServiceApplication |? { $_.Name -eq $wa_name }\r\n$stagingDbs = \"\"\r\nSet-SPWebAnalyticsServiceApplication -Identity $wa_app -ListOfStagingDatabases $stagingDbs\r\n\r\nWrite-Host \"Changing the database server for $up_name\"\r\n$db = Get-SPDatabase |? { $_.Name -eq $up_database }\r\n$db.ChangeDatabaseInstance($alias)<\/pre>\n<p>Source for non supported databases: <a href=\"http:\/\/mmman.itgroove.net\/2012\/03\/some-databases-are-too-ugly-for-the-mirror\/\">http:\/\/mmman.itgroove.net\/2012\/03\/some-databases-are-too-ugly-for-the-mirror\/<\/a><\/p>\n<p><strong>Update 24-5-2013: <\/strong>it seems the above script leaves you with a database reference for the WebAnalyticsStagingDB. This causes exceptions with health monitoring rules which check these references for good database health. This can be easily solved by running Get-SPDatabase, selecting the correct one and issuing a Delete() command on it. This will remove the reference, making sure the health rules won&#8217;t check it again. This only happens for that single database, it seems the other ones are being cleaned up properly.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>So you&#8217;re building a SharePoint farm which has to have a high percentage of availability. And to get that, you decide to make your databases highly available by the techniques offered by SQL Server. Think mirroring, or AlwaysOn if you&#8217;re using SQL2012. Which technique you&#8217;re using doesn&#8217;t really matter; you have to realise that this<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[34],"tags":[45,7,47],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3KFR1-cF","_links":{"self":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/posts\/785"}],"collection":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/comments?post=785"}],"version-history":[{"count":0,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/posts\/785\/revisions"}],"wp:attachment":[{"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/media?parent=785"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/categories?post=785"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.repsaj.nl\/index.php\/wp-json\/wp\/v2\/tags?post=785"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}