SQL - Source Control and Schema/Script management

My company has just gone though its annual review process, and I have finally convinced them that it is time to find a better solution to manage our SQL schema/scripts. Currently, we only have a few scripts to manually update.

I've worked with VS2008 Database Edition at a different company and that is an awesome product. My boss has asked me to take a look at SQL Compare by Redgate and to look for any other products that may be better. SQL Compare appears to be a great product also. However, it does not look like they have 开发者_如何学运维support for Perforce.

Have you used a variety of products for this?

What tools do you use to manage SQL?

What should be included in the requirements before my company makes a purchase?

I'll link to other answers I gave to other questions related to Source Control for databases

Is RedGate SQL Source Control for me?

Exposing SQL Server database objects as files in a file system

I'm in the "script it yourself" camp, as third-party products will only get you so far at managing database code. I don't have one script per object, because objects change over time, and nine times out of ten merely updating my "create table" script to have three new columns would be inadequate.

Creating databases is, by and large, trivial. Set up a bunch of CREATE scripts, order them properly (create database before schemas, schemas before tables, tables before procedures, called procedures before calling procedures, etc), and your done. Managing database change is not nearly as simple:

• If you add a column to a table, you can't just drop the table and create it with the new column, because doing so would wipe out all your valuable Production data.
• If Fred adds a column to table XYZ and Mary adds a different column to table XYZ, which column gets added first? Yes, order of columns in tables doesn't matter [because you never use SELECT *, right?] unless you're trying to manage the database and keep track of versioning, at which point having two "valid" databases that don't look like each other becomes a real headache. We use SQL compare not to manage but to review and keep track of things, particularly during development, and the few "these are different (but it doesn't magger)" situations we have can actively prevent us from noticing the differences that do matter.
• Similarly, when when multiple projects (developers) are working simultaneously and separately on a shared database, it can get very tricky. Perhaps everyone's working on the Next Big Thing project, when suddenly someone has to start work on bug fixes on the Last Big Thing project. How do you manage the required code modifications when the order of release is variable and flexible? (Fun times indeed.)
• Changing table structures means changing data, and that can become hellishly complex when you have to deal with backwards compatibility. You add an "DeltaFactor" column, ok, so what do you do to populate this esoteric value for all your existing (read: legacy) data? You add a new lookup table and related column, but how do you populate it for existing rows? Such situations may not happen often, but when they do, you have to do it yourself. Third-party tools simply cannot anticipate your business logic needs.

Essentially, what I have is a CREATE script for each database, followed by a series of ALTER scripts as our code base changes over time. Every script checks whether or not it can be run: is this the right "kind" of database, have the necessary prerequisite scripts been run, has this script already been run. Only when the checks are passed will the script perform its changes.

Tool-wise, we use SourceGear Fortress for basic source control, Redgate SQL Compare for general support and trouble-shooting, and a number of home-grown scripts based on SQLCMD for "bulk" deployment of the alter scripts to multiple servers and databases and to track who applied what scripts to which databases at what time. End result: all our databases are consistant and stable, and we can readly prove what version any one is or was at any point in time.

I don't think there is a tool that can handle all the pieces. VS Database Edition falls short of offering a decent release mechanism. Running individual scripts from the solution explorer does not scale well in larger projects.

At a minimum you need

• an IDE/editor
• a source code repository that can be ingrated with your IDE
• a convention for naming and organizing your various scripts into folders
• a process for handling changes, managing releases, and doing deployments

The last bullet is where things usually break down. Here is why. For better managability and version tracking, you want to keep each db object into its own script file. I.e. each table, stored procedure, view, index, etc has its own file.

When something changes, you update the file, and you have a new version in your repository with the information that you need. When it comes to bundling a number of changes into a release, dealing with individual files can be cumbersome.

2 options that I have used:

• In addition to keeping all the individual database objects in their files, you have release scripts that are a concatenation of the individual scripts. The downside of this: you have code in 2 places, with all the risks and disadvantages. The upside: running a release is as simple as executing a single script.

• write a little tool that can read script metadata from a release manifest and execute eadch script that is listed in the manifest on the target server. There is no downside to this, except that you have to write code. This approach does not work for tables that can't be dropped and recreated (once you are live and have data), so for tables you will have change scripts anyways. So in reality, this will be combination of both approaches.

We require all database changes or inserts to things like lookup tables to be scripted and stored in source control. They are then deployed the same way any other code for a version of the software is deployed. Since our developers do not have the rights to deploy, they have no choice but to create the scripts.

We use LiquiBase to keep control of database changes:

Liquibase is an open source (Apache 2.0 Licensed), database-independent library for tracking, managing and applying database changes.

I am usually use MS Server Management Studio for managing sql, working with data, developing databases and debug its, if i need to export some data as sql script or i need to create some difficult object in database, i use EMS SQL Management Studio for SQL Server, because there I can more clearly see the narrow sections of my code and visual design in this environment gives me easier

Red Gate SQL Source Control is working to support additional source control systems. If you're interested in Perforce support, please vote/comment on https://redgate.uservoice.com/admin/forums/39019-sql-source-control/suggestions/863715-support-perforce-, which we will update when/if support becomes available.

Try "SQL Effects Clarify" which is pretty good tool to compare most of the objects including row counts for FREE. Also there are tools that compares data too.

I have an open-source (licensed under LGPL) toolset project which tries to address the issues related to proper DB schema versioning for (and more) SQL Server (2005/2008/Azure), the bsn ModuleStore. The whole process is very close to the concept explained by Philip Kelley's post here.

Basically, the standalone part of the toolset scripts the SQL Server DB objects of a DB schema into files with a standard formatting applied, so that the file contents only changes if the object really did change (very much in contrast to the scripting done by VS, which scripts some scripting date etc. as well, marking all objects as changed even if they are in fact identical).

But the toolset goes beyond that if you use .NET: it allows you to embed the SQL scripts into the library or application (as embedded resources) and then have it compare the embedded scripts with the current state in the database. Non-table-related changes (those that are not "destructive changes" as per Martin Fowler's definition) can be applied automatically or on request (e.g. creating and removing objects such as views, functions, stored procedures, types, indexes), and change scripts (which need to be written manually though) can be applied in the same process as well; new tables are also created, optionally along with their setup data. After the update, the DB schema is again compared against the scripts in order to ensure a successful DB upgrade before the changes are committed.

Note that the whole scripting and comparison code works without SMO, so that you don't have the painful SMO dependency when using the bsn ModuleStore in applications.

Depending on how you want to access the database, the toolset offers even more - it implements some ORM capabilities and offers a very nice and useful interface-based approach to invoke stored procedures, including transparent support for XML with native .NET XML classes and also for TVPs (Table-Valued Parameters) as IEnumerable<PocoClass>.

Here is my script to track stored proc and udf and triggers into a table.

1. Create a table to hold existing stored proc source code

2. INsert the table with all existing trigger and script data

3. Create a DDL trigger to monitor changes on them

           /****** Object:  Table [dbo].[sysupdatelog]    Script Date: 9/17/2014 11:36:54 AM ******/
           SET ANSI_NULLS ON
           GO
   
           SET QUOTED_IDENTIFIER ON
           GO
   
                           CREATE TABLE [dbo].[sysupdatelog] (
                               [id] [bigint] IDENTITY(1, 1) NOT NULL
                               ,[UpdateUsername] [nvarchar](1000) NULL
                               ,[actionname] [nvarchar](1000) NULL
                               ,[objectname] [nvarchar](1000) NULL
                               ,[type] [nvarchar](1000) NULL
                               ,[createDate] [datetime] NULL
                               ,[content] NTEXT NULL
                               ,CONSTRAINT [PK_sysupdatelog] PRIMARY KEY CLUSTERED ([id] ASC) WITH (
                                   PAD_INDEX = OFF
                                   ,STATISTICS_NORECOMPUTE = OFF
                                   ,IGNORE_DUP_KEY = OFF
                                   ,ALLOW_ROW_LOCKS = ON
                                   ,ALLOW_PAGE_LOCKS = ON
                                   ) ON [PRIMARY]
                               ) ON [PRIMARY]
                           GO
   
           ALTER TABLE [dbo].[sysupdatelog] ADD CONSTRAINT [DF__sysupdate__conte__4EDE7CE6] DEFAULT('')
           FOR [content]
           GO
   
           INSERT INTO [dbo].[sysupdatelog] (
               [UpdateUsername]
               ,[actionname]
               ,[objectname]
               ,[type]
               ,[createDate]
               ,[content]
               )
           SELECT 'sa'
               ,'loginitialdata'
               ,r.ROUTINE_NAME
               ,r.ROUTINE_TYPE
               ,GETDATE()
               ,r.ROUTINE_DEFINITION
           FROM INFORMATION_SCHEMA.ROUTINES r
   
           UNION
   
           SELECT 'sa'
               ,'loginitialdata'
               ,v.TABLE_NAME
               ,'view'
               ,GETDATE()
               ,v.VIEW_DEFINITION
           FROM INFORMATION_SCHEMA.VIEWS v
   
           UNION
   
           SELECT 'sa'
               ,'loginitialdata'
               ,o.NAME
               ,'trigger'
               ,GETDATE()
               ,m.DEFINITION
           FROM sys.objects o
           JOIN sys.sql_modules m ON o.object_id = m.object_id
           WHERE o.type = 'TR'
           GO
   
           CREATE TRIGGER [SCHEMA_AUDIT] ON DATABASE
           FOR CREATE_PROCEDURE
               ,ALTER_PROCEDURE
               ,DROP_PROCEDURE
               ,CREATE_INDEX
               ,ALTER_INDEX
               ,DROP_INDEX
               ,CREATE_TRIGGER
               ,ALTER_TRIGGER
               ,DROP_TRIGGER
               ,ALTER_TABLE
               ,ALTER_VIEW
               ,CREATE_VIEW
               ,DROP_VIEW AS
   
           BEGIN
               SET NOCOUNT ON
   
               DECLARE @data XML
   
               SET @data = Eventdata()
   
               INSERT INTO sysupdatelog
               VALUES (
                   @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(255)')
                   ,@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)')
                   ,@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)')
                   ,@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(255)')
                   ,getdate()
                   ,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
                   )
   
               SET NOCOUNT OFF
           END
           GO
   
           SET ANSI_NULLS OFF
           GO
   
           SET QUOTED_IDENTIFIER OFF
           GO
   
           ENABLE TRIGGER [SCHEMA_AUDIT] ON DATABASE
           GO