Software developers and SQL Schema Compare

Any software developer who has worked with SQL Server (or any database for that matter) has at some point had to deal with the challenge of propagating the database changes from the development database to the QA/staging database, to the production database. The process is always the same, you start your "application enhancement" project with a clear vision of the outcome, you potentially identify the database objects, tables, views, stored procedures, triggers, functions etc. that you may have to tweak, completely revamp, or write from scratch.

As you move along new changes and additions to the database structure (aka database schema) become necessary - you keep making those changes to the database and depending on your type (from highly organized to totally messy), experience (from decades of experience to a rookie programmer) etc. how you handle those changes may vary from the "meticulously document everything" approach to the "I will remember those changes I am making" approach. 

While the "document everything" approach is commendable it does unfortunately come with a hefty price tag - a lot of extra hours will be spent on that documentation and to make matters worse no intelligent programmer who gets the adrenaline rush from coming up with ingenious approaches to handling technical challenges enjoys dealing with documentation. 

On the other hand, the "I will remember" approach is not only non-commendable but, depending on the complexity and sensitivity of the project it may at times be disastrous - otherwise genius programmers pulling their hair trying to figure out why the application is working on their development environment but is going haywire on the production, operations people going nuts over the malfunctioning of the system, etc. 

So what is an intelligent software programmer to do? 

Fortunately, there is no need to choose between bad and worst, there is no need to spend all those hours documenting everything (don't take this wrong, any good programmer will always take the time to provide sufficient documentation to allow a comparably intelligent human to understand why something was done, who did it and when), nor is there a need to remember what changes you are making. xSQL Software's SQL Schema Compare, allows the user to compare two databases and clearly identify all the changes that have been made on the structure (aka schema) of one database versus the other. But wait, it goes way further than that - it allows the user to generate a safe change script that will apply (transfer) all those changes to the destination database in a single click.

Now that's efficiency, something that would otherwise take hours to do you can handle with SQL Schema Compare in minutes, and you can easily archive the detailed documentation of all the changes you made to the database. Not only is that commendable, but it feels great too, you are doing an outstanding job without having to waste a minute on it. 

Here is where things get even better SQL Schema Compare is completely free, no strings attached, for SQL Server Express edition (aka SQL Express). Download it now and try for yourself. 

Read More

Create database from schema snapshot - SQL Compare SDK

 As we explained in a previous post here, you can easily take schema snapshots using xSQL Software's SQL Compare SDK, and you can commit those schema snapshots into a source control repository to utilize for your database change control process, maintain an audit trail of database schema changes etc. In this post we will show how easy it is to create the database from a previously saved schema snapshot: 

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using xSQL.Schema.Core;
using xSQL.Schema.SqlServer;
using xSQL.SchemaCompare.SqlServer;

namespace xSQL.Sdk.SchemaCompare.Examples
{
    class DatabaseSnapshot
    {
        /// <summary>
        /// Creates the AdventureWorks database from a snapshot file.
        /// </summary>
        public static void CreateDatabaseFromSnapshot()
        {
            SqlDatabase database;
            try
            {
                //--create the database from the snapshot file
                SqlDatabase.CreateFromSnapshot(@"C:\AdventureWorks.snpx");

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}

Download SQL Compare SDK now and try for yourself. 

Read More

SQL Compare bundle - one click comparison

Both of the SQL Compare bundle tools, Schema Compare and Data Compare provide a very simple and intuitive interface that allows you to reduce the operation of comparing the schemas of two databases or the data contained in them, into a single click. 

Obviously, the first time you compare two databases, depending on the scenario, you may need to go through a few steps to:

• choose the object types you may wish to include or exclude from the comparison
• select the comparison options if the default options do not satisfy the requirements of your use-case
• select scripting options
• maybe set some object filters in the case of Schema Compare
• select comparison keys in the case of Data Compare when the tables you are comparing do not have a primary key defined or a unique index
• maybe set row filters in the case of Data Compare. 

However, once you have customized your comparison to meet your specific requirements, every selection you have made is saved into a comparison session which you can then run with just one click. 

Read More

SQL Compare SDK - schema snapshots with 2 lines of code

Using xSQL Software's SQL Compare SDK you can take snapshots of your database schema with just 2 lines of code - well, as you will see below, technically, there are a few extra lines of code needed to set things up, but you are only calling 2 methods, ReadSchema() and SaveToSnapshot():

using xSQL.Schema.Core;
using xSQL.Schema.SqlServer;
using xSQL.SchemaCompare.SqlServer;

namespace xSQL.Sdk.SchemaCompare.Examples
{
    class DatabaseSnapshot
    {
        /// <summary>
        /// Creates a snapshot for the AdventureWorks database
        /// </summary>
        public static void CreateSnapshot()
        {
            SqlServer server;
            SqlDatabase database;
            try
            {
                //--create the SQL Server object, connect using Windows authentication
                server = new SqlServer(@"(local)");

                //--create the database object
                database = server.GetDatabase("AdventureWorks");

                //--attach an event handler to SchemaOperation event in order to get progress information during the schema read
                database.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);

                //--attach an handler to SnapshotOperation event to get snapshot feedback
                database.SnapshotOperation += new EventHandler<SnapshotOperationEventArgs>(database_SnapshotOperation);

                //--read the database schema
                database.ReadSchema();

                //--create the snapshot
                database.SaveToSnapshot(Path.Combine(@"C:\", database.GetDefaultSnapshotFilename()));
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }

        private static void database_SchemaOperation(object sender, SchemaOperationEventArgs e)
        {
            //--exclude verbose messages
            if (e.Message.MessageType != OperationMessageTypeEnum.Verbose)
                Console.WriteLine(e.Message.Text);
        }

        private static void database_SnapshotOperation(object sender, SnapshotOperationEventArgs e)
        {
            //--exclude verbose messages
            if (e.Message.MessageType != OperationMessageTypeEnum.Verbose)
                Console.WriteLine(e.Message.Text);
        }
    }
}

Download the SQL Compare SDK now and try for yourself. 

Read More

Oracle Schema Compare - why are identical objects showing as different

When it comes to comparing two database objects the question whether the two objects are equal or not has a somewhat complicated answer, that is, it depends! The answer depends on what exactly are you comparing those objects on. For example, is "Equal" equal to "equal"? Well, that depends on whether the the character case is relevant to you in that use-case that prompted the comparison in the first place. 

xSQL Software's Oracle Schema Compare tool offers 31 standard comparison options like "ignore the name of the primary key", "ignore the name of the unique constraints", "ignore the name of the check constraints", "compare xml storage", "compare the schema of a synonym based object" etc., and 19 advanced comparison options like "compare the pct. free of a storage clause", "compare the return type of nested table", "compare the segment name of a LOB", etc. Screen shots below show part of those lists that affect the object is "equal or not" decision. Our developers have carefully considered the "default" settings with the aim of minimizing the need for the user to tinker with those options however, every case is different and our Oracle Schema Compare puts you in charge - you can tweak the behavior of the comparison engine as you see fit. 

So, when using Oracle Schema Compare you see that two object are found to be different by the comparison engine while you believe they should be equal, or they are found to be equal while you believe they should be different don't panic - investigate the differences and carefully review the comparison options - more likely than not one of those options holds the answer to your surprise.

Please note that some of the options listed on the advanced tab may be unknown to you depending on the level of familiarity with Oracle and it would have been helpful to provide a description for each. However, due to space and other restrictions, instead of us providing an incomplete and insufficient description that attempts to summarize information already available in the Oracle documentation we selected to omit those descriptions and let you consult the source directly when necessary.

Download Oracle Schema Compare now and see for yourself why it's hands down the best comparison tool our there. 

Read More

Oracle Schema Compare - Entity Filters

When comparing the schemas of two Oracle databases users of xSQL Software's Oracle Schema Compare tool, generally don't have to worry about any specific settings or options that the tool offers, they just add the databases to the workspace, compare them, generate synchronization script and execute the script on the target. However, in some case your schemas may be complex or very complex containing thousands of object like tables, views, functions, procedures etc. - in those cases, instead of a few seconds the comparison operation may take much longer. Furthermore, often you know that certain objects never change and do not need to be compared so excluding those from the comparison could be very helpful. That's where the entity filters come in. Entity filters allow you to exclude schema objects based on some predefined criteria. You could exclude, for example, tables whose name starts with TEST_, views ending with "_TEMP", procedures by name, function using regular expressions and more.

Oracle Schema Compare allows you to creates filters for specific object types, such as tables, views, procedures. A filter is a set of name-selecting criteria, combined with the OR/AND operator. If you wish to exclude, for example, all tables whose name starts with DEV_ or TEST_, you could create the following filter (notice the OR operator):

   Table filter: name starting with "DEV_" OR name starting with "TEST_"

If you wish to exclude all but two views, whose name are V_EMPLOYEES and V_JOBS, the filter would be as follows (notice the AND operator):

   View filter: name != "V_EMPLOYEES" AND name != V_JOBS

To create an entity filter:

• Launch the Entity Filters from the Ribbon link, the Comparison Form context menu, or via the Entity Filters button in the schema selection form.
• Select the object type, for which the filter is intended, such as Tables or Views.
• Click on the "(new...)" link and create the filter criteria. Add as many criteria as you need
• To change the criteria operator, from OR to AND or vice versa, simply click on one operator.

Download Oracle Schema Compare now - it comes with a 3 week fully functional trial. 

Read More

Oracle Schema Compare - supported objects

xSQL Software's Oracle Schema Compare tool supports the majority of the Oracle object types, such as: tables (relational tables, object tables and xml tables); views (standard views, object views and xml views); functions; procedures; packages; synonyms; sequences; object types; collection types; triggers on tables, views or schema; primary keys on tables or views; unique constraints on tables or views; check constraints; foreign keys on tables or views, indexes on tables or clusters; xml indexes; spatial indexes; domain indexes. 

By default, all object types are included in the comparison and synchronization process, but the user can choose to include or exclude certain object types depending on the use-case. However, the user should be aware that excluding objects of a given type may cause the synchronization script to fail due to missing dependencies. 

Download a free fully functional 3-week trial now and try for yourself. Oracle Schema Compare supports Oracle Schemas from 10g up to 19c. 

Questions, comments and suggestions on how to improve our Oracle Schema Compare are greatly appreciated. 

Read More

Database deployment made easy with xSQL Builder

If you are a software publisher and your software utilizes a SQL Server database on the back-end you have most likely faced the daunting task of pushing schema changes down to your customers. A lot of publishers will ship a "database upgrade" script as part of the software upgrade, but successfully executing that script on the target machine is very challenging for most customers. That is where our Builder for SQL Server comes in - it was designed exactly for that purpose. Here is how it works:

• in a few easy steps you create an executable package that includes a schema snapshot of the "reference" version, that is the version you wish to push down to your customers
• you may also include custom pre and post synchronization scripts for certain customers
• you ship the self-contained executable to the customer
• when the customer executes it all it needs to provide is the target database and the necessary credentials
• the executable will compare the reference schema with the target, generate the sync script and execute the script on the target 
• it will also send a notification back to you reporting whether the upgrade was successful or not and if not you will receive additional info on the cause of failure. 

Download now and see how easy it is. Let us know what you think - your feedback is what guides us as we work to make the products better for you. 

Read More

How to run a trace on SQL Server Express

Using xSQL Software's Profiler for SQL Server you can run traces on any SQL Server edition including MSDE and SQL Server Express starting from SQL Server 2000 and later. Here are some of the highlights:

• No need to install any agents on the target machines
• Trace multiple SQL Server instances at once
• Collect all traces into one central repository
• Advanced scheduling allows you to automate the process of collecting the critical data you need
• Precise event filtering (from simple criteria, such as equal or like operators, to complex regular expressions) to minimize the burden on the participating SQL Servers. Just the data you want, no more, no less.
• Built-in user defined events

The flexibility of the Profiler makes it an ideal tool for discovering SQL Server performance bottlenecks, conducting database auditing and compliance with regulations such as Sarbanes-Oxley, HIPAA, GLBA, etc. 

Download now and try for yourself. xSQL Profiler is free for 1 SQL Server Express instance. 

Read More

SQL Compare SDK

 Our SQL Compare SDK allows any software developer to integrate SQL Server database schema comparison and synchronization functionality into any application with minimal effort. Here are some of the highlights:

• Supports all SQL Server editions from SQL Server 2005 to SQL Server 2019
• Supports latest version of SQL Azure
• It's fast, responsive and has a very small footprint
• In addition of comparing and synchronizing you can script any individual objects and take snapshots of the whole schema
• Intuitive object model designed with the developers in mind
• No external dependencies, very easy to deploy

So, what exactly can you use our SQL Compare SDK for? Two of the most common use cases:

• Software developers/publishers who are periodically having to push database changes down to their customers.
• Database administrators / software developers who are building a customized database version control system.

Check out the comprehensive online documentation and download the SQL Compare SDK today to get started. 

Read More