Applies to: xSQL Schema
Compare v7.0.0 – 9.0.2
Before I develop a scenario where
xSQL Schema Compare snapshot comparison is necessary let’s explain what xSQL
Schema snapshots are. An xSQL Schema snapshot is a compact file that
contains all the schema information for a database. In simple words, an xSQL
Schema snapshot is basically a picture of a database’s schema at a specific
moment in time. These snapshots are very useful if you want to keep track and
get a clear picture of the evolution of a database, by comparing the snapshots
with each other or with the live database.
Scenario: Naturally, the
question that arises is “Where and how would one use this feature?”. Let’s, for
the purpose of this article, take the place of a software developer in a
relatively big company. In most cases the development department is separate
from data management which means that developers do not have any access to the
live databases because these databases are managed by entirely different
persons. So how would the developer go about updating the live database with
the schema changes made in the development database after the development of a
new feature is completed? Direct database comparison is not possible because no
party has access to both databases. Simply sending an email to the DBA with the
changes you need to be made is out the question because it’s error prone and
for anything more than very minor changes, it is not feasible.
Solution: Use snapshots! Ideally, the DBA who manages the live database would create a simple job that periodically takes a schema snapshot of the live database and dumps it into a shared repository to which the developers have access. These snapshots would then be used by the developers for auditing and debugging purposes. In this case, you can use these snapshots to compare the
development database with a snapshot of the live database, generate the
synchronization script and send it to the DBA to execute it on the database.
Let’s see how this is done.
For demonstration purposes I
created a copy of the Northwind database and took a snapshot of the copy with
xSQL Schema Compare. This copy will serve as the live database. Then I added a
column on one of the tables in the original database which will serve as the
development database. The picture below shows the snapshot in xSQL Schema
Compare:
The comparison process for
databases and snapshots is exactly the same. For the left database I am using
the NORTHWND which has the extra column and instead of the right database I
added the snapshot.
After choosing the database and
the snapshot, I ran the comparison and xSQL Schema Compare found the difference
in the ‘Categories’ table, for which, the left database has one additional
column.
If I generate the synchronization
script for the right database, it will generate this SQL Statement:
So, at this moment, I as a
developer, have the synchronization script which I can send to the DBA and let
him/her make the change to the live database represented in this demo by
NorthwindCopy. And all of this was done without needing to access the database
from which the snapshot was taken.
In conclusion, besides this
scenario, xSQL Schema Compare’s snapshots can be used to stimulate a very
basic version control for the database’s schema. For example, if you as a
developer are not sure, which is the best database construct for a new feature
you are about to implement, you can take a snapshot of the database, make the
changes you need to do to test the database construct for that new feature, and
if something does not work the way it should, you can always roll back these
changes by synchronizing the database with the snapshot.
Posts
0 comments:
Post a Comment