How Does SQL Source Control Work for SQL Server?

Question

I've seen different proprietary tools available which provide source for SQL Server, and while I've researched somewhat, I don't understand how these tools work. My concern is as follows:

Suppose I have source control for a c# project. Step #1: I make local changes in Visual Studio, and then step #2: I commit changes. At that point, the changes are pushed to the repository, let's say svn.

Before step 2, where are the changes? The answer is, they're on my local machine. They're a bunch of .cs or other files.

So my questions is, with SQL source control, what actually happens before I commit? I don't have a local copy of the database. Without source control, my SSMS client pushes the changes directly to the server. I don't see how it's possible to have the first step in a SQL environment.

Randy Minder · Accepted Answer · 2014-11-06 15:14:18Z

3

It works the same with SQL source control. I use SSDT (SQL Server Data Tools) for source control. There are a number of ways you can work with this. But once you have the schema in SSDT, you can make changes directly in SSDT and then synch them to your Developer, QA or Production SQL Server.

When you make a change to the schema in SSDT, you are actually making changes to SQL script files in source control. You can then check them in and deploy, much like a C# project. You can also 'build' the project which validates the integrity of your DB. It's all very slick, works well and SSDT is 100% free.

Note: When working with SSDT, you are not actually developing against any of your databases. All changes are made to SQL script files. It's not until you deploy changes to one of your SQL servers is any physical schema modified.

edited Nov 6, 2014 at 15:14

answered Nov 6, 2014 at 15:09

Randy Minder

48.8k56 gold badges222 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TomTom Over a year ago

+1 for SSDT. Nothing in SQL, just check in the project.

as9876 Over a year ago

So that means that the client tools "hijack" SSMS. So when I say "Alter object..." and F5, and the message shows "Successful"--it didn't really do what it said it did--namely alter, but rather it "pretended" to alter by storing an alter script. Is that correct?

Randy Minder Over a year ago

This has nothing to do with SSMS. SSMS plays no role in this.

as9876 Over a year ago

I'm not understanding. What happens when I hit F5? Does it hit the server or not? TY.

Randy Minder Over a year ago

You won't be hitting F5 because you won't be working in SSMS. You'll be working in SSDT. It has nothing to do with SSMS.

Ben Whitman · Accepted Answer · 2014-11-06 15:11:07Z

1

Visual Studio allows you to create database projects which create a model of the entire database schema on your local machine, which can be source controlled. VS can publish changes by performing a schema comparison between your model and a given target database and then generating the necessary commands to make the target match your model (hopefully without dropping all the data).

So if I was writing an application with a C# component and a SQL component I'd have both projects in the same solution to keep changes in sync.

answered Nov 6, 2014 at 15:11

Ben Whitman

414 bronze badges

Collectives™ on Stack Overflow

How Does SQL Source Control Work for SQL Server?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related