SQL mirroring, adding nodes, clustering, witness server, SQL 2008 r2 enterprise
I will need to explain this in detail since it is really a test for me to learn. I am not sure what results I want, but I was told to set up and learn "SQL mirroring for SQL 2008 r2 enterprise. My boss has a large project in mind but it not current on SQL mirroring and wants me to get some exposure to it before we begin. What I have done so far is;
1. create 2 SQL servers and set up mirroring using "high saftey without automotic failover"
2. I did not configure a witness server, I am still looking into the benefits of that and exactly what SQL features needed to be added to a third server for that.
3. added a test database that had old data in it, but I know is a functional SQL database that was once used.
4. Mirroring seems to be working, I added the data base to both the principle and mirror servers and starting mirroring. I modified a table on the principle and it changed over on the mirror server also, but I was not able to verify until I stopped mirroring or clicked "Failover" to reverse server roles.
My first question is;
1. is there a want to check to see if the mirror database is really updating without stopping mirroring or doing a failover?
2. The second question is more complicated. My boss asked me to "try setting up a few SQL nodes and see how we can get each node to be primary for a DB and also failover for others that are primary and other nodes"
after a little research, it appears I need to create a cluster, by first going to server manager and adding the cluster feature. Am I on the right track? Does anyone know if mirroring can be done over a WAN, say a 100MB connection between 2 datacenters?
All info about mirroring is appreciated..thanks
1. create 2 SQL servers and set up mirroring using "high saftey without automotic failover"
2. I did not configure a witness server, I am still looking into the benefits of that and exactly what SQL features needed to be added to a third server for that.
3. added a test database that had old data in it, but I know is a functional SQL database that was once used.
4. Mirroring seems to be working, I added the data base to both the principle and mirror servers and starting mirroring. I modified a table on the principle and it changed over on the mirror server also, but I was not able to verify until I stopped mirroring or clicked "Failover" to reverse server roles.
My first question is;
1. is there a want to check to see if the mirror database is really updating without stopping mirroring or doing a failover?
2. The second question is more complicated. My boss asked me to "try setting up a few SQL nodes and see how we can get each node to be primary for a DB and also failover for others that are primary and other nodes"
after a little research, it appears I need to create a cluster, by first going to server manager and adding the cluster feature. Am I on the right track? Does anyone know if mirroring can be done over a WAN, say a 100MB connection between 2 datacenters?
All info about mirroring is appreciated..thanks
ASKER
ahh so based on what I am reading and what you have written, I was comparing apples and oranges. Mirroring is a totaly different from clustering and to "set up a few SQL nodes and see how we can get each node to be primary for a database and also failover for others that are primary on other nodes" is more clustering??
So is mirroring basically one on one? Only two servers are involved, the principle and the mirror? For every SQL database I need to set up a separate mirror on a mirror server?
So is mirroring basically one on one? Only two servers are involved, the principle and the mirror? For every SQL database I need to set up a separate mirror on a mirror server?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the response, now have my mirror up and running...I will have some new questions about SQL syntax though. I will open another question
First, you should evaluate your needs...and to do that you need to understand the differences between mirroring and clustering. Biggest difference is probably the level at which each provides its redundancy...mirroring provides protection/avail at the database level, but clustering provides protection at the server instance level. Another big difference is that in mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, but a SQL instance on a cluster gets a single virtual server name and IP that remains the same no matter what node of the cluster is hosting the instance.
If you need protection at the server level (i.e. multiple databases are required for application functionality, etc.), clustering is probably a more appropriate choice...but if you only need to provide availability for one database at a time, mirroring has a number of advantages.
Unlike clustering, mirroring does't require proprietary hardware and does't have a potential failure point with shared storage...mirroring also can probably bring the standby database into service much faster than any other high availability technology, and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.
Note that you can't use database mirroring within a single cluster, but you can definetly consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be aware that because a cluster failover is probably longer than the timeout value on mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.