locked
moving dbs from several mssql servers to a central; target instance partner for several source servers? RRS feed

  • Question

  • Hi,

    we are planning to reduce server instances by using mirroring. We are planning to perform this during one week. Can a central server instance be fail over for several db instances or need the mirroring be decoupled after each db has been moved ?

    Thanks

    Sven

    Tuesday, February 22, 2011 7:51 PM

Answers

  • Database mirroring is on a per-database level. This means that you can have multiple SQL Server instances with different databases configured as principal databases and mirrored to one SQL Server instance. For example, SQL Server instance A with database Aa, SQL Server instance B with database Bb and SQL Server instance C with database Cc can be mirrored to SQL Server instance D with databases Aa, Bb, and Cc, respectively. You just need to make sure that the SQL Server instance hosting the mirrored databases have enough horsepower to carry the load of the mirroring sessions.  

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    • Marked as answer by Sven W Wednesday, February 23, 2011 10:06 AM
    Wednesday, February 23, 2011 5:09 AM
  • Hi Sven,

    If you are running on 32-bit version of SQL Server instance, database mirroring can support a maximum of about 10 databases per server instance. For more information, I would recommend the following documentation: Mirroring a Large Number of Databases in a Single SQL Server Instance (http://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx).

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Sven W Wednesday, February 23, 2011 10:06 AM
    Wednesday, February 23, 2011 10:01 AM

All replies

  • Database mirroring is on a per-database level. This means that you can have multiple SQL Server instances with different databases configured as principal databases and mirrored to one SQL Server instance. For example, SQL Server instance A with database Aa, SQL Server instance B with database Bb and SQL Server instance C with database Cc can be mirrored to SQL Server instance D with databases Aa, Bb, and Cc, respectively. You just need to make sure that the SQL Server instance hosting the mirrored databases have enough horsepower to carry the load of the mirroring sessions.  

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    • Marked as answer by Sven W Wednesday, February 23, 2011 10:06 AM
    Wednesday, February 23, 2011 5:09 AM
  • A central server instance for failover dbs from several server can be done. Only issue would be we should have powerfull mirror server to support the db load when failover happens. Also you should configure manually all SQL Jobs running on all principal servers on mirroring instances as they are not failover with databases.
    Wednesday, February 23, 2011 9:41 AM
  • Hi Sven,

    If you are running on 32-bit version of SQL Server instance, database mirroring can support a maximum of about 10 databases per server instance. For more information, I would recommend the following documentation: Mirroring a Large Number of Databases in a Single SQL Server Instance (http://sqlcat.com/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx).

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Sven W Wednesday, February 23, 2011 10:06 AM
    Wednesday, February 23, 2011 10:01 AM