locked
Deploying one database to many servers RRS feed

  • Question

  • Is there an easy way to deploy the same database to multiple servers?

    We have a database with some reference tables and maintenance scripts, including backup procedures and we wanted to deploy that same database to multiple servers.

    Can you think of an easy way of doing that?


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Wednesday, April 13, 2011 9:55 PM

Answers

  • Is this a one time thing, or on a regular basis?

    If this is going to be needed on a regular basis, I would suggest Snapshot replication.

    • Proposed as answer by Peja Tao Friday, April 15, 2011 2:15 AM
    • Marked as answer by Alex Feng (SQL) Thursday, April 21, 2011 3:19 AM
    Thursday, April 14, 2011 1:54 PM
  • Is there an easy way to deploy the same database to multiple servers?

    We have a database with some reference tables and maintenance scripts, including backup procedures and we wanted to deploy that same database to multiple servers.

    Can you think of an easy way of doing that?


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com

    As others suggested ...backup and restore or copy the physical files and attach them ..If the main database changes and you also want other replicas to capture that change from time to time ..then transactional replication with updatable subs \ merge REPL will also be fine ...in case the other replicas will only be used as READONLY , then log shipping , mirroring , snapshot\tran replication would suffice ..
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by Peja Tao Friday, April 15, 2011 2:14 AM
    • Marked as answer by Alex Feng (SQL) Thursday, April 21, 2011 3:19 AM
    Thursday, April 14, 2011 6:01 PM

All replies

  • Backup/Restore, detach/attach.

    Wednesday, April 13, 2011 10:41 PM
  • I did this last time by scripting the whole database including data and schema using database publishing wizard (Which I think is now included in SSMS 2008 or R2).Then used this script and executed it on multiple instances using a parameter input file in SQLCMD and you can also generate an output file.
    Thanks, Leks
    Wednesday, April 13, 2011 10:45 PM
    Answerer
  • I did this last time by scripting the whole database including data and schema using database publishing wizard (Which I think is now included in SSMS 2008 or R2).Then used this script and executed it on multiple instances using a parameter input file in SQLCMD and you can also generate an output file.
    Thanks, Leks

    Hi Leks, No offense  , scripting the whole database including data  might work for small databases but I wouldn't be doing this for a really large database.

    Thanks

     

    Wednesday, April 13, 2011 11:42 PM
  • Hi JR81,

    Agreed ! Aware of it , not at all good practice for large database I recommended because the OP mentioned it as DBA admin / monitoring database which I am predicting it to be a smaller one , I have one such database called DBA_ADMIN ( Some SPS and VIEWS that our DBA team uses for monitoring and tracking details ) that is around an MB of Data.


    Thanks, Leks
    Wednesday, April 13, 2011 11:58 PM
    Answerer
  • This is can be done by backup and restore or by detaching the db and copying the db files to the servers where db needs to be restored and then attaching the db.
    Cheers!! Kalps
    Thursday, April 14, 2011 11:07 AM
  • Simply write a backup and restore script, where there are multiple restores. If you did this in Powershell you could do something really simple, efficient and conditional.

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Thursday, April 14, 2011 11:16 AM
  • Is this a one time thing, or on a regular basis?

    If this is going to be needed on a regular basis, I would suggest Snapshot replication.

    • Proposed as answer by Peja Tao Friday, April 15, 2011 2:15 AM
    • Marked as answer by Alex Feng (SQL) Thursday, April 21, 2011 3:19 AM
    Thursday, April 14, 2011 1:54 PM
  • Is there an easy way to deploy the same database to multiple servers?

    We have a database with some reference tables and maintenance scripts, including backup procedures and we wanted to deploy that same database to multiple servers.

    Can you think of an easy way of doing that?


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com

    As others suggested ...backup and restore or copy the physical files and attach them ..If the main database changes and you also want other replicas to capture that change from time to time ..then transactional replication with updatable subs \ merge REPL will also be fine ...in case the other replicas will only be used as READONLY , then log shipping , mirroring , snapshot\tran replication would suffice ..
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by Peja Tao Friday, April 15, 2011 2:14 AM
    • Marked as answer by Alex Feng (SQL) Thursday, April 21, 2011 3:19 AM
    Thursday, April 14, 2011 6:01 PM