Monday, June 23, 2014

xSQL Schema Compare - Entity Filters

In cases when the two SQL Server databases being compared have a large number of objects and you know that a good percentage of those objects are the same on both of those databases it may be beneficial to only compare a subset of the objects.

xSQL Schema Compare provides a very flexible and powerful facility called entity filters that allow you to specify which objects you wish to compare and synchronize based on the object type and the name of the object. An example of an entity filter is shown in the screen shot below:
Entity filters can be accessed from the context menu of the comparison results grid. Once a schema filter is set, it's definition is stored in the comparison session so if you run the same compare again you do not need to redefine the filters.

Wednesday, April 9, 2014

xSQL Builder V5 with support for SQL Server 2014 released

We just released a new version of xSQL Builder with full support for SQL Server 2014. In addition of supporting SQL Server 2014, the new version also adds support for schema filters and filegroup mapping. You can download the xSQL Builder v5 from our site: http://www.xsql.com/download/sql_database_deployment_builder/

xSQL Builder allows SQL Server developers and particularly software publishers to automate the deployment of SQL Server databases to their clients eliminating the need for the client to have any advanced computer knowledge other than simply executing a package.

xSQL Builder supports SQL Server 2014, SQL Server 2012, SQL Server 2008/R2, SQL Server 2005.

Thursday, April 3, 2014

Schema Compare new build - schema filters are back

A new build of xSQL Schema Compare for SQL Server is available for download now. Here is what the new build brings:
  • Adds the new entity filters for schema compare UI and schema compare command line. The entity filters can be accessed from the context menu of the comparison results grid. Filter definitions are stored as part of the comparison session so you only need to set them once.  
  • Fixes an issue with extended properties associated with view columns.
  • Fixes an issue with cyclical dependencies
  • Minor user-interface improvements
xSQL Schema Compare supports all editions of SQL Server from SQL Server 2005 to SQL Server 2014 and it is completely free (no strings attached) for SQL Server Express.
 
You can download the new build from our site: http://www.xsql.com/download/sql_server_comparison_bundle/
 

Tuesday, March 18, 2014

SQL Server max memory and number of processors by edition

SQL Server scale limitations, maximum memory and maximum number of processors utilized for each edition and version of SQL Server, from SQL Server 2005 to SQL Server 2014, are presented in this one page, easy to print pdf document:  http://www.xsql.com/content/SQL_Server_Max_Memory_And_Processors_By_Edition.pdf

The information contained in the document has been collected from publicly available sources and is presented as is. If you find any inaccuracies or misrepresentations in the document please contact us.

While you are here, take a few minutes to check out our recently released xSQL Schema Compare and xSQL Data Compare tools - they are the best tools for the job and they are totally free for SQL Server Express, no restrictions, no limitations.

Monday, March 10, 2014

xSQL Compare Bundle V5 with support for SQL Server 2014 released

We just released version 5 of our SQL Server comparison and synchronization tools, xSQL Schema Compare and xSQL Data Compare, with full support for SQL Server 2014. The release notes provide a more detailed description of what's new in this version. Here is a summary:
  • SQL Server 2014 support: full support for SQL Server 2014 including all new and improved SQL Server 2014 objects such as memory-optimized filegroups, memory-optimized tables, hash and range indexes, columnstore indexes, memory-optimized table types, natively-compiled stored procedures, primary and secondary selective xml indexes, Xml index and namespace paths, etc.
  • New comparison options:  in xSQL Schema Compare you can now choose to include/exclude database filegroups and memory-optimized tables from the comparison and synchronization, whereas in xSQL Data Compare you can now choose to include/exclude from the comparison memory-optimized tables and columnstore tables.
  • Filegroup mapping: xSQL Schema Compare version 5 introduces filegroup mappings feature that allows you to easily handle complex database comparison scenarios involving databases with different filegroup structures.
  • Improved workspace: consistent look, feel and functionality in both comparison tools the xSQL Schema Compare and the xSQL Data Compare; new coloring scheme for comparison sessions allowing you to better organize the comparison sessions in the workspace; separate tab for schema snapshots etc.
xSQL Schema Compare V5 supports SQL Server 2014, SQL Server 2012, SQL Server 2008/2008R2, and SQL Server 2005.
 
xSQL Data Compare V5 supports SQL Server 2014, SQL Server 2012, SQL Server 2008/2008R2, SQL Server 2005, and SQL Server 2000.
 
You can download the new version of the comparison tools as one package from: http://www.xsql.com/download/sql_server_comparison_bundle/

Thursday, February 6, 2014

How to script a SQL Server database object using xSQL Schema SDK

Using the xSQL Schema Compare SDK it is very easy to generate the CREATE, DROP and ALTER (when alter is supported) scripts for any database object. The following example shows how to generate the CREATE script for the table Employees from the AdventureWorks database:

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

namespace xSQL.Sdk.SchemaCompare.Examples
{
    class Scripting
    {
        /// <summary>

        /// This method reads the schema of the database AdventureWorks and scripts the table Employee.
        /// </summary>
        public static void Script()
        {
            SqlServer server;
            SqlDatabase database;
            SqlTable table;
            ScriptingOptions options;
            try
            {
                //--create the SQL Server object

                server = new SqlServer(@"(local)");

                //--create the database object

                database = server.GetDatabase("AdventureWorks");

                //--attach an event handler to database.SchemaOperation event in order to get progress information during the schema read

                database.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);

                //--read the database schema

                database.ReadSchema();

                //--create scripting options;

                options = new ScriptingOptions();
                options.CreateScript = true;
                options.DropScript = false;
                options.AlterScript = false;

                //--locate and script the Employee table

                table = database.SqlTables["HumanResources", "Employee"];
                if (table != null)
                    Console.Write(table.GetScript(options));
            }
            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);
        }
    }
}

Tuesday, February 4, 2014

Step by step - compare and sync two sql database schemas in your .NET app using xSQL SDK

Step 1: reference the three namespaces required by xSQL Schema Compare SDK
      using xSQL.Schema.Core;
      using xSQL.Schema.SqlServer;
      using xSQL.SchemaCompare.SqlServer;


Step 2: Create the SqlServer objects for the left and right SQL Server
      SqlServer xServer, yServer;
      // create the left SQL Server object - let's assume you are using Windows authentication

      xServer = new SqlServer(@"(local)\LeftServer");
      // create the right SQL Server - let's assume you are using SQL Server authentication

      yServer = new SqlServer(@"(local)\RightServer", "<user>", "<password>");

Step 3: Create database objects for the two databases being compared
      SqlDatabase xDatabase, yDatabase;
      // create the left database

      xDatabase = xServer.GetDatabase("Source");
      // create the right database

      yDatabase = yServer.GetDatabase("Target");

Step 4: Create the schema comparer object
      SqlSchemaCompare comparer;
      comparer = new SqlSchemaCompare(xDatabase, yDatabase);


Step 5: (optional) Change the comparison options if you need to
      comparer.Options.CompareUsers = false;
      comparer.Options.CompareSchemas = false;
      comparer.Options.CompareDatabaseRoles = false;
      comparer.Options.CompareApplicationRoles = false;


Step 6: (optional/recommended) it is a good practice to attach event handlers to some of the schema events in order to get progress information during the comparison.
      comparer.LeftDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
      comparer.RightDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
      comparer.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);


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


Step 7: Compare the schemas of the two databases
      // step 1: read the schema
      comparer.ReadSchema();

      // step 2: pair the database objects

      comparer.PairObjects();

      // step 3: compare the database schema

      comparer.Compare();

      // check for errors that could have occurred during the schema compare.

      // some errors are handled quietly and do not stop the process; those that are critical throw exceptions
      // quiet errors are collected and stored into the ErrorRepository object
      if (ErrorRepository.Instance.HasErrors())
      {
            Console.WriteLine("Some errors occurred during the database compare");
            Console.Write(ErrorRepository.Instance.GetErrors());
      }

      // check the database status; exit if no schema differences are found.

      if (comparer.SqlDatabasePair.ComparisonStatus == ComparisonStatusEnum.Equal)
            return;


Step 8: Generate the synchronization script for the target database and execute it (let's assume here that the right database is the target - you can substitute left for right if the left database is your target)
      // get the T-SQL script intended for the right database; that is the script that should be executed
      // on Target database to make it the same as the Source database
      sqlScript = comparer.GetRightDatabaseScript();
      if (!sqlScript.IsEmpty())
      {
            // print the synchronization log

            Console.Write(sqlScript.GetLog());
            // print the synchronization script

            Console.Write(sqlScript.GetScript());
            // attach event handlers to ScriptManager object to get some progress info during the script execution

            sqlScript.SchemaScriptExecuting += new EventHandler<SchemaScriptEventArgs>(sqlScript_SchemaScriptExecuting);
            // execute the sync script

            status = sqlScript.Execute();
            // check the execution and print any errors

            if (status == ScriptExecutionStatusEnum.Succeeded)
            {
                  Console.WriteLine("Database synchronization finished successfully");
            }
            else if (status == ScriptExecutionStatusEnum.Canceled)
            {
                  Console.WriteLine("Database synchronization was canceled");
            }
            else
            {
                  // check for errors

                  if (ErrorRepository.Instance.HasErrors())
                  {
                        Console.WriteLine("Some errors occurred during the script execution");
                        Console.Write(ErrorRepository.Instance.GetErrors());
                  }
            }
      }

      private static void sqlScript_SchemaScriptExecuting(object sender, SchemaScriptEventArgs e)
      {
            Console.WriteLine("{0} {1}", DateTime.Now.ToString("HH:mm:ss"), e.Script);
      }


Step 9: Catching exception. All exceptions are listed here for clarity, but you can reduce this section by catching just the top-level Exception
      catch (ConnectionException ex)
      {
            // a connection exception

            Console.Write(ex.ToString());
      }
      catch (SchemaException ex)
      {
            // a schema-read exception

            Console.Write(ex.ToString());
      }
      catch (SchemaCompareException ex)
      {
            // a schema compare exception

            Console.Write(ex.ToString());
      }
      catch (ScriptExecutionException ex)
      {
            // a script execution exception

            Console.Write(ex.ToString());
            Console.WriteLine("Script fragments that failed:");
            foreach (string err in ex.Errors)
                  Console.WriteLine(err);
      }
      catch (Exception ex)
      {
            // a generic exception

            Console.WriteLine("An unexpected error occurred.");
            Console.Write(ex.Message);
      }


You are done - your app can now compare and synchronize the schemas of two databases and it took you 5 minutes! And here it is, all of it in one place to make it easier for you to copy and paste:

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

namespace xSQL.Sdk.SchemaCompare.Examples
{
    class Examples
    {

        /// <summary>

        /// This example demonstrates a typical database comparison scenario.
        /// </summary>
        public static void SimpleCompare()
        {
            SqlServer xServer, yServer;
            SqlDatabase xDatabase, yDatabase;
            SqlSchemaCompare comparer;
            ScriptManager sqlScript;
            ScriptExecutionStatusEnum status;
            try
            {
                // create the left SQL Server object using Windows authentication

                xServer = new SqlServer(@"(local)");

                // create the right SQL Server using SQL Server authentication

                yServer = new SqlServer(@"(local)", "<user>", "<password>");

                // create the left database

                xDatabase = xServer.GetDatabase("Source");

                // create the right database

                yDatabase = yServer.GetDatabase("Target");

                // create the schema comparer

                comparer = new SqlSchemaCompare(xDatabase, yDatabase);

                // exclude some database objects

                comparer.Options.CompareUsers = false;
                comparer.Options.CompareSchemas = false;
                comparer.Options.CompareDatabaseRoles = false;
                comparer.Options.CompareApplicationRoles = false;

                // attach event handlers to these events in order to get some progress information during the schema read and compare

                comparer.LeftDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
                comparer.RightDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);
                comparer.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation);

                // step 1: read the schema

                comparer.ReadSchema();

                // step 2: pair the database objects

                comparer.PairObjects();

                // step 3: compare the database schema

                comparer.Compare();

                // check for errors that could have occurred during the schema compare.

                // some errors are handled quietly and do not stop the process; those that are critical throw exceptions
                // quiet errors are collected and stored into the ErrorRepository object
                if (ErrorRepository.Instance.HasErrors())
                {
                    Console.WriteLine("Some errors occurred during the database compare");
                    Console.Write(ErrorRepository.Instance.GetErrors());
                }

                // check the database status; exit if no schema differences are found.

                if (comparer.SqlDatabasePair.ComparisonStatus == ComparisonStatusEnum.Equal)
                    return;

                // step 4: get the T-SQL script intended for the right database; that is the script that should be executed

                // on Target database to make it the same as the Source database
                sqlScript = comparer.GetRightDatabaseScript();
                if (!sqlScript.IsEmpty())
                {
                    // print the synchronization log

                    Console.Write(sqlScript.GetLog());

                    // print the synchronization script

                    Console.Write(sqlScript.GetScript());

                    // attach event handlers to ScriptManager object to get some progress info during the script execution                     sqlScript.SchemaScriptExecuting += new EventHandler<SchemaScriptEventArgs>(sqlScript_SchemaScriptExecuting);

                    // execute the sync script

                    status = sqlScript.Execute();

                    // check the execution and print any errors

                    if (status == ScriptExecutionStatusEnum.Succeeded)
                    {
                        Console.WriteLine("Database synchronization finished successfully");
                    }
                    else if (status == ScriptExecutionStatusEnum.Canceled)
                    {
                        Console.WriteLine("Database synchronization was canceled");
                    }
                    else
                    {
                        // check for quiet errors

                        if (ErrorRepository.Instance.HasErrors())
                        {
                            Console.WriteLine("Some errors occurred during the script execution");
                            Console.Write(ErrorRepository.Instance.GetErrors());
                        }
                    }
                }
            }
            catch (ConnectionException ex)
            {
                // a connection exception

                Console.Write(ex.ToString());
            }
            catch (SchemaException ex)
            {
                // a schema-read exception

                Console.Write(ex.ToString());
            }
            catch (SchemaCompareException ex)
            {
                // a schema compare exception

                Console.Write(ex.ToString());
            }
            catch (ScriptExecutionException ex)
            {
                // a script execution exception

                Console.Write(ex.ToString());
                Console.WriteLine("Script fragments that failed:");
                foreach (string err in ex.Errors)
                    Console.WriteLine(err);
            }
            catch (Exception ex)
            {
                // a generic exception

                Console.WriteLine("An unexpected error occurred.");
                Console.Write(ex.Message);
            }

        }
        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 sqlScript_SchemaScriptExecuting(object sender, SchemaScriptEventArgs e)
        {
            Console.WriteLine("{0} {1}", DateTime.Now.ToString("HH:mm:ss"), e.Script);
        }
    }
}

//


You can download xSQL Schema Compare SDK from: http://www.xsql.com/download/sdk/sql_server_schema_compare/