Tuesday, February 05, 2013 7:15 AMThis is probably the most classic database problem.
I have an E-commerce software solution hosted on a SQL server for data, and a web server for the frontend. Every instance/customer has its own database on SQL Server 2008.
During development of the next version, I might change or add tables, views, stored procedures etc.
How do I publish this change to all databases, without losing data? It should be done via a script or something similar. Centralized management is the key...
Tuesday, February 05, 2013 7:29 AM
here are few options
Wednesday, February 06, 2013 7:14 AMModerator
This problem is essentially one of automating the manual process of logging on to a SQL Server, and running a script against one or more databases, that does the modifications you need.
It's made worse, of course, if the instances of SQL Server that you need to update are remote from you, and therefore not directly accessible.
It's also vital to ensure that the scripts are applied in sequence - it would be no point running the "add index" script before the "create table" script.
The way we've solved this is with a web service that packages script files as datasets, and delivers them in the correct sequence to the remote systems when they call home.
On the remote SQL Server, we have a .NET application which calls the web service, downloads the script files, unpacks them and applies them to the database.
When the remote system calls in, it supplies the ID of the most recent upgrade it has. When the web service completes, it knows the last one it delivered. It's therefore trivial to know what level the remote systems are at.
The only manual intervention required is to create the scripts in the first place, and upload them to the central server.
TechNet Community Support
- Marked As Answer by Iric WenModerator Monday, February 18, 2013 1:38 AM