SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mirroring Setup


Database Mirroring Setup

Author
Message
SQLBrady
SQLBrady
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 225
Comments posted to this topic are about the item Database Mirroring Setup
Robert Davis
Robert Davis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10692 Visits: 1660
Congrats on writing posting your first article!! I think it's a pretty good first article. There are a few points I'd like to clarify just to make sure the readers are clear.

Mirroring is not supported on different Editions for the Principal and Mirror. Though possible to do so, you lose supportability by using different Editions.

The service accounts for the SQL services should not be running as domain admins ... ever.

The principal and mirror servers do not need to use the same port number on both sides. In fact, there are some circumstances where this isn't even possible.

The role of the endpoint doesn't have to be the same on mirror and principal. Each one has to be in either the "Partner" or "All" role.

The picture at the end does not show that the service account has been granted Connect permissions. In fact, it doesn't show the mirroring endpoint at all. The reason for this is because the service account's permissions are inherited from it's role as sysadmin rather than having been explicitly granted.

Regards,


My blog: SQL Soldier
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management
augustine.damba 67604
augustine.damba 67604
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 11
I agree with Robert's additional comments. if instances are installed on the same server then mirroring ports will have to be different
Robert Davis
Robert Davis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10692 Visits: 1660
Just to reiterate, All I'm all, good article. Don't let my comments discourage you. Keep writing!!


My blog: SQL Soldier
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management
James A Skipwith
James A Skipwith
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 736
A good first article. Yes there are a few minor points that will be picked up on but that's all good, as you only learn about article writing this way! Keep on writing.

James
MCM [@TheSQLPimp]
echatzigeorgiou
echatzigeorgiou
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 209
Thanks for the article. A quick question:

on a production system, can I do the backup/restore steps without
blocking incoming connections? If this is true, then the transactions
issued after the backup, will be applied to the mirror after synchronization?

Thanks
adrian.cunningham
adrian.cunningham
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 14
Screen shot shows principal /sychronised in both cases. Assumed the second screen shot should say 'mirrored/synchronised'
David Atherton
David Atherton
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 125
First thing, good article Brady.
With many topics, sometimes your going to get a lot of feedback (which is good) on little tidbits which may need "more" feeling out.

@echatzigeorgiou
Backups can be run at anytime but only from the Principle Servers database. Backups are basically point of time snapshots so the database goes on it's merry way while the backup occurs.
For restoring, in mirror configuration, it's a bit more complicated. Basically you have to break the mirror, do your restore on your principle and then go through the whole process of configuring the mirror again.

Also, to add to your article, sample connection string for using the failover in applications...
connectionString="Network=dbmssocn;server=SQL1;Failover Partner=SQL2;Initial Catalog=YourDB;"
It gives the application the 2 partners in the mirror to connect to if one fails. This is for when you have a witness in the mirror operation, "realtime" failover requires the witness server (which can be a simple express version of SQL)

Also, Mirroring is database specific, any configurations for the actual SQL server (such as jobs, security, etc...) have to be manually managed between the servers.
HTH and keep writing...
Dave
echatzigeorgiou
echatzigeorgiou
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 209
Thanks David, but my question was different:

If I need to add mirroring on a production system which at the time consists
of a single server (db01), do I have to block transactions before taking
the backup on (db01) and only allow them after the mirroring has been
setup?

Or the mirror server is able to apply any additional transactions that were issued
after the backup was taken on db01 and before the mirror was completely
setup (restore on db02 + mirror)

Thanks
masud4u2006
masud4u2006
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 45
Hi,
I have one question...
while restoring the log backup on mirror db (db will be in restore mode), will the users can access the db at the same time. Bcos in log shipping users can't access the db while db is in restore mode. So is this rule apply in Mirror db as well....?

Thanks in advance
Masood
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search