CODEWITHSUNDEEP
sql-serverdatabaseazure-sql-database
CBreeze
CBreeze

Reputation: 2965

Synchronising tables between two SQL Server databases

At the moment I have Database_One and Database_Test, both SQL Server databases.

In the future I may have Database_Two, Database_Three etc as more clients use my program.

Currently if I need to add a column to a table in Database_Test, I would need to add it to all other databases individually, manually.

I would rather have a system where all databases are replicas of a master database, but have their own data. So that if I need to add a new column to a table in the master database, this is replicated to all others but the data is individual to each database (and therefore client).

Is there a method that lets me do this with SQL Server databases?

I should also add that these are SQL Server instances running in Azure.

I would also like this mechanism to copy the database structure, but not it's data, so that I can easily setup new databases in the future.

Upvotes: 0

Views: 101

Answers (5)

Alberto Morillo
Alberto Morillo

Reputation: 15648

You can use elastic jobs to manage schema changes and all tenant databases. Please examine a sample application here. that make changes to all tenant database schemas and data.

You can put all your tenant databases on an elastic pool and use Elastic Database jobs to ease managing large sets of databases and allow execution of Transact-SQL (T-SQL) scripts across all databases in a pool. Read more here.

Upvotes: 0

David Rushton
David Rushton

Reputation: 5030

Microsoft publishes an add-in for Visual Studio called SQL Server Data Tools (SSDT).

SQL Server Data Tools is a modern development tool that you can download for free to build SQL Server relational databases, Azure SQL databases, Integration Services packages, Analysis Services data models, and Reporting Services reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

Source - MS Docs

At a high level; SSDT converts your database objects in source code and provides a mechanism to publish updates to your target servers.

SSDT is by no means the only software out there. Other options include Ready Roll, form Redgate.

Choosing the right tool for you is just the start of the journey. Delivering change in a fast, robust manner with the appropriate controls in place takes time and a lot of careful thought. Luckily there are loads of people out there who can help. Steve Jones (from SQL Server Central) appears on a couple of channel 9 videos about this very subject. He's a big DevOps advocate, and well worth following if you like to learn more.

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

I don't know, what's the best practice to do this task but let me brief mine practice as I found it easy and safe manual process

Physical file of every DDL and DML is saved in SVN and applied to every related versions upon every commit. For example, I already have the script of creating table named as test and now, I need to add one more column as category to it then my script in the separate file would be :

IF COL_LENGTH('test', 'category') IS NULL
BEGIN
    ALTER TABLE test ADD category INT
END
GO

The script file is now saved in the SVN Repository and will be applied to the every version.

Upvotes: 0

Mauri
Mauri

Reputation: 119

If we consider the simplest "deploy" procedure having the "_test" database as your main development stream with the final database structure

then a simplest "installation" of a new version ( like adding a column ) may be performed by using a simple SQL Schema Compare tool

your "_Test" database will be the "source" of the schema to apply to all your destination database A database schema compare will find out and highlight all the difference and automatically prepare a SQL script to be executed to your target database

here a discussion about different schema compare, the one in visual studio is pretty handy, works great also on big databases.. and is already there!

What is best tool to compare two SQL Server databases (schema and data)?

Upvotes: 0

Grant Fritchey
Grant Fritchey

Reputation: 2775

There is no built-in method to do what you want while maintaining discrete sets of data. Instead, I recommend building out a full deployment process using source control as your centralized location for your database and then automate deployments from there. It's how I've done the same thing that you're attempting. Further, it allows you to automate testing and validation prior to deploying out to your production servers. This free book I wrote covers a lot of the general process. For details we'd need to start talking tooling.

Upvotes: 3

Related Questions