Post reply

Database Mirroring Setup

  • April 12, 2011 at 9:05 am

    #1310450

    Thank you all......I got the clear understanding now

    Have a nice day 🙂

  • April 12, 2011 at 9:10 am

    #1310454

    hmmm...Haven't done any mirroring dealing with SSRS but my gut feeling is that it should be possible. Issues have to stem from the web access point to the reporting servers. You would have to configure a generic url to access the reporting services and check which one is active (load balanced scenario checking for availablility?).

    Again never done and could be blowing smoke out my butt but I would think that it should be possible...

    Actually, someone else has a thread on the topic...

    http://www.sqlservercentral.com/Forums/Topic656547-146-1.aspx

    Sorry...just got the gist of masud4u2006 question...Brady and crashdan's assesments are correct. Mirroring is not an option since only one database is active at a given time.

    Dave

  • April 12, 2011 at 7:26 pm

    #1310777

    echatzigeorgiou (4/12/2011)

    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

    Mirroring works in Full Recovery mode so any changes made on the principal during mirroring setup are applied to the mirror through log synchronisation. That is why you need to restore the full backup and then restore the log so mirroring knows the last log sequence restored.

  • April 12, 2011 at 7:37 pm

    #1310779

    masud4u2006 (4/12/2011)

    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

    In log shipping you can have the database in NORECOVERY or STANDBY mode. When in STANDBY mode, the DB can be accessed for read only but disconnections may occur if log is being restored.

    Mirroring does not suport STANDBY mode. so you cannot access the mirror DB.

    http://msdn.microsoft.com/en-us/library/ms366349.aspx

  • April 12, 2011 at 8:26 pm

    #1310784

    Mirroring supports database snapshots in Enterprise/Datacenter Edition. You can take a snapshot of the mirror database and provide a read-only point-in-time copy of the database for reporting purposes.

    Database mirroring is not supported for reporting services databases. For the user databases that the reports pull data from, it is supported.

    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]

  • April 13, 2011 at 7:22 am

    #1311006

    Great article! I am a huge fan of mirroring. I would just add a couple of things.

    If your applications actually need to connect to the mirror (which becomes the principal after a failover), you will need to have logins transferred to the mirrored db's instance. If you choose to use the Transfer Login task in SSIS, a little trick I found is to make sure you change the default database of the login to the master DB before transferring and change it back to the correct user DB after transfer. Otherwise the SSIS job will fail.

    Also, some applications will have dependencies on objects in the system DB's (like tables, roles, users, schemas, etc) but you can bring those over to the second instance with the Generate Scripts utility. Just right click on the DB in the first instance, Generate Scripts, check all the objects you want (I usually take everything if I am not sure but you want to go through the lists), then run the resulting scripts on the second instance.

    You may also need to export/import data from those system DB's to support your application. I have several customers with homegrown (and even 2 commercial) applications that store and use data in the master DB. This makes mirroring and moving databases much more complicated.

    And one of my favorite parts of mirroring is that you can use SQL Express as the witness on any available Windows machine (although I would recommend a server near the principal, not someone's laptop 🙂 )

    And remember the use case for mirroring, a very important piece. When you need DB redundancy or high availability, use mirroring. When you need instance redundancy, use clustering. I have many customers that ask for clustering when they only need mirroring. And if you have dependencies for any one application on multiple DB's or system databases, clustering can be a simpler method.

    Thanks again for the article!!

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • June 1, 2011 at 10:39 am

    #1332605

    In the Screen shot, Principal and Mirror Server looks same (Principal and Mirror Same)

    Is that an error? Or that's how it looks like?

    Thanks for the amazing Article. Fan for years....

    Thanks,

  • January 31, 2014 at 8:38 pm

    #1685270

    No you do not need to block transactions. However, do not waste too much time between the backup and restore of the transaction log. When you set up the mirror it checks the LSNs on both sides. If it is too far out of date it will tell you that you must do another transaction log backup and restore before setting up the mirror.

    Cheers 🙂

  • January 31, 2014 at 8:43 pm

    #1685271

    Hi masud4u2006,

    Regarding your question about reading data from the mirrored database. You can by implementing a DBSnapshot

    Please read http://technet.microsoft.com/en-us/library/ms175511.aspx

    There are a few gotchas re performance and renewing the snapshot but these may be surmountable depending on your requirements.

    Cheers 🙂

  • February 3, 2014 at 2:58 am

    #1685414

    Great article and equally good and interesting responses.

    Food for thought. Whilst Mirroring in my mind used to be a great feature when we used to build Servers (physical) for SQL, these days I tend to not set mirroring up as often as I am completely satisfied with the redundancy offered in VM environments.

    In the days of physical servers the concern was that both physical and software related failure (except for data corruption as that will also be mirrored) could cause system failure and warrant a switch to the mirror.

    With virtual environments, Hardware redundancy is ideally built into the VM environment and in my view the risk of software failure is minimal as I have never had this happen to me even when we had physical servers.

    The only scenario where software could possibly fail is when an patch or software update is implemented on the server. In these circumstances, I would normally have tested said software patches or updates before applying to live environment. Additionally, I would have scheduled downtime and therefore take a VM snapshot before applying patches so I can revert back instantly if needed.

    Therefore I find the need for mirroring servers is becoming less. Even when system uptime is crucial, I would consider building a Mirror when I do a software update, switch to the mirror, break the mirror, apply the patch, switch the mirror to the main, test to ensure it is working, if satisfied remove the mirror.

    If I wish to upgrade from say SQL 2005 to SQL 2012, I tend to simply build a new SQL Server completely in VM, copy database over, then change all sofware to point to the new database. Leaving the SQL 2005 in place and ready to fire up if need to revert back.

    I find many software companies tend to recommend mirror setup, but on questioning they are normally unable to provide good reason and I get the impression they simply suggest this as they think it is the best and safest option.

    Anyhow, I would be interested in others thoughts or views on this

  • February 24, 2014 at 12:00 am

    #1691416

    Dear,

    I have a server with Application and database in one as Primary to which all the clients are connected. I have another server in which same application and database is installed. I want to install SQL Mirror with High Safety option on the Secondary server. I will change the Secondary server's IP with the Primary's and so that my clients start talking to the Secondary.

    My questions are,

    1). What if my Secondary goes down ? Being this a sync will my Primary stop responding to the Clients as the transaction in sync is not committed to the Secondary.

Viewing 11 posts - 16 through 25 (of 25 total)

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