November 26, 2014 at 1:32 am
Hi all,
I have an existing production SQL 2012 SP1 Server that I need to now add Availability Groups for HADR / Read Only Copies to. I've researched this and found that "Windows Fail-over Clustering" should be installed before SQL is installed, but in this case that would be very difficult.
The complexity of rebuilding this server is mainly due to the fact that this server is a data 'hub' of sorts with SQL Replication both inbound and outbound to / from multiple servers and even 3rd parties. The database sizes are also prohibitive as they range between 600GB and 1.1TB. Rebuilding, and consequently re-initializing, the Replication publications and subscriptions would result in downtime or service degradation that is unacceptable.
Has anyone attempted anything similar?
I'm looking for alternative methods to getting this done. Some idea's I've had involve uninstalling SQL, installing the WFC components and re-installing SQL and then restoring system DB's in order to get things back to the way they were or just installing the WFC components and re-installing SQL over the current instance (basically an 'in-place repair').
Thanks in advance,
Keith
November 26, 2014 at 5:17 am
you should be able to form the WSFC without uninstalling the existing instance.
First things to check are the OS and hardware between the existing server and its intended
failover cluster partners, are they compatible?
Once the cluster has been created enable AlwaysOn for the instance via SQL Server configuration manager
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2014 at 8:21 am
Perry Whittle (11/26/2014)
you should be able to form the WSFC without uninstalling the existing instance.First things to check are the OS and hardware between the existing server and its intended
failover cluster partners, are they compatible?
Once the cluster has been created enable AlwaysOn for the instance via SQL Server configuration manager
Hi Perry,
Thanks for the reply. From what I've read adding the WSFC components to the server after SQL has already been installed is problematic and one of the ways around it is to uninstall & reinstall SQL.
I neglected to mention in the Original Post that these servers are Hyper-V VM's, so 'hardware' compatibility isn't a problem.
Do you have any experience (good or bad) with installing the WSFC components to an existing SQL Server and setting up a Cluster and AOAG's?
Keith
November 26, 2014 at 9:01 am
jumpin (11/26/2014)
From what I've read adding the WSFC components to the server after SQL has already been installed is problematic and one of the ways around it is to uninstall & reinstall SQL.
Not one i'm aware of, do you have a link?
The only time theres a dependency here is if you're installing an FCI. A stand alone instance will have no dependency.
jumpin (11/26/2014)
I neglected to mention in the Original Post that these servers are Hyper-V VM's, so 'hardware' compatibility isn't a problem.
Ok fine, just ensure the OS versions are compatible.
jumpin (11/26/2014)
Do you have any experience (good or bad) with installing the WSFC components to an existing SQL Server and setting up a Cluster and AOAG's?Keith
No i dont i'm afraid, just setup the cluster as usual and then enable the existing instance for AlwaysOn
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2014 at 10:23 am
Being quite blunt and straight-forward with this reply. I have seen issues over and over with what you are pursuing:
1) What is your experience with windows clustering and SQL Server's Always On technologies? If they aren't very strong, you have a very high likelihood of things not going as you intend or functioning as you intend.
2) There are quite a number of windows patches and configurations to get right. Networking stack issues too. Specific SQL Server patches to apply.
3) Are you aware that reading from a secondary will modify PRIMARY ROWS adding a slot for the 14-byte version store pointer for read rows?? This leads to latching/locking/dirty pages/tlog activity (all of which also has to be sent over the wire via mirroring to be replayed on the secondary)? Oh, lets not forget the massive index fragmentation that can occur for those that use the default index fill factor of 0.
4) Do your myriad applications know how to properly interact with AGs? What will they do when the Primary gets yanked out from under them and moved to the other server?
5) Of particular concern is the various replications you have going on. Multiple forms of data synchronization can EASILY get TOTALLY HOSED UP. More i's to dot and t's to cross to have a chance of success.
6) Got any SQL Agent jobs (backups, maintenance, batch activities, etc.)? They need to be dealt with too because the database they hit may or may not be the primary on the given server.
7) Logins/users in the database? Another gotcha...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 26, 2014 at 3:40 pm
Kevin has excellent points here but to add
1) Yes, you need someone who's on the ball with Windows Server Failover Clusters, you also need to understand a great deal about the way an AlwaysOn group is handled (specifically the AO cluster resource) within the failover cluster.
2) it cant be stressed enough, you need to push your mirror traffic down a segregated pipe (network link), this removes the overhead on the Public network and ensures sufficient performance\segregation.
3) this is massive in AO and needs to be fully reaslised
4) to be fair its the same with database mirroring and log shipping, how do the apps handle a role reversal? With AO and a listener its more transparent, but still needs to be considered.
5) your choice of synch and asynch needs to be carefully realised. Remember you can only have 3 synch replicas of which only 2 can be configured for auto failover and those replicas cannot be an FCI.
6) again even with mirroring or log shipping this is an issue
7) again same with mirroring and log shipping
Don't rush into this or you'll likely regret it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 27, 2014 at 4:23 am
Hi there,
Thanks for the response so far. Below are the answers to your questions:
TheSQLGuru (11/26/2014)
1) What is your experience with windows clustering and SQL Server's Always On technologies? If they aren't very strong, you have a very high likelihood of things not going as you intend or functioning as you intend.
I have built quite a few FCI's on SQL 2008 (R2) and 1 or 2 AOAG's in SQL 2012 in the past, but always from a clean slate.
TheSQLGuru (11/26/2014)
2) There are quite a number of windows patches and configurations to get right. Networking stack issues too. Specific SQL Server patches to apply.
This is exactly why I asked the question. Most of my builds have been pretty straight forward in the past. I prefer to keep things as simple as possible.
TheSQLGuru (11/26/2014)
3) Are you aware that reading from a secondary will modify PRIMARY ROWS adding a slot for the 14-byte version store pointer for read rows?? This leads to latching/locking/dirty pages/tlog activity (all of which also has to be sent over the wire via mirroring to be replayed on the secondary)? Oh, lets not forget the massive index fragmentation that can occur for those that use the default index fill factor of 0.
This is news to me. I haven't read anywhere that reading from 'read only copies' would make changes to the primary. I assumed they would work similar to 'read only' snapshots.
TheSQLGuru (11/26/2014)
4) Do your myriad applications know how to properly interact with AGs? What will they do when the Primary gets yanked out from under them and moved to the other server?
We are not looking to use this particularly for High Availability or Failover, but more as an 'online backup' where the data would still be available for reading if the primary goes done. In the past my 'apps' have just connected to the listener and worked happily.
TheSQLGuru (11/26/2014)
5) Of particular concern is the various replications you have going on. Multiple forms of data synchronization can EASILY get TOTALLY HOSED UP. More i's to dot and t's to cross to have a chance of success.
Exactly why I don't want to either break what's already in place or try and redo it all.
TheSQLGuru (11/26/2014)
6) Got any SQL Agent jobs (backups, maintenance, batch activities, etc.)? They need to be dealt with too because the database they hit may or may not be the primary on the given server.
I already have a solution for this.
TheSQLGuru (11/26/2014)
7) Logins/users in the database? Another gotcha...
I already have a solution to this too.
Again, thanks for your input, it is appreciated.
Keith
November 27, 2014 at 6:17 am
jumpin (11/26/2014)
From what I've read adding the WSFC components to the server after SQL has already been installed is problematic and one of the ways around it is to uninstall & reinstall SQL.
Got a link for this at all?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 27, 2014 at 6:50 am
This is news to me. I haven't read anywhere that reading from 'read only copies' would make changes to the primary. I assumed they would work similar to 'read only' snapshots.
You are correct! Unfortunately you apparently aren't aware that the snapshotting you mention to give you a "read only" consistent picture of being-modified data is done exactly as I described. Each row is put in the version store in tempdb as copy-on-write and a 14-byte pointer is added to each row in the base table so reader(s) can get to the original row(s).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 27, 2014 at 6:51 am
Perry Whittle (11/27/2014)
jumpin (11/26/2014)
From what I've read adding the WSFC components to the server after SQL has already been installed is problematic and one of the ways around it is to uninstall & reinstall SQL.Got a link for this at all?
This is one: Common SQL Server AlwaysOn Gotchas
I've read it in other places too, just can't find them right now.
November 27, 2014 at 7:57 am
I see nothing in there that provides any reasonable evidence.
In an ideal scenario you would deploy the cluster before installing your instances, but a standalone instance of SQL Server has no dependency on WSFC, the only time a SQL server instance has a dependency is when deploying FCIs.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 1, 2014 at 12:17 am
Thanks Perry.
I'm going to build a 'Test Bed' and try it out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply