Back To Documents
Working With ShiftSchema
Contents
Introduction
This document describes how to set up and use ShiftSchema in your development environment
If you have additional questions regarding the usage of ShiftSchema or are interested
in on-site consultation about ShiftSchema or managing
your databases and source code please get in touch with us.
Back To Top
Development Environment
ShiftSchema is designed to slot unobtrusively into your existing development environment
but it may require some changes to your processes and developer workstations.
Many developers using a single database.
Existing Setup
Traditionally many teams have shared a single development database. The problem
with this is that managing changes can become a tedious and time consuming chore.
When one developer wants to make changes to the database, even something seemingly
simple like dropping a column from a table, other developers can be affected. If
a database change will cause an application to break it can mean lost productivity
and frustration for your developers.
If a database requires an overhaul having to share that database with developers
working on other parts of the application can be very risky. Often code changes
will be committed to the repository prematurely in order to allow other developers
to keep working. Sometimes an important improvement to a database will be avoided
simply because the management issues are overwhelming.
If this sounds like your team ShiftSchema can save you time and money as well help
you produce great quality software.
Source Control
ShiftSchema works best with a checkout -> edit -> merge -> commit source control
system such as Subversion. To use it
with the exclusive locking of a source control system like Microsoft Visual SourceSafe
developers will need to checkout files prior to making changes in the database.
Many developers, each with their own development database.
ShiftSchema does not touch any files the source control system uses to manage a
working copy. Developers use the existing tools provided by the source control system
to update and commit the files created by ShiftSchema.
Individual Development Databases
ShiftSchema requires that each developer have their own private development database.
This database is tied to one working copy at a time and only one instance of ShiftSchema
can connect to the database at any time.
Microsoft SQL Server Express is ideal for this situation. Each developer can run
a local SQL Server instance and create a database in that for each working copy
they need to use.
Without a tool like ShiftSchema managing the changes to each of these databases
can be very time consuming. By monitoring both the working copy files and database
for changes and automatically synchronizing them ShiftSchema makes working with
a private development database seamless.
When ShiftSchema is running it can be minimized to the system tray where it will
only notify the user of errors. It leaves a developer free to go about her work
and any changes to the database will be automatically stored in the repository when
she checks in her other code.
Getting Started
To get started with ShiftSchema you will need to create
a working copy from your existing database. Once that is done you can
create a development database.
Create a new profile that links the working copy to your empty development database.
When you open the profile ShiftSchema will install the required DDL triggers and
create the tables and other objects from your working copy.
Now that ShiftSchema is up and running you can make changes to the database and
they will be automatically reflected in your working copy.
Back To Top
Day To Day Usage
This section describes tasks developers can perform with ShiftSchema.
Existing Databases
The first step when starting out with ShiftSchema is to get existing databases into
source control.
To do this from within ShiftSchema click on the Tools menu and select "Create a
working copy". A dialog will be displayed that requests the SQL Server database
connection details and the location of the working copy to create.
Once the structure of the database has been written to the working copy it can be
committed to the repository.
Creating A Development Database
First create an empty database as well as any objects the database requires that
ShiftSchema is not able to manage. These include Logins and Certificates. See the
Features page for the details of objects that are not
supported.
Once the database has been created start ShiftSchema, click the File menu and then
Profiles. In the Profiles dialog click the New button and enter the details of the
database and the location of the working copy. Click the Save button and then the
Open button.
The first time ShiftSchema accesses a database it will need to install some triggers
and stored procedures. Once it has done this ShiftSchema will push the database
schema from the working copy into the development database, along with any data
that is also stored in the working copy.
The Database Is Out Of Sync
With Microsoft SQL Server 2005 there is a chance that database changes will not
fire the DDL triggers installed by ShiftSchema. More
information here.
If this happens you can force the synchronization of the working copy from the database.
Click on the File menu and then Profiles. In the Profiles dialog click on the name
of the Profile and then the Export button.
Removing ShiftSchema From A Database
The triggers ShiftSchema installs on a database prevent any DDL changes when ShiftSchema
is not running. To remove these triggers go to the File menu and click Profiles.
In the Profiles dialog select the name of the profile and then click the Uninstall
button.
Synchronizing Data Along With Objects
The purpose of ShiftSchema is to help developers get database objects - tables,
stored procedures, views and so on - in and out of a source control repository.
However many applications will have some constant data stored in tables that is
required for the program to function. ShiftSchema can include this data in the working
copy.
In ShiftSchema click on the File menu and the Profiles. Select a profile and click
on the Table Data tab. A list of all tables in the database will be displayed. Check
the boxes next to the tables containing data to be kept in the repository. When
you click the Save button ShiftSchema will copy the data from the tables into the
working copy.
ShiftSchema will automatically track changes to those tables.
ShiftSchema is not intended to provide a complete replication solution. Using this
feature to synchronize large amounts of data is not recommended as it could lead
to performance degradation or excessive memory usage.
Excluding Objects From Synchronization
There are cases when it is not desirable to have ShiftSchema synchronize every object
in a database. For example, an application may create and drop tables or procedures
dynamically.
In this situation ShiftSchema can be directed to exclude certain objects from synchronization.
Go to the File menu and then Profiles. Select the desired profile and click on the
Exclusions tab. Check the boxes next to the objects that are to be excluded.
Excluding an object means that all its child objects will also be excluded. So if
an application needs to create and drop tables on the fly create a schema for those
tables and exclude the schema from synchronization.
ShiftSchema will still need to be running when excluded objects are modified as
the exclusion process is controlled from within the application, not the DDL triggers
in the database.
Back To Top
Releasing Changes
ShiftSchema is a tool for managing a developer's private development database and
keeping it synchronized with code stored in a source control repository. It
should not, under any circumstances be used on a QA or Production database
as it does not consider factors such as security or table and index partitioning.
It also does not guarantee to maintain the data in modified tables.
ShiftSchema does provide a command line application called
ScriptGen for generating a DDL script to be used in your upgrade process.
The recommended steps for using this application are:
Back To Top
Continuous Integration
Using ShiftSchema with continuous integration.
ShiftSchema can also be included in your continuous integration or automated testing
environment. A tool called QuickSync is included that
will perform a one-off synchronization of a database from a working copy.
QuickSync can be called by your continuous integration system to ensure the code
being tested has the correct database available.
Under no circumstances should QuickSync be used on a QA or Production Database.
Like other parts of ShiftSchema it is intended for use in development environments
only. It does not consider factors such as table and index partitioning options
or database security. It also does not guarantee to maintain the data in modified
tables.
Back To Top
Back To Documents