Multi-Environment Deployments Using Team Edition for Database Professi

  • Comments posted to this topic are about the item Multi-Environment Deployments Using Team Edition for Database Professi

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Good article Grant, I have one question though.

    You say "you have to browse to the .user file and add it to your project and check it into source control"

    I was speaking to Jamie Laflen from the DB Pro dev team once and he had kittens at the very suggestion of checking in the .user file. Do we have the option to put all the configurations into the .dbproj file and, if so, what is best practice here?

    Great article!

    -Jamie

  • I was a bit curious about that too, but I'm seeing what Grant mentioned: when you add the separate configurations, some amount of that info seems to be put directly into the .dbproj file, and some more seems to go into the .dbproj.user file.

    Some of it seemed to be duplicative, but I can't say I saw everything that was in the .user, duplicated in the .dbproj. I don't know what the best practice is as the moment (I just really started dabbling with this version), but it does seem to be the DEFAULT setting. The UI seems to put those there, and I don't see any way to change where that info might get stored.

    For example - I can't find hide nor hair of the designated deployment DB connection string anywhere else, and I'd have to agree with Grant that that in itself would be something you'd want to set and keep with the project.

    Perhaps ask Jamie what the best course of action for those things might be...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I can't take credit for it. One of the guys in my shop, Scott Abrants, is one of those obsesive compulsive types who has to find the deep dark secret behind everything. We found that when we were opening each other's projects, the database connection settings kept going away. At first there was recriminations & finger-pointing, but when it happened to Scott, he went and found where the connection was being stored. It's in the .user file. He did a bit more experimentation and sure enough, we can check that file into TFS, and then get it to each local machine and the connection strings for each configuration travel with it.

    I'd sure be open to any other methods that work better and don't involve mucking about with files that normally remain hidden, but it's what we have.

    I'm glad you guys liked the article. We're currently working through the best way to get incremental deployments working and automated, so I may have another one in six or eight weeks.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • One catch you also need to add this file to the project - as Grant stated in his article - and not shown in the replies.

    If anyone else has a better suggestion I would LOVE to hear it. The alternative works fine if no one else on your team ever has to open your project. Without that file and the contents you would have to recreate all of the settings again for a project that is checked into a central source control.

  • Oh yes and nice article! Keep up the good work and thank you for the plug.

  • I wish there was a definitive answer here to the question "user file or dbproj file?". I use DBPro A LOT but I don't use configurations...after reading this article I'm think that I should.

    I see the following in my .dbproj file:

    PropertyGroup Condition=" '$(Configuration)' == 'Default' "

    (I would put the full XML in here but as soon as I add some angle brackets it doesn't show up in the preview window for some reason)

    so I kinda assume that it should be possible to define the other configurations in there. I don't know though.

    I generally find that in this version hacking the XML seems unavoidable at times (for instance, there's no way to define the target database name except in the .user files)

    Thanks again.

    -Jamie

  • Our solution gets us around having to hack the XML definition file; which is a good thing because not everyone here could handle that , nor should they.

  • Holy cow. We've been lucky so far then. We've been naming the project after the database. What a mess.

    With the initial release, I did find that we were hacking the XML, but after the first service pack, we haven't had to do that.

    Visual Studio provides a mechanism for creating more configurations with the Configuration Manager. Just don't get caught by the fact that there is a Solution Configuration and a Project Configuration. Until we had that seperation clear in our heads we ran into all kinds of problems.

    I haven't seen the 2008 version yet, but if it fixes the .user issue, I'd love to know. That'd give me a fantastic excuse to upgrade. I'm sure they'll address this soon. I'm pretty sure they're aware of it. If I recall correctly, and hopefully no one will beat me if I don't, I asked Gert Drapers about the .user file and he acknowledged that they shouldn't have stored the connection strings there and would probably be fixing it in the future. That was at PASS last year.

    We're using this tool more and more because it really is fantastic. But, because we use it more and more, all the little gotcha's get magnified.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey (1/24/2008)


    We're using this tool more and more because it really is fantastic. But, because we use it more and more, all the little gotcha's get magnified.

    Amen to that. It saves me buckets of time now that I know how to use it, but it took me a long time to get to that point.

    -Jamie

  • Hey guys. I've started to try and evaluate the VSDB and I wasn't really impressed. I'm so used to having more control over how things are structed by using a simple "Database Project" (which comes with any version of VS Pro and above). When I attempted to put all the "Login" scripts into one file, VS just barked at me and said I didn't know what I was doing. Also, in some cases it's nice to add a Sub-folder to my Procs folder to categorize the Procedures (because we tend to have 200+ Procs). Were currently pushing Developers to use the Projects instead of going into the databases directly so that more can be controlled using VSS (yes, I don't like it either but it works). Then, once they've signed-off on the Scripts, us DBAs evaluate and deploy them.

    As far as deploying it to multiple servers, that's always been easy. Just setup multiple Connections to the DB Project and depending on the User's rights (we use NT Groups here) they either can or can't script the objects on that Server.

    I'm just trying to get some "Pros" of using this because I haven't seen one yet.

  • Automatic verification of changes to the scripts as you save them is a pretty large advantage. Refactoring object names is great. The Static Code Analysis is getting a lot more use. Using this tool & MSBuild we've been able to automate our deployments in a way that just wasn't possible just using the old style DB projects. You do have to buy into the tools approach in order to make it work well. We've found that managing all the scripts individually, rather than in groups, has its advantages too. Still, it's not for everyone.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • tymberwyld (2/4/2008)


    Hey guys. I've started to try and evaluate the VSDB and I wasn't really impressed. I'm so used to having more control over how things are structed by using a simple "Database Project" (which comes with any version of VS Pro and above). When I attempted to put all the "Login" scripts into one file, VS just barked at me and said I didn't know what I was doing. Also, in some cases it's nice to add a Sub-folder to my Procs folder to categorize the Procedures (because we tend to have 200+ Procs). Were currently pushing Developers to use the Projects instead of going into the databases directly so that more can be controlled using VSS (yes, I don't like it either but it works). Then, once they've signed-off on the Scripts, us DBAs evaluate and deploy them.

    As far as deploying it to multiple servers, that's always been easy. Just setup multiple Connections to the DB Project and depending on the User's rights (we use NT Groups here) they either can or can't script the objects on that Server.

    I'm just trying to get some "Pros" of using this because I haven't seen one yet.

    tymberwyld,

    I could give you a fairly sizable list. Top of that list would probably (for me) be the Schema Compare and Data Compare features. I love the ability to easily sync up different environments against each other or against your project. Hugely valuable.

    -Jamie

  • I really believe the biggest benefit is the ability to check and validate objects on the fly. This will point out errors with the changes as you make them; this is nice. As far as the build in schema compare and data compare Red Gate does a FAR better job at this. Of course you have to pay for those tools but the added cost is worth it. DBPro does not allow you, from what I can see, the ability to set up a reusable definition of your comparison projects something that Red Gate does nicely.

  • Thanks for the info. I guess I just need to let go and trust the "tools".

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply