SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deploying Databases From Visual Studio Team System Database Edition


Deploying Databases From Visual Studio Team System Database Edition

Author
Message
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40184 Visits: 32654
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Alexander Kuznetsov
Alexander Kuznetsov
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 824
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
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40184 Visits: 32654
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 VSTSBigGrinB, 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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Alexander Kuznetsov
Alexander Kuznetsov
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 824
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
Alexander Kuznetsov
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 824
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search