Showing posts with label AG. Show all posts
Showing posts with label AG. Show all posts

Wednesday 20 March 2013

AlwaysOn Availability Groups SQL 2012

Overview:  SQL 2012 offers AlwaysON Availability Groups (AOAG)

Notes:
  • All SP2013 dbs support Synchronous Replication via AOAG.
  • Most SP2010 excludes Search, Profiles and Syn (I think).  SP2010 needs SP 1 to use SQL2012 features including AOAG.
  • Synchronous replication requires 1GB+ bandwidth and less than a 10ms latency
  • For AOAG you need Windows 2008 R2 Enterprise edition.  SQL 2012 Enterprise edition
  • Databases need to be in the Full Recovery Model to use AOAG
Download the PS to add databases to existing AG's.

AOAG only support databases that are set to use the "FULL" recovery model.  Multiple SharePoint database are set to use the "Simple" recovery model.  I haven't found anything saying MS will support switching all the databases to the "Full" recovery model.  Performance wise "Simple" runs faster but the benefit of having AOAG is generally worth the change to the recovery model.

Note: SP2010 has several databases that use a simple recovery model (Search, Profile, sync, usageandhealth dbs). 

Note: SP2010 has several databases that use a simple recovery model (4 Search dbs: Search, search_analytics, search_Crawl, search_linkstore.  5) Profile 6) Sync 7) Social 8) UsageandHealth).

Note: The listener and the cluster are created within AD and are added as Computers, this will only use the 1st 16 characters for adding the listen and the cluster, it cuts off the rest.  Make sure the listen and cluster are less than 17 chars.

Note: FS4SP I added the Administrators database for resilience.
FASTSearchAdministors (Full Recovery model) - AOAG works
FAST_ContentService (Simple)
FAST_ContentService_CrawlStore (Simple)
FAST_ContentService_PropertyStore (Simple)
FAST_QueryService (Simple)
FAST_QueryService_CrawlStore (Simple)
FAST_QueryService_PropertyStore (Simple)

Tip: I was performing backups that we corrupting my databases.  Before doing the backup and
Restore to perform the AOAG sync, delete the old backups.

References:
http://www.slideshare.net/michaeltnoel/sql-2012-alwayson-availability-groups-for-sharepoint-2013-sharepoint-connections-amsterdam-2012
http://technet.microsoft.com/en-gb/library/jj715261.aspx
http://blogs.msdn.com/b/sambetts/archive/2013/04/24/sharepoint-2013-and-sql-server-alwayson-high-availability-sharepoint.aspx

"No need for Aliases..".  Aliase have 2 purposes:
1) the database goes down, you get pointed to the alternative database.  AOAG listener does the exact same function so I agree that there is no point in using an alaise for failover.
2) SQL Alaises allow you to split up which database you are working with.  So by having let's say a single AOAG with 2 aliases that point to the same AOAG listener is perfect as later you can move the Search databaases to another AOAG instance and you don't need to re-run the config wizard. 

Minor point but I'd still use aliases on top of AOAG listeners.

The script below runs thru the databases on the 1 server in the AOAG and if they are set to use the "Simple" Recovery Model, it changes the SQL Recovery Model to "FULL".  In my case I have used AutoSPInstaller to build my SP2013 farm.  This has prefixed all my databases with AutoSP_, this allows me to make sure I am only targeting SP database for my change to the recovery model (See line 11).
 

Tuesday 8 January 2013

Create SQL Server Aliases using Powershell


Create SQL Aliases example Powershell
For DR and Moving/splitting up SQL Server load use aliases, costs you nothing and later on you can split the load.  I use 3-4 even on small SP farms.

Tip: SQL 2012 has always on availability clustering, the SQL Server listener (need for Availability Groups (AG)) does the same functions as a SQL Alias.  So my take is if you use a SQL 2012 AG then the listener on an always on availability cluster does the same function as the SQL Alias.  Obviously rather use the listeners DNS name as opposed to the IP adr of the listener but if you are using AG you don't need a SQL Alias.

Thoughts: SQL 2012 brings a new option to the table regarding SQL Aliases for SP2010 & SP2013.  If you are using Always-on Availability Groups (AP) in SQL 2013, you get a SQL listener that does the same function as as the SQL Alias.  AG gives you automatic db fail over for your Sp farm.  Issue is if you use AG with a SQL alias you have a single point of failure so your DB won't automatically fail over.

So the big reason to use SQL Aliases for me in the past was to allow me to split my database servers when 1 became the bottleneck.  The goodness with AG outweighs this option to improve performance especially as if I'm using AG I probably have sufficient resources as this is planned upfront.

Creating Registry keys safely in PowerShell:
    # Check if the key already exists - Example from AutoSPInstaller on creating aliases.   
    $client = Get-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client' -ErrorAction SilentlyContinue
    # Create the key in case it doesn't yet exist
    If (!$client) {$client = New-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client' -Force}



Tip:  Check SQL connections and SQL Aliases using a udl file.  Create a text file on your desktop, rename the .txt extension to .udl.  Open the UDL file and verify the connection string works.  I check the Alias that uses the AOAG listener, if this fails I check the connection using the listener, if this fails I check I can hook to any SQL instance.  This pretty much tells me where I have gone wrong.

Tip:  Review your SQL Alaises and cleintside neworking using the SQL Server Client Network Utility tool.  In the run window type: cliconfg