January 2, 2014 at 2:37 am
We have been implementing High Availability Clustering
which includes
disk mirroring (data mirroring)
hybrid clustering (using storage device)
Till now we are not using system databases(like master) in a HA cluster environment.
so my question is:
Is it good practice to use/synchronize/mirror SQL SERVER 2005/2008/2012 master database (system database) in High Availability Fail-over Cluster.
Thanks in Advance
January 2, 2014 at 4:33 am
are you referring to database mirroring?
You cannot mirror system databases. If you have installed a failover clustered instance of sql server, the system databases will be on shared drives that either node may access once it claims the cluster resources.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 2, 2014 at 9:56 pm
Thanks Perry Whittle
You mean to say we put all the system databases (like master, msdb, temdb etc)
on a shared disk and these databases are common to both the nodes.
Active node will claim these databases on a shared disk right?
January 3, 2014 at 12:56 am
darklight_arizona (1/2/2014)
Thanks Perry WhittleYou mean to say we put all the system databases (like master, msdb, temdb etc)
on a shared disk and these databases are common to both the nodes.
Active node will claim these databases on a shared disk right?
No, youre missing the point!
If you had truly deployed a windows high availability cluster and installed a failover clustered instance of sql, the system databases would also be on shared storage.
Your description above is unclear, you start by stating you have implemented a cluster but then state you are not using sql clustered Instance,
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 2:23 am
We aint using Windows Clustering its the disaster recovery solution (High Availability)
where common shared disk (e.g. SAN) are used for High Availability(same location) and the shared disk is then
synchronize with other Dr-site server (located at different location).
All the databases data files and log files are in common disk (Shared disk) For e.g D: drive where both the nodes
will access the drive when it is active and that drive is then synchronized with another nodes for Dr-site.
Here you can say that its the data mirroring. But we can fail over the cluster as well fail back if one of the
node is down. My only question should we put system databases to D: drive as well and synchronize
to Dr-Site.
Thanks once again.
January 3, 2014 at 2:33 am
So you're replicating the database storage drives at the storage level, no HA on the Windows or SQL level, correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 2:43 am
Its High Availability Cluster software solution, as it can detect hardware/software faults, and immediately restarting the application on another system without requiring administrative intervention, a process known as fail-over.
We can put number of servers in a cluster and fail-over with a minimum of down time.
You can say its a data mirroring between clustered servers which supports shared disk type clustering.
Thanks
January 3, 2014 at 3:20 am
similar to HP Polyserve then by the sound of it.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 8:33 am
Master and resource contain details of the patch level relating to the SQL Server program files and other instance-unique details.
If you did have a shared master, then you are locking both servers to the same patch level from that point forward. You cannot patch a SQL instance without having master online, and you cannot expect a SQL instance to work correctly if the program files and master db are at different patch levels. So, if you did share the master db you could never apply a service pack or a CU or a hotfix to any of the instances that use the shared master (always assuming that this configuration would work in the first place!).
You need to have instance-unique versions of master, model, resource and tempdb. One benefit of this is that all SQL instances could be up and running, but only one would have the active database files online to it. You can then run other instance-specific work (if you have any) and patch the 'offline' instances completely independently of which instance has the active databases.
In my old place (pre 2007) we used Double-Take to replicate our user databases between London and New York. One site had the databases online, while the other site had these offline. We used a DNS alias to point to the active instance, so that application connection strings could remain constant. Our failover process stopped the replication, switched which databases were online and offline, switched the DNS alias, restarted replication, and ran a few automated tests. Each month we swapped which site was live, so that we always knew that our DR process would work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply