While Team Foundation Server 2010 may be primarily known for handling applications, there are extended abilities that will allow you to store other types of mission critical information which is what we’ll be taking a look at.
As with most data-driven applications, the code is something that we safeguard fairly well but sometimes the queries and other database objects are overlooked. I hate speaking from personal experience but how many times have there been attempts to “fix” a stored procedure that ends with disastrous results?
One neat feature that we’ve been exploring is storing queries that we work on into Team Foundation Server 2010 and I’ll be sharing that information with you.
There are some prerequisites involved. Make sure that you have the following available and installed:
• SQL Server Management Studio 2005, 2008 or 2008 R2 (Express versions will not work)
• Team Foundation Server MSSCCI Provider 2010
• Added your user to the collection
• Have an existing Team Project or have created a new Team Project
• Have proper permissions to the project
I’ll be concentrating on getting new SQL scripts through a SQL Server Management Studio project to your Team Foundation Server.
After you’ve installed the Team Foundation Server MSSCCI Provider 2010, open up SQL Server Management Studio and then access, “File”, “New” and then “Project…” to get started. Select the SQL Server Scripts template, provide relevant information for the fields, make sure that you check the “Add to Source Control” option and then click on the “OK” button.
Next, we’ll need to create a connection to our Team Foundation Server. At the “Connect to a Team Foundation Server” window, click on the “Servers…” button and then the “Add..” button. Supply the information for your DiscountASP.NET Team Foundation Server and provide your sign-in credentials when prompted. Click on the “Close” button at the “Add/Remove Team Foundation Server” window which should take you back to the “Connect to a Team Foundation Server” window where you can click the “OK” button.
From the “Choose Folder in Team Foundation Server” window, choose your existing project or the new project that you have created and then check in all of the files.
If the “Solution Explorer” isn’t already visible, click on “Solution Explorer” from the “View” menu. Right-click on the “Connections” folder and then click on the “New Connection” item to store the connection information to your database server.
Right-click on the “Queries” folder and then click on the “New Query” option. For test purposes, you can create a simple query such as “select getdate()” and then save it.
To check the new query in, right-click on the query that you’ve created and then click on the “Check In…” option.
From the “Check In” window, make sure that the new query is selected and then click on the “Check In” button. From the available source files, make sure that your new query is selected and then click on the “Check In” button again.
Your file should now be properly marked as read-only. To make revisions, you can check the file out, apply your changes and then check the file in.
I have created Database project in Visual Studio 2010 against my database on database server. Now, everything is fine. I can Check In and Check Out SPs , tables and function etc from Visual studio and On “Deploy”, changes goes to database server database.
Now, what I want is to Check In and Check out SPs, tables, functions etc from SQL Server Management studio instead of Visual Studio 2010. Can I link the project that I created in Visual studio 2010 with SQL Server Management studio to accomplish my will? If Yes, HOW?
I don’t think the Visual Studio database project solution files can be opened in SQL Management Studio. So you’ll have to choose one or the other. If you only want to use SQL Management Studio, then you’ll have to create a new SQL Management Studio project and move everything to that project.
This is so microsoft. why I am not able to change “Team Foundation Server” Folder Location..while creating first time I selected wrong folder and there is no way it’s let me change now. There is no option.
Even I uninstalled MSSCCI and reinstalled but somehow it just knows my first time folder location?
Our blog is not the place to get TFS support. Please use the helpdesk to send a ticket into support: https://tfs.discountasp.net/
Takeshi
Is it possible to create a sub folder or folders using the TFS provider plugin in SSMS? I would like to create folders such as Views, Stored Procedures, Tables, and etc.. instead of putting all the sql scripts in the Queries folder. Is that possible?
I’m afraid that you can’t create any additional folders for organization when you create a Project in SQL Server Management Studio and if I remember correctly, it’s been a problem since SQL Server Management Studio 2005 that has yet to be addressed.
Other than using a product like SQL Source Control from Red Gate, there’s no workaround that I’m aware of.
I wish to be able to create whatever folder in SSMS project to group queries, scripts in the way I want.
Can not just put thousand of stored procedures in one folders.
I’m finding for this feature, but hopeless. Why don’t MS support this? just one more minor step
This method is not bad. But as your database grows, the maintenance of this solution gets a headache per se. Maybe we don’t do it right. But imagine that everytime you want change your database, instead of changing it through fabulous designer UI, you should check out some scripts, change it, and reapply it. God, it’s so preventative.
saeed, that is indeed the right way to version control database objects, check out, modify it and check in..
A bit tedious but hey what else can you think? just jump into production and change it right way? That’s so wrong
Hi. This works really well. I can add new solutions into TFS from SSMS, modify files, check in updates, etc. However, a big feature I want to be able to use is Branching, so I can support release branches and development branches. However, as soon as I start trying to branch a SSMS solution, the binding gets messed up! I think it may still be pointing at the original solution. I’ve tried all sorts of steps for creating solutions and branching, but nothing seems to work for SSMS. Nobody seems to be reporting this as an issue, that I can find anyways. Is anyone out there successfully branching SSMS solutions? Would really appreciate some advice on this as its a pretty big issue for us.
Hi
I have same problem as Colin. Anyone found a solution to this?
Hi Colin and Magnus,
No one works in the way what you both are trying to do it. usually for database project when your final sql objects goes in production all you have to do is run a schema compare from production to your local TFS and update the changes so it will get sync with your local.
I have worked with many companies and this is how i have seen people doing it.
Hope this helps.
Thanks
VJ
Hi Colin and Magnus,
After branch, you must update the ssms solution file and project files. I branched “Development” branch from “Main” and replaced “Main” branch with “Development” and everything works fine.
.ssmssln -> SccProjectName0 = $/DB/Development/Sources/NorthPole
*.ssmssqlproj -> $/DB/Development/Sources/NorthPole
You can avoid having to update your ssmssqlproj files if you alter the ‘Main’ version to use SAK for all SCC related values.
e.g.
SAK
SAK
SAK
SAK
There is a good explanation of this on Alin Constantin’s blog:
http://alinconstantin.blogspot.co.uk/2008/02/sak-source-control-strings-in-visual.html
That doesn’t look right 🙂
I guess I’m not dereferencing the markup characters, the blog explains it all though.
Hi has anybody been using Code Analysis\Check-in policies for SQL objects in TFS Team Projects?
I’ve been searching but can only find Custom build policies, I’m wanting to use the same policies that are available for Database projects but cannot add them.
I don’t see the “Add to Source Control” check box. I’ve got all the prerequisites covered though I believe. Am I not set up correctly maybe?
Hi,
This is pretty amazing piece of information. Could you please help me understand why i’m unable to get the popup of “choose folder in team foundation server” in case of visual studio 2013.
Any help will be highly appreciated.
Thank you,
Sree.