asked
Can I manage several "identical" MS SQL Databases with different data?
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?
Remember the site works from voting. For all helpful answers you should vote them up by clicking on the thumbs up next to those answers. If any one answer lead to a solution, you should indicate that answer by clicking on the check box.
SQL Server Data Tools is perhaps worth looking at. You can use it to develop the "source code" for the database independently to the data. When you make changes, it can generate change scripts to apply to the databases
I would strongly recommend taking a look at the book [SQL Server Team-Based Development][1]. The tools it covers are somewhat out of date (it covers both Microsoft and Red Gate tools), but the concepts of managing your code in source control, automating deployment, and managing multiple copies of your database are very well covered within. [1]:
http://www.red-gate.com/community/books/sql-server-team-based-development.htm
16 People are following this question.
