Database Mirroring Setup

  • SQLBrady

    Ten Centuries

    Points: 1015

    Comments posted to this topic are about the item Database Mirroring Setup

  • Robert Davis

    One Orange Chip

    Points: 28027

    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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • augustine.damba 67604

    SSC Rookie

    Points: 46

    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

    One Orange Chip

    Points: 28027

    Just to reiterate, All I'm all, good article. Don't let my comments discourage you. Keep writing!!


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • James A Skipwith

    SSCommitted

    Points: 1808

    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

    Valued Member

    Points: 55

    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

    Grasshopper

    Points: 13

    Screen shot shows principal /sychronised in both cases. Assumed the second screen shot should say 'mirrored/synchronised'

  • David Atherton

    SSC Veteran

    Points: 298

    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

    Valued Member

    Points: 55

    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

    Grasshopper

    Points: 15

    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

  • SQLBrady

    Ten Centuries

    Points: 1015

    @Everyone

    Thanks for all the comments!

    @Robert Davis

    Thanks for the feedback. Coming from a MCM and Author of Pro SQL Server 2008 Mirroring your feedback is very important.

    While I agree with all your points this was meant to be a brief, easy overview for the Junior level just to get a grasp on what database mirroring looks like. This should not be a production environment's setup.

    @adrian.cunningham

    Doh, you are correct. Looks like I added the principal server twice. Attached is what the mirror should look like...

    @david-2 Atherton

    Thanks for the feedback and adding the connection string for people to use also.

    @masud4u2006

    Once configured, the mirror database with be unaccessible (like log shipping)

  • David Atherton

    SSC Veteran

    Points: 298

    @echatzigeorgiou

    OK, now first thing first, I would never condone doing any type of configuration changes without testing (and with this article using VM, it's a good idea to create a test VM environment to do the actual config in the lab first before in production) and full backups of everything before moving forward.

    With that said, when your basically changing anything production, you should book the time to be offline just in case (yep, be ready for that 2:00 AM deployment).

    You can obviously jump right in and start configuring your production DB to be mirrored but with the items in Brady's article as well as Roberts items, there are many aspects such as connectivity, SQL accounts, ports, firewalls, etc...that can play a part in getting your mirror working correctly and you should have that all documented in your steps (from your testing) prior to configuration to make the process clockwork but IMHO, reality usually jumps in with monkey wrenches and you'll probably be scrambling to do fixes/configurations during the setup.

    @masud4u2006

    As Brady mentioned, only the Principle database is active and can be connected to. The Mirror database is basically in a permanent state of recovery (receiving logs from the principle)

    Dave

  • masud4u2006

    Grasshopper

    Points: 15

    Thanks David for your reply...

    But my requirement is - we have to build new reporting server (db) and we have to implement Mirroring for read-only db and users will use Mirror db as reporting db and generate reports.

    So my question is --> is Mirroring a feasible option for reporting db....?

    Bcos as per log shipping, users can't use db when db is in restoring mode....so is this apply in Mirroring also....?

    Thanks

    Massoud

  • crashdan

    SSCommitted

    Points: 1960

    Replicated databases may be something better for you

  • brady.upton

    Newbie

    Points: 8

    Mirroring is not feasible for this situation as the reporting server will not be able to read the data from the mirrored instance.

    Replication or backup/restore options is the best way to accomplish this.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply