|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What is best way to manage database changes?I'm wondering what "best practices" there are out there for keeping control
on what has been deployed to a database. I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that keep up with incremental changes in a database (e.g. new procs, changes to procs, tables, etc). But I never thought it was ideal. So, what is? What is anyone else doing out there? Thanks, Ron Cook I rely on version control software like Visual Source Safe for this.
"Ronald S. Cook" <rc***@westinis.com> wrote in message I'm wondering what "best practices" there are out there for keeping controlnews:O4Gpf$opGHA.148@TK2MSFTNGP04.phx.gbl... on what has been deployed to a database. I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that keep up with incremental changes in a database (e.g. new procs, changes to procs, tables, etc). But I never thought it was ideal. So, what is? What is anyone else doing out there? Thanks, Ron Cook Choice #1 -Source Control
Choice #2 -Source Control Choice #3 -Source Control And if those don't work for you, there is always Source Control. Also having a Compare product can be useful (SQLCompare, SQLDiff, etc.) -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Ronald S. Cook" <rc***@westinis.com> wrote in message news:O4Gpf$opGHA.148@TK2MSFTNGP04.phx.gbl... > I'm wondering what "best practices" there are out there for keeping > control on what has been deployed to a database. > > I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that > keep up with incremental changes in a database (e.g. new procs, changes to > procs, tables, etc). But I never thought it was ideal. > > So, what is? What is anyone else doing out there? > > Thanks, > Ron Cook > Ronald S. Cook wrote:
> I'm wondering what "best practices" there are out there for keeping control Always script your database changes, never edit objects directly via > on what has been deployed to a database. > > I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that > keep up with incremental changes in a database (e.g. new procs, changes to > procs, tables, etc). But I never thought it was ideal. > > So, what is? What is anyone else doing out there? > > Thanks, > Ron Cook > > Enterprise Manager or Query Analyzer. Store those scripts in some sort of version control software, like SourceSafe or Perforce. This gives you two benefits: full version history, and an automatic "backup" of your database schema. I should have mentioned that we do use source control. But, what do you
suggest putting in there? One master .sql file fo reverything? What if I need to change one stored procedure? Should that be a new script in source control or what? Thanks, Ron Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:%23uLkxFppGHA.2256@TK2MSFTNGP03.phx.gbl... > Ronald S. Cook wrote: >> I'm wondering what "best practices" there are out there for keeping >> control on what has been deployed to a database. >> >> I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that >> keep up with incremental changes in a database (e.g. new procs, changes >> to procs, tables, etc). But I never thought it was ideal. >> >> So, what is? What is anyone else doing out there? >> >> Thanks, >> Ron Cook > > Always script your database changes, never edit objects directly via > Enterprise Manager or Query Analyzer. Store those scripts in some sort of > version control software, like SourceSafe or Perforce. This gives you two > benefits: full version history, and an automatic "backup" of your > database schema. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com In new development, I usually keep Table/View schema as one file, and then
each Stored Procedure/Function/Trigger as separate files, each with a IF EXISTS...DROP block, AND a GRANT EXECUTE portion after the GO that finished the sproc. Jobs are also individually scripted out and source controlled. For existing projects, I 'may' change the above to script out each Table individually. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Ronald S. Cook" <rc***@westinis.com> wrote in message news:%23e9tPTqpGHA.4188@TK2MSFTNGP03.phx.gbl... >I should have mentioned that we do use source control. But, what do you >suggest putting in there? One master .sql file fo reverything? What if I >need to change one stored procedure? Should that be a new script in source >control or what? > > Thanks, > Ron > > "Tracy McKibben" <tr***@realsqlguy.com> wrote in message > news:%23uLkxFppGHA.2256@TK2MSFTNGP03.phx.gbl... >> Ronald S. Cook wrote: >>> I'm wondering what "best practices" there are out there for keeping >>> control on what has been deployed to a database. >>> >>> I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) >>> that keep up with incremental changes in a database (e.g. new procs, >>> changes to procs, tables, etc). But I never thought it was ideal. >>> >>> So, what is? What is anyone else doing out there? >>> >>> Thanks, >>> Ron Cook >> >> Always script your database changes, never edit objects directly via >> Enterprise Manager or Query Analyzer. Store those scripts in some sort >> of version control software, like SourceSafe or Perforce. This gives you >> two benefits: full version history, and an automatic "backup" of your >> database schema. >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com > > Ideally, every object should have it's own source control file. That
way you can track versioning down to specific changes on a specific object. Stu Ronald S. Cook wrote: Show quote > I should have mentioned that we do use source control. But, what do you > suggest putting in there? One master .sql file fo reverything? What if I > need to change one stored procedure? Should that be a new script in source > control or what? > > Thanks, > Ron > > "Tracy McKibben" <tr***@realsqlguy.com> wrote in message > news:%23uLkxFppGHA.2256@TK2MSFTNGP03.phx.gbl... > > Ronald S. Cook wrote: > >> I'm wondering what "best practices" there are out there for keeping > >> control on what has been deployed to a database. > >> > >> I've seen some companies have files (e.g. 001.sql, 002.sql, 003.sql) that > >> keep up with incremental changes in a database (e.g. new procs, changes > >> to procs, tables, etc). But I never thought it was ideal. > >> > >> So, what is? What is anyone else doing out there? > >> > >> Thanks, > >> Ron Cook > > > > Always script your database changes, never edit objects directly via > > Enterprise Manager or Query Analyzer. Store those scripts in some sort of > > version control software, like SourceSafe or Perforce. This gives you two > > benefits: full version history, and an automatic "backup" of your > > database schema. > > > > > > -- > > Tracy McKibben > > MCDBA > > http://www.realsqlguy.com Ronald S. Cook wrote:
> I should have mentioned that we do use source control. But, what do you Essentially, you have one script for each object in the database.> suggest putting in there? One master .sql file fo reverything? What if I > need to change one stored procedure? Should that be a new script in source > control or what? > IF EXISTS ( SELECT 'x' FROM sysobjects WHERE id = OBJECT_ID( 'dbo.procSprocName' ) AND OBJECTPROPERTY( id, 'IsProcedure' ) = 1 ) BEGIN DROP PROCEDURE dbo.procSprocName END GO CREATE PROCEDURE dbo.procSprocName ( @P1 int, @P2 datetime, @P3 varchar(20) ) AS /************************************************************************************************* * * Name: * * procSprocName * * Input Parameters: * * @P1 - Description of @P1 * @P2 - Description of @P2 * @P3 - Description of @P3 * * Description: * * Stored procedure description. * * Revision History: * * MM/DD/YYYY - Author411 * Initial Release * * Copyright © 2006 My Company, Inc. All rights reserved. * *************************************************************************************************/ BEGIN SET NOCOUNT ON SET XACT_ABORT ON END GO GRANT EXECUTE ON dbo.vspSprocName TO myusers GRANT EXECUTE ON dbo.vspSprocName TO myadmins GO IF ( @@SERVERNAME = 'DEVSERVER' ) BEGIN GRANT EXECUTE ON dbo.vspSprocName TO MyDevelopers END GO IF ( @@SERVERNAME IN ( 'TESTSERVER1', 'TESTSERVER2' ) ) BEGIN GRANT EXECUTE ON dbo.vspSprocName TO MyQAUsers END GO In addition, EVERY change you make to the database is done with a script. Each change should be made for a reason, i.e. to fix a bug that is tracked in a bug tracking system, or as an enhancement that is tracked in a project tracking system. Each bug/project exists within your source control system, and contains the scripts associated with that bug/project. If you need to add an index to a table, you script it: IF (DB_NAME() NOT IN ('MyDB', 'MyDBDev', 'MyDBTest')) BEGIN RAISERROR ( 'You are not in the correct database. This script should be run in the MyDB database', 16, 1 ) RETURN END GO IF EXISTS(SELECT 'x' FROM dbo.sysindexes WHERE name = 'IDX_EXAREG_DeExVeOrItIDDisp') BEGIN DROP INDEX ExamRegistration.IDX_EXAREG_DeExVeOrItIDDisp END CREATE UNIQUE INDEX IDX_EXAREG_DeExVeOrItIDDisp ON ExamRegistration (DeliveredExamVersionID, OrderItemID, Disposition, ProcessStateCode, ExamResultSequence, RegisteredExamVersionID) Is this easier to manage with this in SQL 2005? I wanted to start using this
method in managing changes to the DB as well. But to include as a process so it is done cross the board. The developers use source control for their code but I am knew here and they are not using it for any database changes. I used for sp, functions, views, etc but any structure change I don't. So after reading this post, I will start doing that. We are gearing up for SQL Server 2005 migration from 2000. I am currently preping the Server with Clustering for HA solution. So this will be good to implement this process as well with the new platform. Show quote "Tracy McKibben" wrote: > Ronald S. Cook wrote: > > I should have mentioned that we do use source control. But, what do you > > suggest putting in there? One master .sql file fo reverything? What if I > > need to change one stored procedure? Should that be a new script in source > > control or what? > > > > Essentially, you have one script for each object in the database. > > IF EXISTS ( SELECT 'x' FROM sysobjects WHERE id = OBJECT_ID( > 'dbo.procSprocName' ) AND OBJECTPROPERTY( id, 'IsProcedure' ) = 1 ) > BEGIN > DROP PROCEDURE dbo.procSprocName > END > GO > > CREATE PROCEDURE dbo.procSprocName > ( > @P1 int, > @P2 datetime, > @P3 varchar(20) > ) > AS > > /************************************************************************************************* > * > * Name: > * > * procSprocName > * > * Input Parameters: > * > * @P1 - Description of @P1 > * @P2 - Description of @P2 > * @P3 - Description of @P3 > * > * Description: > * > * Stored procedure description. > * > * Revision History: > * > * MM/DD/YYYY - Author411 > * Initial Release > * > * Copyright © 2006 My Company, Inc. All rights reserved. > * > > *************************************************************************************************/ > > BEGIN > SET NOCOUNT ON > SET XACT_ABORT ON > > END > GO > > GRANT EXECUTE ON dbo.vspSprocName TO myusers > GRANT EXECUTE ON dbo.vspSprocName TO myadmins > GO > > IF ( @@SERVERNAME = 'DEVSERVER' ) > BEGIN > GRANT EXECUTE ON dbo.vspSprocName TO MyDevelopers > END > GO > > IF ( @@SERVERNAME IN ( 'TESTSERVER1', 'TESTSERVER2' ) ) > BEGIN > GRANT EXECUTE ON dbo.vspSprocName TO MyQAUsers > END > GO > > > In addition, EVERY change you make to the database is done with a > script. Each change should be made for a reason, i.e. to fix a bug that > is tracked in a bug tracking system, or as an enhancement that is > tracked in a project tracking system. Each bug/project exists within > your source control system, and contains the scripts associated with > that bug/project. If you need to add an index to a table, you script it: > > IF (DB_NAME() NOT IN ('MyDB', 'MyDBDev', 'MyDBTest')) > BEGIN > RAISERROR ( 'You are not in the correct database. This script should > be run in the MyDB database', 16, 1 ) > RETURN > END > GO > > IF EXISTS(SELECT 'x' FROM dbo.sysindexes WHERE name = > 'IDX_EXAREG_DeExVeOrItIDDisp') > BEGIN > > DROP INDEX ExamRegistration.IDX_EXAREG_DeExVeOrItIDDisp > > END > > CREATE UNIQUE INDEX IDX_EXAREG_DeExVeOrItIDDisp > ON ExamRegistration (DeliveredExamVersionID, OrderItemID, Disposition, > ProcessStateCode, ExamResultSequence, RegisteredExamVersionID) > > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > SAM wrote:
> Is this easier to manage with this in SQL 2005? I wanted to start using this We're still planning our 2005 migration, but so far have seen nothing to > method in managing changes to the DB as well. > But to include as a process so it is done cross the board. The developers > use source control for their code but I am knew here and they > are not using it for any database changes. > > I used for sp, functions, views, etc but any structure change I don't. So > after reading this post, I will start doing that. > > We are gearing up for SQL Server 2005 migration from 2000. I am currently > preping the Server with Clustering for HA solution. So this will be good to > implement this process as well with the new platform. > indicate that our current methods will be any harder or easier on that platform. DDL scripts are DDL scripts, doesn't matter what database server they're written for. The integration between Management Studio and Sourcesafe doesn't look like it simplifies the process much, and since we use Perforce instead of Sourcesafe, won't work for us anyway. If the budget can absorb it, I HIGHLY recommend investigating Visual Studio
Team System for DB Professionals edition. -Fully incorporated SQL Server back end for source code control. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "SAM" <S**@discussions.microsoft.com> wrote in message news:E1A92E2E-76D8-4C81-B04C-465DA9467920@microsoft.com... > Is this easier to manage with this in SQL 2005? I wanted to start using > this > method in managing changes to the DB as well. > But to include as a process so it is done cross the board. The developers > use source control for their code but I am knew here and they > are not using it for any database changes. > > I used for sp, functions, views, etc but any structure change I don't. So > after reading this post, I will start doing that. > > We are gearing up for SQL Server 2005 migration from 2000. I am currently > preping the Server with Clustering for HA solution. So this will be good > to > implement this process as well with the new platform. > > > "Tracy McKibben" wrote: > >> Ronald S. Cook wrote: >> > I should have mentioned that we do use source control. But, what do >> > you >> > suggest putting in there? One master .sql file fo reverything? What >> > if I >> > need to change one stored procedure? Should that be a new script in >> > source >> > control or what? >> > >> >> Essentially, you have one script for each object in the database. >> >> IF EXISTS ( SELECT 'x' FROM sysobjects WHERE id = OBJECT_ID( >> 'dbo.procSprocName' ) AND OBJECTPROPERTY( id, 'IsProcedure' ) = 1 ) >> BEGIN >> DROP PROCEDURE dbo.procSprocName >> END >> GO >> >> CREATE PROCEDURE dbo.procSprocName >> ( >> @P1 int, >> @P2 datetime, >> @P3 varchar(20) >> ) >> AS >> >> /************************************************************************************************* >> * >> * Name: >> * >> * procSprocName >> * >> * Input Parameters: >> * >> * @P1 - Description of @P1 >> * @P2 - Description of @P2 >> * @P3 - Description of @P3 >> * >> * Description: >> * >> * Stored procedure description. >> * >> * Revision History: >> * >> * MM/DD/YYYY - Author411 >> * Initial Release >> * >> * Copyright © 2006 My Company, Inc. All rights reserved. >> * >> >> *************************************************************************************************/ >> >> BEGIN >> SET NOCOUNT ON >> SET XACT_ABORT ON >> >> END >> GO >> >> GRANT EXECUTE ON dbo.vspSprocName TO myusers >> GRANT EXECUTE ON dbo.vspSprocName TO myadmins >> GO >> >> IF ( @@SERVERNAME = 'DEVSERVER' ) >> BEGIN >> GRANT EXECUTE ON dbo.vspSprocName TO MyDevelopers >> END >> GO >> >> IF ( @@SERVERNAME IN ( 'TESTSERVER1', 'TESTSERVER2' ) ) >> BEGIN >> GRANT EXECUTE ON dbo.vspSprocName TO MyQAUsers >> END >> GO >> >> >> In addition, EVERY change you make to the database is done with a >> script. Each change should be made for a reason, i.e. to fix a bug that >> is tracked in a bug tracking system, or as an enhancement that is >> tracked in a project tracking system. Each bug/project exists within >> your source control system, and contains the scripts associated with >> that bug/project. If you need to add an index to a table, you script it: >> >> IF (DB_NAME() NOT IN ('MyDB', 'MyDBDev', 'MyDBTest')) >> BEGIN >> RAISERROR ( 'You are not in the correct database. This script should >> be run in the MyDB database', 16, 1 ) >> RETURN >> END >> GO >> >> IF EXISTS(SELECT 'x' FROM dbo.sysindexes WHERE name = >> 'IDX_EXAREG_DeExVeOrItIDDisp') >> BEGIN >> >> DROP INDEX ExamRegistration.IDX_EXAREG_DeExVeOrItIDDisp >> >> END >> >> CREATE UNIQUE INDEX IDX_EXAREG_DeExVeOrItIDDisp >> ON ExamRegistration (DeliveredExamVersionID, OrderItemID, Disposition, >> ProcessStateCode, ExamResultSequence, RegisteredExamVersionID) >> >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com >> I don't know if it's any easier with SQL 2005 or not, but it does sound
as if you will run into some problems with people when implementing a source control procedure. Be sure that when you make the change that you also periodically verify that the source control procedures are being followed. If people have been doing things a certain way for a while, it's difficult for them to change behavior. In our shop, we have procedures where we check things in and out of source control suring development work, but we also automatically script our databases to a seperate source control file. This gives us the ability to ensure that any changes that are made outside of our standard operating procedure are also captured. Stu SAM wrote: Show quote > Is this easier to manage with this in SQL 2005? I wanted to start using this > method in managing changes to the DB as well. > But to include as a process so it is done cross the board. The developers > use source control for their code but I am knew here and they > are not using it for any database changes. > > I used for sp, functions, views, etc but any structure change I don't. So > after reading this post, I will start doing that. > > We are gearing up for SQL Server 2005 migration from 2000. I am currently > preping the Server with Clustering for HA solution. So this will be good to > implement this process as well with the new platform. > > > "Tracy McKibben" wrote: > > > Ronald S. Cook wrote: > > > I should have mentioned that we do use source control. But, what do you > > > suggest putting in there? One master .sql file fo reverything? What if I > > > need to change one stored procedure? Should that be a new script in source > > > control or what? > > > > > > > Essentially, you have one script for each object in the database. > > > > IF EXISTS ( SELECT 'x' FROM sysobjects WHERE id = OBJECT_ID( > > 'dbo.procSprocName' ) AND OBJECTPROPERTY( id, 'IsProcedure' ) = 1 ) > > BEGIN > > DROP PROCEDURE dbo.procSprocName > > END > > GO > > > > CREATE PROCEDURE dbo.procSprocName > > ( > > @P1 int, > > @P2 datetime, > > @P3 varchar(20) > > ) > > AS > > > > /************************************************************************************************* > > * > > * Name: > > * > > * procSprocName > > * > > * Input Parameters: > > * > > * @P1 - Description of @P1 > > * @P2 - Description of @P2 > > * @P3 - Description of @P3 > > * > > * Description: > > * > > * Stored procedure description. > > * > > * Revision History: > > * > > * MM/DD/YYYY - Author411 > > * Initial Release > > * > > * Copyright © 2006 My Company, Inc. All rights reserved. > > * > > > > *************************************************************************************************/ > > > > BEGIN > > SET NOCOUNT ON > > SET XACT_ABORT ON > > > > END > > GO > > > > GRANT EXECUTE ON dbo.vspSprocName TO myusers > > GRANT EXECUTE ON dbo.vspSprocName TO myadmins > > GO > > > > IF ( @@SERVERNAME = 'DEVSERVER' ) > > BEGIN > > GRANT EXECUTE ON dbo.vspSprocName TO MyDevelopers > > END > > GO > > > > IF ( @@SERVERNAME IN ( 'TESTSERVER1', 'TESTSERVER2' ) ) > > BEGIN > > GRANT EXECUTE ON dbo.vspSprocName TO MyQAUsers > > END > > GO > > > > > > In addition, EVERY change you make to the database is done with a > > script. Each change should be made for a reason, i.e. to fix a bug that > > is tracked in a bug tracking system, or as an enhancement that is > > tracked in a project tracking system. Each bug/project exists within > > your source control system, and contains the scripts associated with > > that bug/project. If you need to add an index to a table, you script it: > > > > IF (DB_NAME() NOT IN ('MyDB', 'MyDBDev', 'MyDBTest')) > > BEGIN > > RAISERROR ( 'You are not in the correct database. This script should > > be run in the MyDB database', 16, 1 ) > > RETURN > > END > > GO > > > > IF EXISTS(SELECT 'x' FROM dbo.sysindexes WHERE name = > > 'IDX_EXAREG_DeExVeOrItIDDisp') > > BEGIN > > > > DROP INDEX ExamRegistration.IDX_EXAREG_DeExVeOrItIDDisp > > > > END > > > > CREATE UNIQUE INDEX IDX_EXAREG_DeExVeOrItIDDisp > > ON ExamRegistration (DeliveredExamVersionID, OrderItemID, Disposition, > > ProcessStateCode, ExamResultSequence, RegisteredExamVersionID) > > > > > > > > -- > > Tracy McKibben > > MCDBA > > http://www.realsqlguy.com > >  |
|||||||||||||||||||||||
Other interesting topics