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


How can we standardize SQL Server Data Deployments?


How can we standardize SQL Server Data Deployments?

Author
Message
Rodney Landrum
Rodney Landrum
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 716
Comments posted to this topic are about the item How can we standardize SQL Server Data Deployments?
blandry
blandry
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 723
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...
Ian Massi
Ian Massi
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2855 Visits: 2197
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.
IowaDave
IowaDave
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 517
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.
cy-dba
cy-dba
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1571 Visits: 963
I don't have anything constructive to say, however, I love this metaphor: "...flung from afar by the developer’s trebuchet"
lionfan91
lionfan91
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5350 Visits: 431
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



Adam Gojdas
Adam Gojdas
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 1451
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 ...
John M Dennis
John M Dennis
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 432
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
Toby Harman
Toby Harman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1209 Visits: 670
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.
david.wright-948385
david.wright-948385
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 991
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.
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