March 28, 2016 at 5:19 am
So our new environment has a SQL failover cluster in the Primary data center and then another failover cluster in our Secondary data center. The idea being if a server goes down, it fails over to a new cluster node. If the entire data center goes down, we bring up the servers in the secondary DC. Good so far, right?
But as part of a good DR we need to get our SQL jobs replicated over to the secondary DC. Doing it initially isn't a problem, but I want to make sure each change, each new job and each deleted job ends up copied over at the secondary DC each time the change happens. People tend to forget to duplicate effort, so I'd prefer an automated solution to this.
Every article I find on Google talks about using Availability Groups to replicate the jobs, but we are not using AG between the data centers. So no go on those solutions.
I'm wondering if I could Snapshot or Merge Replication on MSDB to accomplish this.
Has anyone ever done something like this before? Any thoughts, recommendations, or article links I can examine?
March 28, 2016 at 9:17 pm
Brandie Tarvin (3/28/2016)
So our new environment has a SQL failover cluster in the Primary data center and then another failover cluster in our Secondary data center. The idea being if a server goes down, it fails over to a new cluster node. If the entire data center goes down, we bring up the servers in the secondary DC. Good so far, right?But as part of a good DR we need to get our SQL jobs replicated over to the secondary DC. Doing it initially isn't a problem, but I want to make sure each change, each new job and each deleted job ends up copied over at the secondary DC each time the change happens. People tend to forget to duplicate effort, so I'd prefer an automated solution to this.
Every article I find on Google talks about using Availability Groups to replicate the jobs, but we are not using AG between the data centers. So no go on those solutions.
I'm wondering if I could Snapshot or Merge Replication on MSDB to accomplish this.
Has anyone ever done something like this before? Any thoughts, recommendations, or article links I can examine?
You said that the new environment is a SQL failover cluster. If this is correct, there is no need to worry about SQL Agent Jobs. All databases in the instance are included in the failover cluster including MSDB (which is where the job definitions are actually stored). Hence, when a failover occurs, the content of MSDB on the SQL instance in the secondary DC are automatically up to date. The way that SQL Server clustering works is that the disk(s) used to store the databases must be on drives that move to the other node in the cluster when a failover occurs. When a failover occurs, Windows Cluster Service moves all resources to another node (these resources include the disk drive, ip addresses etc) and then starts the services on that node. Hence, when the failover occurs there is no need to worry about whether the SQL Agent job on the other node is up to date - it is actually the same job as on the node that failed because the same MDB and LDB files for MSDB are no used on the node that is being started.
If you are using Availability Groups (and from your message, I think that you are not), then the you need at least 2 separate SQL instance that are in the same Windows cluster and are not actually in a SQL Server cluster. System databases such as MSDB cannot be included in an Availability Group. Additionally, if you have SQL Agent jobs, you would also need to include additional logic in the job so that it does not attempt to run on a node that it is not supposed to run on (e.g. only run it on the node that has the primary copy of the database(s) in the AG.). Any code in these jobs should refer to name of the AG, not the name of either SQL Server instance.
You cannot replicate system databases (including MSDB) so Snapshot or Merge replication are no options.
March 29, 2016 at 3:48 am
happycat59 (3/28/2016)
You said that the new environment is a SQL failover cluster. If this is correct, there is no need to worry about SQL Agent Jobs.
That's not true. While this theory holds true for a single cluster, it doesn't hold true for my entire scenario.I have 2 clusters, one in each DC. I need the primary DC jobs over in the secondary DC, which is part of a different cluster. Therefore, I do need to worry about the SQL Agent Jobs.
And no, we are not using AG.
happycat59 (3/28/2016)
You cannot replicate system databases (including MSDB) so Snapshot or Merge replication are no options.
So what are my other options?
March 29, 2016 at 3:01 pm
So you've setup two completely independent clusters not a geocluster?
March 31, 2016 at 9:12 am
PretendDBA (3/29/2016)
So you've setup two completely independent clusters not a geocluster?
Never heard of the term geocluster before.
Yes, these are two independent clusters. Each on their own data center.
March 31, 2016 at 3:31 pm
You might be best to destroy your second cluster and setup a geo-cluster now called multi site clusters in Microsoft speak. Assuming your storage vendor has the required plugins and you have the ability to do storage replication.
Multi-site clustering will do everything you want, remove your dependence on a single DC etc but without all the other hassles you have with trying to sync agent jobs etc as it will be the one single instance of SQL Server. It's now a pretty mature product M$ I think have officially supported geo clustering since Windows 2008. It works pretty well in Windows 2012 R2 with the dynamic quorum features etc that have greatly improved it's functionality and stability.
April 4, 2016 at 6:18 am
PretendDBA (3/31/2016)
You might be best to destroy your second cluster and setup a geo-cluster now called multi site clusters in Microsoft speak.
Please understand that I am looking for options within my current setup. Not options that require us to change our current setup.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply