Deploying Databases From Visual Studio Team System Database Edition

  • Grant Fritchey

    SSC Guru

    Points: 395275

    Comments posted to this topic are about the item Deploying Databases From Visual Studio Team System Database Edition

    ----------------------------------------------------
    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    - Be sure to add GDR R2 to your vstedba (datadude) because it adds some nice db-features.

    ( http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed )

    FYI: The Datadude's lair: http://blogs.msdn.com/gertd/default.aspx 😉

    I'm not yet a user, but I think if you get the grip, you'll be hooked in a positive way :w00t:

    There's more to datadude than only the schema !

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Nice article Grant.

    I'm still not the best friend of SQL integration in Visual Studio but I think the new database projects become better and better and it seems to be a good way to maintain base structure. Anyway I hope it will become better in Visual Studio 2010 and/or SQL Server 2011...

    Thanks

  • Grant Fritchey

    SSC Guru

    Points: 395275

    ALZDBA (6/24/2009)


    - Be sure to add GDR R2 to your vstedba (datadude) because it adds some nice db-features.

    ( http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed )

    FYI: The Datadude's lair: http://blogs.msdn.com/gertd/default.aspx 😉

    I'm not yet a user, but I think if you get the grip, you'll be hooked in a positive way :w00t:

    There's more to datadude than only the schema !

    Absolutely. The R2 release of GDR is a must. The GDR itself created the situation that allows the compound projects to work. The R2 release ironed out a lot of bugs.

    ----------------------------------------------------
    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

    SSC Guru

    Points: 395275

    Florian Reischl (6/24/2009)


    Nice article Grant.

    I'm still not the best friend of SQL integration in Visual Studio but I think the new database projects become better and better and it seems to be a good way to maintain base structure. Anyway I hope it will become better in Visual Studio 2010 and/or SQL Server 2011...

    Thanks

    Thanks Flo. I think there's nothing but improvements on the horizon for this. One thing that will cause... friction, is that in 2010, it's no longer a seperate product, but built right into the Developers Edition of Visual Studio Team System.

    ----------------------------------------------------
    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

    SSC Guru

    Points: 395275

    In addition to Gert's blog, Barclay Hill is posting on best practices with VSTS:DBE. It's worth a read. http://blogs.msdn.com/bahill/default.aspx

    ----------------------------------------------------
    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

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    Grant,

    What do I need Active solution configuration and Active solution platform for?

    This is a database project, not a C# one, so why am I exposed to and maybe even allowed to select Any CPU/x86/64 at all? These settings are completely irrelevant to my database development, are they not?

    How does creating a server project speed up my development?

    Could it be faster not to create it at all?

  • Grant Fritchey

    SSC Guru

    Points: 395275

    Alexander Kuznetsov (6/24/2009)


    Grant,

    What do I need Active solution configuration and Active solution platform for?

    This is a database project, not a C# one, so why am I exposed to and maybe even allowed to select Any CPU/x86/64 at all? These settings are completely irrelevant to my database development, are they not?

    Yes, the CPU is irrelevant to your project (although I've never tried switching it). But the configuration works to allow you to set up environments and have the settings change on projects to match the environments.

    How does creating a server project speed up my development?

    Could it be faster not to create it at all?

    Creating a server project doesn't speed or slow your development. It acts as a placehoder for server level objects, such as Logins. When you make a compound project using the server project, you can then create users that map to logins without errors within the project. Otherwise, if you try to map users to logins, you will get errors.

    ----------------------------------------------------
    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

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    Grant Fritchey (6/24/2009)


    Alexander Kuznetsov (6/24/2009)


    Grant,

    What do I need Active solution configuration and Active solution platform for?

    This is a database project, not a C# one, so why am I exposed to and maybe even allowed to select Any CPU/x86/64 at all? These settings are completely irrelevant to my database development, are they not?

    Yes, the CPU is irrelevant to your project (although I've never tried switching it). But the configuration works to allow you to set up environments and have the settings change on projects to match the environments.

    I guess I know how and why configuration works, but IMO it is completely irrelevant so it shouldn't be there at all. Why do I need to waste my time to "have the settings change on projects to match the environments." Who cares?

    How does creating a server project speed up my development?

    Could it be faster not to create it at all?

    Creating a server project doesn't speed or slow your development. It acts as a placehoder for server level objects, such as Logins. When you make a compound project using the server project, you can then create users that map to logins without errors within the project. Otherwise, if you try to map users to logins, you will get errors.

    Well as a database developer I don't know and I don't need to know what the logins are, at least in the environment I work for. I create roles and grant permissions to them, and that's it, and that ends at the database level. Having to create a server project that I don't need seems to be a waste of time.

    Having an unnecessary project sit in my solution is an even bigger waste of time, as it clutters my screen, slows down my searches, check ins, check outs etc.

    Am I missing something?

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Alexander Kuznetsov (6/24/2009)


    I guess I know how and why configuration works, but IMO it is completely irrelevant so it shouldn't be there at all. Why do I need to waste my time to "have the settings change on projects to match the environments." Who cares?

    Me 😉

    Since this project types are usually used for SQL Server 2005 or later the CPU configuration becomes really important if you start to develop CLR modules for your database. Maybe this information is a missing part but the configuration is not irrelevant in my opinion.

  • Grant Fritchey

    SSC Guru

    Points: 395275

    Alexander Kuznetsov (6/24/2009)


    I guess I know how and why configuration works, but IMO it is completely irrelevant so it shouldn't be there at all. Why do I need to waste my time to "have the settings change on projects to match the environments." Who cares?

    You would only care if you're trying to deploy to lots of different environments. If I have to get the same database to two, three, five, different environments, but, there are different settings, security is the one example I use because it's the most common problem we run into, required for each of those environments, you need a mechanism to tell the database to deploy differently AND the same in each environment. The "same" part of that deployment is the core database project. This project will need to have connection strings to each of the various environments. In prior versions of Data Dude, I used command line batches with variable settings to be able to deploy one project to multiple locations. With the configuration, I get two things, the ability to change the connection string AND the ability to store it with the project, in source control, so that more than one DBA can grab the project and repeatedly and reliably deploy it.

    Well as a database developer I don't know and I don't need to know what the logins are, at least in the environment I work for. I create roles and grant permissions to them, and that's it, and that ends at the database level. Having to create a server project that I don't need seems to be a waste of time.

    Having an unnecessary project sit in my solution is an even bigger waste of time, as it clutters my screen, slows down my searches, check ins, check outs etc.

    Am I missing something?

    I think so. If the only deployment you care about is on a single machine, yeah, you sure don't need this hassle. But when you have to deploy this to Dev, QA, CI, Financtial Testing, Training, Performance Testing, Staging and Production, the other QA enviroment because we're working of a branched version of the code right now, the sandbox server, etc., ... You might want a mechanism for maintaining which set of security is in use in which environment and a way to store that information with the project.

    ----------------------------------------------------
    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

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    Grant Fritchey (6/24/2009)

    Well as a database developer I don't know and I don't need to know what the logins are, at least in the environment I work for. I create roles and grant permissions to them, and that's it, and that ends at the database level. Having to create a server project that I don't need seems to be a waste of time.

    Having an unnecessary project sit in my solution is an even bigger waste of time, as it clutters my screen, slows down my searches, check ins, check outs etc.

    Am I missing something?

    I think so. If the only deployment you care about is on a single machine, yeah, you sure don't need this hassle. But when you have to deploy this to Dev, QA, CI, Financtial Testing, Training, Performance Testing, Staging and Production, the other QA enviroment because we're working of a branched version of the code right now, the sandbox server, etc., ... You might want a mechanism for maintaining which set of security is in use in which environment and a way to store that information with the project.

    I want the opposite - I want security on test boxes to closely match production security, and I want no hassle at all. That's what I use roles for. Also I absolutely do not want to give write access to my solution to the people who actually grant roles to groups.

  • Grant Fritchey

    SSC Guru

    Points: 395275

    Alexander Kuznetsov (6/25/2009)


    I want the opposite - I want security on test boxes to closely match production security, and I want no hassle at all. That's what I use roles for. Also I absolutely do not want to give write access to my solution to the people who actually grant roles to groups.

    Exactly! We use roles for similar results, but our security is a bit different than yours. So, if you want to also deploy the logins and users with VSTS:DB, you'll have to define them in scripts. But, if you define them in the standard scripts, they won't resolve correctly when the engine checks the syntax. You create a server project where you place the logins, and associate that server project with your database project and then the scripts will resolve. Then, you need to take into account that the same database that you're deploying in dev, that has, I assume, two sets of behaviors, a straight out of production copy, so that developers can write code against it, and a developers copy that allows them to do whatever you allow developers to do (in our case, create procs, but not tables or indexes, or views, etc.). Now you have to have a method for deploying to to places, with two different sets of security.

    That's why we're doing the stuff we're doing with the compound projects and the configurations.

    It's not for everyone, certainly. But if you find yourself in similar circumstance, and you need to be able to repeatedly, accurately, quickly, do lots of deployments to lots of environments, I haven't found any methods or tools that work quite as well as what we have now.

    It's not the only way to skin the cat. Previous to the GDR release, we added post-deployment scripts to the projects. In these we would put a query that tested the server where the script was being run, by name. We would create appropriate security through these scripts. That approach is a little less intrusive than the method I've outlined here, but it's also MUCH less visible. That invisibility caused a lot of failed deployments. People would assume a particular system had been set up and deploy the database, only to have security fail. This approach requires only a little more work than the other one, but it's very visible. So you know whether or not a given server or environment has been set up for deployment.

    ----------------------------------------------------
    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

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    I want all these features completely out my way, disabled, invisible, so that there is no chance I can ever use them. Direct quote from Joel Spolski:

    "But wait!" you say. "It's important to have options for advanced users who want to tweak their environments!" In reality, it's not as important as you think.

    The article I am quoting from is a must read: http://www.joelonsoftware.com/uibook/chapters/fog0000000059.html

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    I want all these features completely out my way, disabled, invisible, so that there is no chance I can ever use them. Direct quote from Joel Spolski:

    "But wait!" you say. "It's important to have options for advanced users who want to tweak their environments!" In reality, it's not as important as you think.

    The article I am quoting from is a must read: http://www.joelonsoftware.com/uibook/chapters/fog0000000059.html

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

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