May 11, 2009 at 9:35 am
I'm no DBA (yet?!), just a humble C#/SQL programmer who finds himself suddenly in charge of all things data, including the stuff mentioned in the title.
We are a new team of generalist programmers recently put together to create a new web application from scratch, and the database part is all down to me. Part of that is to define our working practices and controls. We are using Team Foundation Server and Visual Studio Development and Database editions.
We've had a brainstorm and decided there are two main ways to manage the source control and release process:
1) We make changes to the Dev database and, after suitable code reviewing (the exact nature of which is another item on the agenda!) we use Schema Compare (and perhaps Data Compare too for dimension tables - I've not looked at that one yet) to synchronise the database project in Team Foundation Server with the Development DB changes, and then update production from there at release time - again with Schema Compare. This seems like a very quick and simple process in many ways, but has its drawbacks (for instance, a typical "if exists... drop... create procedure... grant execute..." template doesn't work with schema compare as it doesn't understand anything outside the CREATE...END structure). Of course there would be checking and possibly amending of the update script too, but overall it seems to shortcut a lot of work that would be in the next scenario...
2) Maintain templated scripts for all database objects in TFS. Developers check out/change/check in the script, and we maintain a build script/list of changed objects, and some automated process to build an update script from all the items in the change scripts. This is the approach the DBAs at my last company used, but it seemed to incur a lot of DBA work for each release.
I think the tools in Visual Studio Database Edition should be a great help to the DBA, but I'm not sure how to make the best use of them. Ideally I'd like to come up with some hybrid of the two scenarios that allows us to keep well-formed SQL scripts under source control but avoid the overhead of the manual system by making use of the Visual Studio database tools (especially as there's just the one newbie DBA with no support - i.e. me!).
What do you think people? Have you devised the perfect strategy yet?
Cheers,
Dave.
May 11, 2009 at 12:10 pm
I think your plan is complicated closer to convoluted, here is what we did after two SQL Server hardware failure in one month. All developers save their compiled stored procedures in VSS/TFS as .sql file and you the team lead saves at least a copy of .bak in TFS every day or week depending on number of backups and changes. This means all your developers will be up and running in less than two hours. I can help with most things not covered by my nondisclosure.
Kind regards,
Gift Peddie
May 11, 2009 at 1:28 pm
Do you mean you're using Visual Studio Team System Database Edition, or just the regular Visual Studio with a database project? I'm asking because you talked about Schema Compare & Data Compare and I just want to be sure you're not using Red Gate tools with the standard VS.
If you're using VSTS:DB, I'd say you're not using the tool properly. In general, I would want all changes to originate in the code, in TFS, just like it does in whatever .NET language they're writing in. We treat our database, as much as possible, exactly like .NET code. It gets checked in & checked out of source control (TFS, just like you) and changes are made then it's checked back in. Testing is done locally first, then on a shared development server. Sets of changes are marked via labelling and then released to a QA environment. Possibly they go straight from there to Prod, but usually more changes get aggregated & new labels are applied. We always build QA from a label and then use the same label to build prod.
You're pretty close though. There's some great posts describing how to use the tool over at Barclay Hill's blog. Recommended reading. I have a few posts with my fumblings at my blog too[/url], but they're not as good as the other ones..
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2009 at 4:15 am
Grant Fritchey (5/11/2009)
Do you mean you're using Visual Studio Team System Database Edition, or just the regular Visual Studio with a database project? I'm asking because you talked about Schema Compare & Data Compare and I just want to be sure you're not using Red Gate tools with the standard VS.
Yes, we're using Visual Studio Team System Database Edition.
The schema compare option seems much simpler, which is why it appeals to me over having to devise a structured way to control a release - I'm a developer who just happens to be the only one who knows a bit of SQL and therefore find myself responsible for what would normally be DBA tasks.
In the past I've always worked within established procedures that follow the source-controlled script approach you outlined, but as I mentioned this has always seemed to demand a lot of DBA time to administer. As my primary role is development I want to avoid being tied up in administration tasks for much of the time if at all possible - both for my own job satisfaction and in order to maintain a decent level of development output from me to contribute to the team (as it is already I find myself with more hours of development tasks assigned to me than to any of the other team members as this application is very database-heavy).
Cheers,
Dave.
May 18, 2009 at 6:22 am
I do feel your pain. I work as a development dba, so I straddle both disciplines although the overwhelming majority of my work is straight database work.
It does require a certain level of structure & discipline to get started using VSTS:DB, but once yo get the deployments set up & operating, it should reduce your overall workload. At least, I've seen it that way. My biggest problem is I can't get all of my development teams to work on the procedures out source control in the same fashion. Some do it, and their deployments are easy, and some don't, with the time-consuming deployments you're facing where I have to try to determine what has changed, what hasn't and what should be deployed & what shouldn't.
All I can say is, if you set up compound projects so that you have a base project for the database code and then a project for each environment that combines the appropriate server & app code with security, it really does work very well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2009 at 9:32 am
Hi Dave,
No need to pain.. you can use a tool for SQL Version Control, that will do the job for you.
--------
SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools[/url].
June 9, 2009 at 8:02 am
See the following post which briefly describes our process.
http://www.sqlservercentral.com/Forums/FindPost702354.aspx
More "meat" can be found in the linked posting (message 474053).
http://www.sqlservercentral.com/Forums/FindPost474053.aspx
June 10, 2009 at 9:55 am
What we do is very simple. Maybe too simple you decide. We don’t use VSTS or VSTSDBE. Just VS 2008. We store all the database objects as scripts inside SCM. When we create a new development branch from the QA branch the objects are included in the new branch. When we need to make a change to a stored procedure, we check the scripted object out of the branch make the change, test it, and check it back in and it gets promoted with the rest of the code. When we compile the script on the database sever we compile them with encryption. This prevents any developer from going directly to the server to make a change.
Simple, but as a small shop it works for us.
David
June 10, 2009 at 9:57 am
What we do is very simple. Maybe too simple you decide. We don’t use VSTS or VSTSDBE. Just VS 2008. We store all the database objects as scripts inside SCM. When we create a new development branch from the QA branch the objects are included in the new branch. When we need to make a change to a stored procedure, we check the scripted object out of the branch make the change, test it, and check it back in and it gets promoted with the rest of the code. When we compile the script on the database sever we compile them with encryption. This prevents any developer from going directly to the server to make a change.
Simple, but as a small shop it works for us.
David
June 10, 2009 at 9:59 am
Weird, my post added twice, but it wont let me delete one of them.
Sorry.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply