Solved

SQL 2008 DB mirroringQuestion:

Posted on 2011-02-16
12
279 Views
Last Modified: 2012-05-11
Hello Experts,

I am setting up DB mirroring, with Principal Server (with W2K8 R2 OS and SQK 2008 64 bit) being a physical server - Blade G7 (high spec with 2 CPU, 6 cores each and 96 GB RAM), and a Mirror server to be set-up in VM (ESX 4.0), a witness in physical box (DL 320).

Following are the DB;s in existing three servers that need to be migrated to this new DB mirroring.
First server:  1 instance with  2 x DBs (High usage) SQL 2008 on W2K3 64 bit OS
Second server: 1 x Sharepoint farm instance with 30 DB's runnning SQl 2005 Ent on W2K3 OS.
Third server: 5 instances with a total of 15 DB's, running SQL 2005 Ent on W2K3 OS

My question is can I have multiple instances set-up in DB mirroring in order to migrate above Instances/DB's to my new DB mirroring? if so what are the limitations and pre-caution(s) I need to undertake?

Please advise.

Thanks and Regards

0
Comment
Question by:goprasad
  • 6
  • 5
12 Comments
 
LVL 11

Expert Comment

by:Máté Farkas
Comment Utility
You can migrate all of your instances and databases into one mirror server but you have to install at least one Standard and one Enterprise instance because the partners of the mirroring session must be the same edition.
For example Standard can be mirrored only with another Standard server, but the version can be different (2008 can be mirrored with 2005 if the database compatibility level is 90).
I recomend that install on the mirror box a SQL 2008 64bit Standard and SQL 2008 64bit Enterprise and it will can be the mirror all of your databases.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
Comment Utility
Can it be done?  Yes, but not easily, and I would ask whether db mirroring is the best fit for your business requirements.  Mirroring is great if you've got a single database per instance, but it's going to get very complicated with as many databases as you've referenced.

Let's say you have an application that connects to several of your databases, all residing on one principal instance.  If there is a database failure, and the mirrored instances takes over, will your application have problems talking to two different SQL instances?

That is only one of the considerations to be made, and it can actually be handled by using trace events, and then automating your own failover:
http://www.mssqltips.com/tip.asp?tip=1564

Take a look at the above post, along with these two, and ask yourself whether a single principal/mirror instance is really the optimal solution for as many databases are you are referencing:
http://www.code-magazine.com/Article.aspx?quickid=080083
http://www.mssqltips.com/tip.asp?tip=1701

0
 

Author Comment

by:goprasad
Comment Utility
At this stage I have one application DB and MOS2007 SharePoint Farm DB's. MS have indicated that support to SQL 2005 is going to end in April this year. We have 2 processor SQL 64 bit enterprise license that we purchased for application DB, we just want to utilise the licensing benefit and add Sharepoint DB's, which are currently hosted in SQl 2005.
Can we have multiple instances in DB mirroring? say default instance for application Db and named instance for Sharepoint DB's?
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
0
 

Author Comment

by:goprasad
Comment Utility
Currently we have MOS1, MOS2 and Index server as web front end servers for Sharepoint farm. When we set-up DB mirroring, do we then need to have:
three web front-end for Principal and three web front-end for Mirror?
Please advise.
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
I don't believe you have to mirror them, but the redundancy of these components is definitely recommended.  

I have not mirrored a sharepoint farm, so I can't give more intuitive advice, but take a look at these links.  This one gives a couple example models for the mirrored sharepoint farm:
http://technet.microsoft.com/en-us/library/cc748824.aspx

This one has an interesting section regarding redundancy in a sharepoint farm, and it specifically says that the index server cannot be redundant.  See page 7, the 'Redundancy' and 'Database Availability':  http://www.spguru.com/Whitepapers1/MOSS%20Farm%20Architecture.pdf



0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:goprasad
Comment Utility
Witness server, I am planning to install sql 2008 express edition, will this be OK?
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
>>SQL Express and the Workgroup Edition can be used as a witness server, but they cannot be used as a partner server in database mirroring.

http://technet.microsoft.com/en-us/library/cc917680.aspx
0
 

Author Comment

by:goprasad
Comment Utility
Considering that physical server (principal server) is a high spec blade server and Mirror server (Virtual server) sitting in the same geographical location, and a very good bandwidth, can I have 6 x named  instances and DB's under those mirrored? Out of those 6 instances, SharePoint 2007 farm instance is the one which has most number of DB's , the rest of the 4 instances have 1 to 3 DB's.

Is this possible?




0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
Your hardware is good, that is not in question.  Given the number of instances you are referring to, and the fact that you've got a sharepoint farm on one of them, I would just ask the same question I did initially --  does the db mirroring fit your your business requirements?   If so, then it should be fine, but it won't be without the overhead necessary to maintain the mirrored db's on the multiple instances.
0
 

Author Comment

by:goprasad
Comment Utility
I have got following questions:
First: Principal and Mirrored servers with Fibre SAN storage will be sitting in the same location  I am expecting that even if we opt for High safety Synchronous DB mirroring, with bandwidth of 1 GB/sec that we currently have, I am hoping we should not be having any issues with traffic (T-logs) between Prin and Mirror server? Is this assumption correct?
Second quesion: To utilise licensing benefit, can I host 2 or more instances (with 3- 4 DB's) that are non-critical. in Principal server, without havinng mirroring them?
0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 500 total points
Comment Utility
First:  No, I don't think that is a safe assumption to make.  The simple presence of FULL safety (synchronous) impedes the workload and transaction throughput by default.  Though your servers are co-located, and you're on a 1GB line, you should not discount this.  The true impact of the chosen safety level is very dependent upon the application(s), but still, I don't believe you should discount this.  See each of the comparison tables listed within the 'Transaction Safety Levels' section:  http://msdn.microsoft.com/library/cc917681#EDAA

Second:  Yes, you can house other non-mirrored databases on the principal server.  Database Mirroring is at the database layer.  The whole instance is not mirrored.  

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
Teach the user how to configure vSphere Replication and how to protect and recover VMs Open vSphere Web Client: Verify vsphere Replication is enabled: Enable vSphere Replication for a virtual machine: Verify replicated VM is created: Recover replica…
This video shows you how to use a vSphere client to connect to your ESX host as the root user. Demonstrates the basic connection of bypassing certification set up. Demonstrates how to access the traditional view to begin managing your virtual mac…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now