Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Deploying Databases From Visual Studio Team System Database Edition Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 1:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:59 AM
Points: 14,796, Visits: 27,270
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #741329
Posted Thursday, June 25, 2009 7:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 06, 2014 6:58 PM
Points: 219, Visits: 823
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.
Post #741813
Posted Thursday, June 25, 2009 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:59 AM
Points: 14,796, Visits: 27,270
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #741849
Posted Wednesday, July 08, 2009 8:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 06, 2014 6:58 PM
Points: 219, Visits: 823
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
Post #749810
Posted Wednesday, July 08, 2009 8:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 06, 2014 6:58 PM
Points: 219, Visits: 823
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
Post #749815
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse