none
Can I manage several "identical" MS SQL Databases with different data?

    Question

  • 

    I have a set of different stores that belong to different owners I have a common web application for all of them. The Data bases are identical (same tables, columns store procedures etc.) but the data obviously is different depending on the store.

    I couldn't have a single database for all of them and have a column in each table indicating to which store belongs, because of different reasons like "legal reasons", privacy that each store wants, optimize the product search, client search, individual backups, etc.

    However every time I have to make a change in the application like adding a new column, change a store procedure I have to repeat the same steps for each Data Base is there any way I can have only one "MetaBase" in which I can make the changes and then they will be applied to each individual database?

    Monday, August 19, 2013 3:03 AM

Answers

  • 

    However every time I have to make a change in the application like adding a new column, change a store procedure I have to repeat the same steps for each Data Base is there any way I can have only one "MetaBase" in which I can make the changes and then they will be applied to each individual database?

    If the schema are identical, just keep the master copy under source control and deploy any schema change to each instance using a deployment script.  This can be facilitated using SQL Server Data Tools.  See http://msdn.microsoft.com/en-us/data/tools.aspx


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 19, 2013 3:35 AM

All replies

  • 

    However every time I have to make a change in the application like adding a new column, change a store procedure I have to repeat the same steps for each Data Base is there any way I can have only one "MetaBase" in which I can make the changes and then they will be applied to each individual database?

    If the schema are identical, just keep the master copy under source control and deploy any schema change to each instance using a deployment script.  This can be facilitated using SQL Server Data Tools.  See http://msdn.microsoft.com/en-us/data/tools.aspx


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 19, 2013 3:35 AM
  • If you don't have source control and don't use SQL Server Data Tools then (apart from recommending that you look them up and give them a go) you could also use Registered Servers.

    If you have identical database names and schemas then this would let just one management studio window connect to multiple servers at once and therefore any script you run in that window would be applied to the whole server group at once.

    It's a very underrated tool at times.  There are some good guides on the internet that will get you started...

    http://www.mssqltips.com/sqlservertip/2855/sql-server-multi-database-query-with-registered-servers/

    Monday, August 19, 2013 7:14 AM