Source control: Using Visual Studio Online in SSMS

As database professionals, we’re all aware of the importance of backups. We make sure the backup process runs as expected, and (hopefully) we restore a backup every now and then to test if the restore process works. But what about source control for your scripts? Most of us don’t use source control in our daily job. But source control is a much a part of the backup process as the actual database backups.

So looking for an easy way out, I focused on Visual Studio Online (VS Online). This is a free online source control system, that you can use once you created an account. You can login on the website with your Microsoft Live account, enter some information (like a username, etc), and you’re ready to go!

But how do you go from writing a query in SQL Server Management Studio (SSMS), to checking in your .sql code files into VS Online? Let’s take a look.

 
Installing TFS tools
In order to use VS Online, you need to install some extra things on your machine. The first thing you need to download is the “Team Explorer for Microsoft Visual Studio 2013”. This installs the team explorer, that you need to get things from and check thing into VS online. This install requires a reboot, so please take that into account!

The second and last installation is the “Microsoft Visual Studio Team Foundation Server 2013 MSSCCI Provider”. This installs the provider, that takes care of the communication with VS Online.

For this installation, it’s important that you download the right version. If you’re running the 32-bit version of SSMS, you need to download the 32-bit version of the provider. If you install the 64-bit version, it won’t work (and trust me on this, I’ve made that mistake before!).

 
VS Online
At this point, I’m assuming you have an account for VS Online. If you log in to the website, and you go to your account url ([AccountName].visualstudio.com), you see the “Create your first team project” page. On this page, you need to create a project before you can check in any files:

 
Configure source control in SSMS
The next step is to configure SSMS, to use the source control provider you just installed. After the installation, you get a new menu in SSMS for source control:

In this menu, click on “Open from Source Control”. In the window that opens, click “Servers…”:

Click “Add…” to add a TFS server or URL:

Now you need to enter your account URL in the textbox:

Once you’ve done that a login screen pops up. Log in with your Microsoft Live account (the same you used to create your VS Online profile), and you’re authenticated:

At this point, your source control is added, and you’ll see it in the overview screen:

If you close the windows that are shown, you end up in your source control project overview:

 
Creating a SQL Server Scripts project/solution
Now that we’ve installed the source control providers, it’s time to create our SQL Scripts project. In SSMS, click on File => New => Project (or Ctrl+Shift+N), and choose “SQL Server Scripts”:

Now that you have a project, it might be handy to show what’s in the solution (if you don’t have that on your screen already). You can open the “Solution Explorer” by clicking on View => Solution Explorer (or Ctrl+Alt+L).

 
Checking in your solution
If you want to check-in your solution or project, you can right-click it in the Solution Explorer, and choose “Add Solution to Source Control”:

 
Another way of doing it, is by clicking on File => Source Control => Add Solution to Source Control. In the next window you are asked for the source control server (click okay if your VS Online URL is selected), and you see the project explorer:

Click on “MySQLProject”, and click OK. Now the check-in screen pops up, where you can “tag” your check-in:

Congratulation! You just checked in your first code in VS Online!

 
Pitfalls and difficulties
In all honesty, there are some downsides in using this, but those are mainly issues in SSMS. For example, you can’t create folders in your SQL Script project. You have to work with the default “Queries” folder you get by default. So there’s no chance you can add a “Finance”, “Maintenance” or “DBA” folder to your project for example.

A work-around for this is creating multiple projects in the solution. So you’ll end up with 3 projects in 1 solution. The nice thing is that you can check-in or check-out 1 single project in your solution, or you can just work on the entire solution at once. This gives you the possibility to work on the solution on your own, or with a group of people.

Another pitfall is the process. If you’re not used to working with queries in source control, it’s easy to forget to check-in your changes at the end of the day. But that’s just something you need to get used to, and need to deal with in your own way. If you’d like to check-in multiple times a day, go ahead and do that. Do you want to check-in your changes at the end of the day only, it’s fine as well.

 
Conclusion
Using source control is great, especially when you have a lot of scripts you need to maintain. I’ve tried to create zip-archives, version numbering my .sql files, different directories, etc. But there’s always a moment that you forget to save your script in an archive, or your computer crashes, and all your work is gone. Using source control prevents these issues (if you use it as intended of course!). VS Online is a really good source control platform (it’s basically TFS online), and it’s free for use. One of the mayor advantages is that VS Online / TFS is fully compatible with SSMS and Visual Studio (which you both use as database professional).

Are you still skeptical? Maybe you should just try it out for a week, to see if it helps you in your daily job. And you know what, maybe you’ll like it. And even better: maybe it’ll someday save your life!

#SQLHelp – SQL 2012 Management Studio Freezes

As I told you in a few of my previous blogposts, I try to follow the #SQLHelp hashtag / topic. And two weeks ago, I could help another colleague via this communication channel.

When SQL Server 2012 RTM came out, I installed it as quick as possible. Just to try it out, and to see what the differences were compared to the other version I installed on my machine: SQL Server 2008. When using SQL Server Management Studio 2012, I encountered random freezes of SSMS. The freezes didn’t occur every time I opened a menu, or started a wizard or something. So it was a problem with my installation.

After a while, I remembered that the installations of SQL Server 2005 and 2008 had the same issue. These SSMS installations also froze, because they shared some dll’s with Visual Studio. So the issues I had now, might have the same cause. And eventually I re-applied Visual Studio SP1, and this solved the issue for me.

And after a few weeks, I saw a similar question from Samson J. Loo (Blog | @ayesamson) coming by, using the #SQLHelp hashtag:

_{@ayesamson, 2012-05-23}

has anyone experienced random unresponsiveness with SSMS 2012 to a point where you have to kill the process? #sqlhelp #sql

So because I saw this issue before, I replied to his tweet:

_{@DevJef, 2012-05-23}

@ayesamson: Yes. Are you running into this issue constantly, or just once? Problem might come from shared DLL’s with VS2010…

Apparently he was still having these issues:

_{@ayesamson, 2012-05-23}

@DevJef its been happening more frequently now. I do have VS2010 installed as well. ‪#sqlhelp

So from my previous experience, I gave him the tip to re-apply Visual Studio 2010 SP1:

_{@DevJef, 2012-05-23}

@ayesamson: I had the same issue. I actually fixed it by applying VS210 SP1 again. This might help you as well! ‪#SQLHelp

The next day, I got the confirmation that SP1 was re-applied:

_{@ayesamson, 2012-05-24}

@DevJef I re-applied VS2010 SP1 this morning, rebooted and haven’t had an issue. If I don’t have an issue come Mon. then we’re golden!

And a week later, I got the great news it helped him get rid of the freezes:

_{@ayesamson, 2012-05-31}

@DevJef well I haven’t experienced any lockups with SSMS 2012 since reapplying VS2010 SP1. Thanks!! ‪#sqlhelp

So I was glad I could help him out, and happy he actually got back to me about resolving the issues. So thank you for that Samson! And for the rest of the community, I hope I helped you with writing this post!