December 15, 2009 at 8:26 pm
Comments posted to this topic are about the item How can we standardize SQL Server Data Deployments?
December 16, 2009 at 6:12 am
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.
December 16, 2009 at 6:38 am
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.
December 16, 2009 at 7:08 am
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.
December 16, 2009 at 7:36 am
I don't have anything constructive to say, however, I love this metaphor: "...flung from afar by the developer’s trebuchet"
December 16, 2009 at 7:37 am
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
December 16, 2009 at 12:03 pm
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 ...
December 16, 2009 at 3:01 pm
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
December 16, 2009 at 4:12 pm
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.
December 17, 2009 at 6:18 am
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.
December 17, 2009 at 10:02 am
Adam Gojdas (12/16/2009)
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 ...
I agree, but I add an additional check.
if object_id(N'[ProcName]','P') is null and object_id(N'[ProcName]') is not null
raiserror('Object name in use', 21, 1) with log;
That aborts the script if, for example, there's a view with the name you want for the proc. I then follow that with an Else and then pretty much what you have. (I use "return" instead of "select 'stub', but that's immaterial.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2009 at 10:05 am
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.
I agree that DBA skills vary all over the spectrum, but there should be someone who acts as the gatekeeper between test/QA and production. Regardless of job title, that person is functionally a/the DBA, and should have standard procedures for this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2009 at 2:58 pm
GSquared (12/17/2009)
I agree, but I add an additional check.
if object_id(N'[ProcName]','P') is null and object_id(N'[ProcName]') is not nullraiserror('Object name in use', 21, 1) with log;
That aborts the script if, for example, there's a view with the name you want for the proc. I then follow that with an Else and then pretty much what you have. (I use "return" instead of "select 'stub', but that's immaterial.)
I think you bring up a good point as a better practice I should be checking for the actual object type like this:
IF OBJECT_ID(N'[dbo].[Procedure_Name]','P') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE
[dbo].[Procedure_Name]
AS BEGIN SELECT ''STUB'' END');
END;
because if there is an object with that name and it is not a stored proc my script will fail...
And since/if I don't have a rule for different object types having the same name in the DB then I could opt not to do the additional checking you have.
December 18, 2009 at 6:48 am
Adam Gojdas (12/17/2009)
GSquared (12/17/2009)
I agree, but I add an additional check.
if object_id(N'[ProcName]','P') is null and object_id(N'[ProcName]') is not nullraiserror('Object name in use', 21, 1) with log;
That aborts the script if, for example, there's a view with the name you want for the proc. I then follow that with an Else and then pretty much what you have. (I use "return" instead of "select 'stub', but that's immaterial.)
I think you bring up a good point as a better practice I should be checking for the actual object type like this:
IF OBJECT_ID(N'[dbo].[Procedure_Name]','P') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE
[dbo].[Procedure_Name]
AS BEGIN SELECT ''STUB'' END');
END;
because if there is an object with that name and it is not a stored proc my script will fail...
And since/if I don't have a rule for different object types having the same name in the DB then I could opt not to do the additional checking you have.
Your version will raise a priority 16 error that will abort that batch, but it will then continue to run from the next GO statement, which can cause problems, and is annoying even if it doesn't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2009 at 8:02 am
GSquared (12/18/2009)
Your version will raise a priority 16 error that will abort that batch, but it will then continue to run from the next GO statement, which can cause problems, and is annoying even if it doesn't.
I now see what you mean. I finally tried running a create with a view and stored procedure using the same name. I was not aware there would be an issue with naming a view and a stored procedure the same, bad assumption on my part. I guess I have never tried to do that so I had never ran across it. Should have tested what you were saying first! Will have to adopt the way you have as it is probably the best practice for this type of method.
thanks
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply