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

How can we standardize SQL Server Data Deployments? Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 8:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 12:00 PM
Points: 32, Visits: 605
Comments posted to this topic are about the item How can we standardize SQL Server Data Deployments?
Post #834823
Posted Wednesday, December 16, 2009 6:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 7, 2012 9:23 AM
Points: 304, Visits: 716
I hate to throw a whine at you, but your post presumes that all over the world a DBA can be measured to have the same basic skills from one person to the next. Nothing could be further from the truth. There is NO standard (still!) for what a DBA is, and in my career I have rarely seen any two DBA's with matched skills and depth in them. I've worked with folks who can handle SQL in their sleep, and also worked with "DBA's" who don't know was SSIS is, or who were anointed "DBA" because they could do a backup.

Hence, to make a statement like: "It is the DBA who is responsible for making sure that a deployment does not have any ill effect..." presumes that the DBA has the skills to anticipate and know these ill effects, and that is just completely a fantasy by my experience.

I think your initiating question would serve us all better if it were a statement of what a DBA should know. I would rather interview a potential DBA and ask "What is the standard for SQL Server Data Deployments?" and get an intelligent, somewhat standard answer, than to toss such a question into the air when we have no solid guidelines as to what makes a DBA.


There's no such thing as dumb questions, only poorly thought-out answers...
Post #835023
Posted Wednesday, December 16, 2009 6:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 1,615, Visits: 1,956
At one place I worked, we used to have DTS packages to promote data from one environment to another when necessary when it was on the SQL Server side and the DBA was responsible for it. When a developer was responsible and didn't have the foresight to consult a DBA, then they usually built some kind of custom application for the sole purpose of importing the data.

Later, someone realized that importing from a table in dev or test wasn't ideal since the data could be modified there and no red flags would be raised. So we adopted the process of dumping the data to a text file and either checking it into source control or at least noting the date and time it was last modified. Then the file could be imported with a DTS package. This solution pleased the change control overlords.
Post #835033
Posted Wednesday, December 16, 2009 7:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 3, 2014 8:05 AM
Points: 421, Visits: 370
I think that I disagree with your opinion that developers should provide Create Table or Index scripts. Developers shouldn't have to worry about how a table gets created, that's a DBA's job - especially if they're the "gatekeeper" as you indicate. Those questions you raise - about Identity Columns, indexes, constraints, etc. - developers won't think about nor rarely know the answers to, and I'm not sure a DBA should expect them to. Once those questions are answered - which can be done in concert with the developer - the DBA should be responsible for creating the scripts to get the job done. That way it's done correctly - and nobody gets hurt!

My 2 cents.
Post #835052
Posted Wednesday, December 16, 2009 7:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869, Visits: 963
I don't have anything constructive to say, however, I love this metaphor: "...flung from afar by the developer’s trebuchet"
Post #835078
Posted Wednesday, December 16, 2009 7:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 6:50 AM
Points: 3,808, Visits: 339
If I as a developer have to provide all that information, just let me do it in the first place!

Fortunately that's how it works here. Our DBAs are the "overseers" of the live environment and are the only ones who can create/drop databases and perform all of the administration tasks. The developers responsible for each of the live databases' projects actually have the rights to modify the database elements and data. Using scripts and the god-send RedGate SqlCompare and SqlDataCompare tools, keeping the production databases up-to-speed with the current configuration is a piece of cake.

I do need to caveat that we're a US gov't organizations with a fairly focused mission area.

Steve



Post #835081
Posted Wednesday, December 16, 2009 12:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
Using a DROP/CREATE with an IF EXISTS clause instead of an ALTER statement for deploying SQL Server objects like stored procedures is one example of a standard that can be put in place that will help assure an error free deployment.

I never really cared for this method using a drop. When one has any auditing of the DDL that takes place this always has the extra drop when making changes. So for almost all the changes there is the extra record of a drop, meaning double the audit records for the change.

I wish more people would adopt a format such as this where only 1 time does the create ever happen (when it doesn't exist which is the only time it should) and afterward always just an alter:

IF OBJECT_ID(N'[dbo].[Procedure_Name]') IS NULL 
BEGIN
EXEC ('CREATE PROCEDURE
[dbo].[Procedure_Name]
AS BEGIN SELECT ''STUB'' END');
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Procedure_Name]
AS
/*********************************************************************************************

Description: This stored procedure is just a template to illustrate 1 time create
and thereafter always using alter.

*********************************************************************************************/
SET NOCOUNT ON;

BEGIN TRY
--*******************************************************************
-- try code
--*******************************************************************

RETURN 0; --(SUCCESS)
END TRY
BEGIN CATCH
--*******************************************************************
-- catch code
--*******************************************************************
RETURN -1; --(FAILURE)
END CATCH;
GO

Granted it is a little work to set this up for all the differing objects but I think it benefits the DDL auditing, may help maintain permissions, metadata and other things that get zapped when something is dropped as opposed to being altered. And it just works ...
Post #835363
Posted Wednesday, December 16, 2009 3:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:28 AM
Points: 56, Visits: 333
blandry (12/16/2009)
I hate to throw a whine at you, but your post presumes that all over the world a DBA can be measured to have the same basic skills from one person to the next. Nothing could be further from the truth. There is NO standard (still!) for what a DBA is, and in my career I have rarely seen any two DBA's with matched skills and depth in them. I've worked with folks who can handle SQL in their sleep, and also worked with "DBA's" who don't know was SSIS is, or who were anointed "DBA" because they could do a backup.

Hence, to make a statement like: "It is the DBA who is responsible for making sure that a deployment does not have any ill effect..." presumes that the DBA has the skills to anticipate and know these ill effects, and that is just completely a fantasy by my experience.

I think your initiating question would serve us all better if it were a statement of what a DBA should know. I would rather interview a potential DBA and ask "What is the standard for SQL Server Data Deployments?" and get an intelligent, somewhat standard answer, than to toss such a question into the air when we have no solid guidelines as to what makes a DBA.


While I agree that there is no standard definition of a DBA; I do disagree that Rodney presumes such. But rather, he has defined a typical role of a DBA as "gatekeeper".

It is your very point that a DBA can possess any range of skills and experiences that deployment processes and procedures be well tested and mature. A staging environment (identical to production) to which the DBA can test the deployment process, the developer to validate changes, and the end-user to do acceptance testing is a critical component of a mature model.

In this way, the ability to anticipate or know the ill affects has little consequence as it will be learned in staging without affecting production. This is of course speaking narrowly of the deployment and without consideration of the experience a DBA needs to identify areas of improvement in a database's design. In that respect--it is often a collaboration with the developers.

John D
Post #835466
Posted Wednesday, December 16, 2009 4:12 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:50 PM
Points: 405, Visits: 563
I may be a bit strange in thinking this, but to my mind a DBA should understand what is being deployed (ultimately because we are the ones to support it!) and therefore I encourage DBAs to be involved in the development, from table design and space requirements to support the application.

If you understand the table structure and the data, then deployment should come down to a relatively simple set of requirements that are well understood. You can also identify additional hardware requirements before they become an issue. Various scripting tools exist to allow comparisons and scripting of table moves etc. (not least of which is Red-Gate)

If you leave the DB design up to the application developers, I can pretty much guarantee that it will cause you pain later and create the "Us and Them" scenario that is so prevalent in this industry.

In short, by getting involved earlier, we can become part of the solution rather than part of the problem.
Post #835486
Posted Thursday, December 17, 2009 6:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:48 AM
Points: 988, Visits: 806
Adam Gojdas (12/16/2009)

I never really cared for this method using a drop. When one has any auditing of the DDL that takes place this always has the extra drop when making changes. So for almost all the changes there is the extra record of a drop, meaning double the audit records for the change.

I wish more people would adopt a format such as this where only 1 time does the create ever happen (when it doesn't exist which is the only time it should) and afterward always just an alter...

I've been tentatively evangelising this procedure for quite a while now. It has the sometimes useful benefit that a single SP can be rolled out to a busy database without a rash of application exceptions complaining about not being able to find the SP in the window between drop and create. Of course this can't be seen as carte blanche to roll a set of programmable units unless steps are taken to ensure that access is only granted to all or none of the changes.
Post #835621
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse