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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Thor2923,

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.

Thor2923Author Commented:
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?
Correct.  Typically, when mirroring is used with clustering, the principal server and mirror server both reside on clusters, with the principal server running on the failover clustered instance of one cluster and the mirror server running on the failover clustered instance of a different cluster. You can establish a mirroring session in which one partner resides on the failover clustered instance of a cluster and the other partner resides on a separate, unclustered computer.

Here's information setting up a Failover Cluster, hopefully this article will shed some light:



Microsoft only supports a maximum of 10 mirrored databases per instance on 32-bit systems, these restrictions do not apply on 64-bit SQL Server systems and the SQLCAT team has just released a whitepaper showing that it’s possible to mirror in excess of 100 databases on a single SQL Server instance (http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx)  The article is definitely worth a read if you plan to mirror multiple databases on a 64-bit instance of SQL Server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thor2923Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.