Monday, March 1, 2021

One-click database compare - comparison sessions

All fours xSQL Software's database compare tools, Schema Compare for SQL Server, Data Compare for SQL Server, Schema Compare for Oracle, and Data Compare for Oracle, come with an awesome feature called "comparison sessions". So, what are comparison sessions and how do they help you? 

When you first compare two databases, whether it is two Oracle databases or two SQL Server databases, and whether you are comparing the schemas or the data, depending on the complexity of your scenario, the default tool settings may not satisfy your requirements and hence, you may need to spend some time configuring everything right, for example, if you are comparing a development database to a production database comparing and synchronizing permissions may not make sense so you want to exclude those, you may have a set of test tables on development that you do not want in the production, when comparing data you may want to chose a comparison key other than the primary key, etc. This can sometimes be an elaborate effort requiring hours to get everything just right and the last thing you want is to have to repeat that process the next time around. That is where the comparison sessions come in - every choice you make is automatically stored into a session that you can name, and a "tile" will be displayed on the workspace for each session. 

Each session tile shows the session name, the names of the two databases participating in the comparison, the last time that comparison was executed, and the number of times that comparison has been executed. 

You can change the color of the tiles and add a description for each session. Up to 100 comparison sessions can be stored in a workspace. By default the session tiles are ordered based on the "last execution date" but you can change the order in the "Application Settings".

Once a session has been saved you can simply click on the tile to run the comparison - just one click. 

You can download the SQL Server compare tools here and the Oracle compare tools here

Wednesday, February 24, 2021

Free Silver Subscription when you buy Comparison Bundle for Oracle - today only

Do you use both Oracle and SQL Server? If yes, this is the deal for you: purchase a license for the Comparison Bundle for Oracle today (Feb24, 2021) and we will send you a free 1-year Silver Subscription. The Comparison Bundle for Oracle includes Schema Compare for Oracle  and Data Compare for Oracle, whereas the Silver Subscription includes Schema Compare for SQL Server, Data Compare for SQL Server, Script Executor, and RSS Reporter

To take advantage of this offer:

Note that the discount code will subtract $1 from the price you pay for the Comparison Bundle for Oracle and, upon receipt of the order we will send you a complimentary license for the Silver Subscription. 

If you have any questions please email sales@xsql.com 

Note: this deal expires on 2/25/2021 at 00:00 - US Eastern Time. 

Monday, February 22, 2021

Schema Compare for Oracle - Identical but Different Columns

When comparing the schemas of two Oracle Databases using the Schema Compare for Oracle, sometimes you may see "strange" comparison results, that is, you may see that two tables that appear to be identical to each other have been marked as different, or the other way around, two tables that you know have differences may have been marked as equal. We will explain those two scenarios below. 

Marked as different but they seem to be identical! So, you see a table pair marked as different and click on the pair to review the schema differences. On the "Schema Differences" tab on the bottom you may notices that one or more columns have been highlighted as being different but there is no visible difference on the script! In these cases you will need to dig deeper to find out why those columns are marked as different. To do that expand the table on the top section and then click on the column you are interested on - on the bottom section of the screen you will see the script for that particular column with the different section highlighted. This difference will be visible on the table script as well however it is a lot easier to "spot" on the column script. Differences on default constraints, check constraints, default bindings, rule bindings, or extended properties can cause the columns to be flagged as different.

Marked as equal but they are different! The highlighting of the differences on the script of an object is controlled by the comparison engine so if the comparison engine marks two objects as equal then nothing will be highlighted on the scripts of those objects even though there might be visible differences on the script. Why does this happen? The answer lies in the comparison options - there are a few options related to collation, nullability, replication, identity etc. that affect the column comparison. So if you have, for example, chosen to ignore the column collation then even though the column on the left might have a different collation from that on the right the columns will be marked as equal and therefore the collation difference on the script will not be highlighted.

Download Schema Compare for Oracle now and try it for yourself. 

Wednesday, February 17, 2021

Data Compare for Oracle - Where Clause

Comparing data between two databases sounds simple and conceptually is very simple - basically you compare a data element from database 1 with the corresponding data element from table 2. However, the goal is to compare ALL data in database 1 with all data in database 2 and that is where things get complicated very quickly. Let's take a simple scenario, assume we just need to compare DB1.Table1 to DB2.Table1. Let's further assume that DB1.Table1 contains ~10 million rows and DB2.Table1 contains approximately the same amount of rows. In order to compare the data in those two tables, Data Compare for Oracle needs to transfer ~20 million rows (~10 million from each side) from the servers where those databases are hosted into the machine that is performing the data compare. As you can imagine this is an expensive operation that takes significant amount of time. 

Now, in most common scenarios you know that over 90% of those rows are going to be the same and you don't really need to compare them, so if you could avoid transferring all those rows and comparing them, you would save significant amount of time and resources. To accomplish that, Data Compare for Oracle provides a "where clause" option that allows you to compare only the rows you want. When defining the where clause, by default, the same filter is applied to both tables. If you wish to apply a different filters for each of the tables, you can un-check "Use the same where clause" and define a separate clause for each side. 

Important notes to keep in mind:
  1. Do not include the "where" keyword in the where clause. Simply enter the SQL condition, in the same way you would write it in any Oracle tools.
  2. Validate the where clause before comparing the data. This ensures that the clause is syntactically correct.
  3. We strongly recommend basing the where clause on the columns participating in the comparison key. You may include additional columns, but all data key columns must be included in the where clause. Not having a data key column could produce false missing records during the comparison.
Download the Data Compare for Oracle and try now. 

Tuesday, February 16, 2021

Data Compare for Oracle - Comparison Keys

The Data Compare for Oracle is designed to compare the data in "matching" rows in two tables that may be in two different databases or even the same database. What does "matching" rows mean? Matching rows means rows from both tables, that can be uniquely paired to each other based on the value of a certain column or a combination of columns. 

The most common scenario is that in which you would pair the rows from both sides based on the value of the Primary Key. This works great when you are comparing tables from identical or similar schemas / databases, but that is not always the case. Regardless, after pairing the tables (either automatically or manually), the Data Compare for Oracle analyses the tables and tries to identify an identical unique key that exist on both tables. The unique key can be the Primary Key, a Unique Constraint or a Unique Index. If it finds one, that is the key that will be used to pair the rows to each other, and if it does not find one it marks the table pair as un-comparable. Whether a useful comparison key was automatically identified or not you can still drill down on that table pair and either pick a different comparison key from the list of identified unique keys, or create a custom comparison key


When you drill down on a table pair you will see a window with 3 tabs one of which is called "Unique Keys". The "Unique Keys" tab is divided in two main sections - the top section contains two boxes with the list of unique keys that are potential candidates to be used as the comparison key for each table in the pair, whereas, the bottom section contains a box with the pair of unique keys that have been selected as the key to be used for the data comparison operation.

If the key that has been selected is what you want then you may proceed. However, you can also pick one of the other keys or,  create a custom key based on one or more columns of the table. Note that, a user-defined key or a custom key exists only inside data compare. No index or constraint is created on the Oracle table.

Data Compare picks the comparison key in the following order:
  1. User-Defined Key
  2. Primary Key
  3. Unique Constraint (the 1st in alpha order)
  4. Unique Index (the 1st in alpha order)
A user-defined key or a custom key always take precedence over the primary key, unique constraints or unique indexes that might exist on a table. When custom keys are selected, you should validate them by clicking on the Validate button. Custom keys are validated as following:
  • Columns in the custom keys must have the same name.
  • Columns in the custom keys must have the same data type. Length, scale, precision, nullability and other column properties are not considered.
  • The uniqueness of the key is checked only if the option “Check custom key uniqueness”, in the Application Settings form, is on.
To select a key for data comparison, simply double-click on it.

Download Data Compare for Oracle and try now. 

Thursday, February 11, 2021

Data Compare for Oracle - Mapping Rules

In most common data compare scenarios, the default mapping mechanism employed by Data Compare for Oracle, pairs the tables and views based on the name. However, there are often scenarios in which tables and views in both databases being compared may have slightly different names. Of course, as explained here, you can manually pair any two tables or views regardless of their names but that requires a lot of effort and makes sense when the names are completely different. In cases when, let's say, you may have table names in database1 prefixed with prefix1_ whereas the equivalent tables on database2 prefixed with prefix2_, you can utilize the mapping rules to make your job easier.  Mapping rules allow you to automate the pairing of those tables by creating simple rules. 


The Mapping Rules can be accessed from the ribbon or from the action links on the right panel of the comparison tab. There are two basic rules you can set, "Ignore prefix" and "Ignore postfix". For example if you wish to map prefix1_employees from database1 to employees table from database2 then you just enter "prefix1_"  in the "Source table prefix" textbox - that will cause the "prefix1_" part of the table name to be ignored in database1 and map the tables only based on the part of the name that comes after that part. Similarly, you can ignore a prefix on the other database, so you could easily have prefix1_employees table mapped to prefix2_employees table. You can do the same thing with "postfixes", so you could map employee_postfix1 to employee_postfix2. This is a small feature but comes in very handy in certain scenarios.  

Download Data Compare for Oracle and try today - it comes with a 3 week free trial. 


Tuesday, February 9, 2021

Data Compare for Oracle - Custom Table Mapping

The default behavior of xSQL Software's Data Compare for Oracle has been carefully designed to minimize the need for user intervention in most common cases. Generally, the most common data compare scenario involves two databases with identical or at least similar schemas (same tables and views), therefore, when you click on "compare databases" (in the case of Oracle the button is actually "Compare Schemas"), after you select the two databases/schemas to be compared, the tool will automatically map (pair) the tables and views based on the name. 

However, there are cases when you may need to compare data between two databases that do not share the same schema. That is, you want to compare the data in two tables with different names, and those tables may be in the same schema/database or in different schemas/databases. In this case, instead of clicking on "Compare Schemas" you would click on "Compare Tables/Views" - that invokes the manual mapping / pairing functionality that allows you to pair any two tables regardless of their name. 

The mapping process is very simple: 

  • click on "Compare Tables/Views" button on the "Home" tab of the ribbon. 
  • On the "Object Compare" dialog window that appears first select the Oracle connections and schemas on which the objects reside (you can choose the same database / schema on both sides if necessary)
  • When there is a large number of objects you may wish to specify a pattern for the name of the objects you are looking for (ex. all tables that start with a) - that will make it easier for you to find the tables/views you wish to compare. 
  • Once you have made your selections, click on the "Read Database Objects" link. Data Compare for Oracle will read objects on each schema and populate the two left bottom windows with the list of tables / views available.
  • Now you can map those tables/views one at a time irrespective of the names. Select an object on the left, then select an object on the right and click the left to right "arrow" button to map those two objects.

NOTE: In order to compare the Oracle views you will need to check the data comparison option "Compare and Synchronize Views". Keep in mind that Oracle imposes limitations on views and not all views are updatable. Also be aware of the fact that updating views means updating the underlying tables so use this option with caution.

Download Data Compare for Oracle and try today. 


Monday, February 8, 2021

Schema Compare for Oracle - a great version control tool

The name Schema Compare for Oracle clearly conveys the essence of the tool, the core functionality that this tool provides is that of comparing two Oracle database schemas. However, the fact that this same tool can provide one of the most effective ways to implementing database version control and change tracking capability is not obvious. So, how does the Schema Compare for Oracle make the leap from a schema compare tool to a database version control tool?  

First, let's clarify the phrase "database version control", what is it? Much like the code version control that every developer is familiar with, the goal of the database version control mechanism is to ensure that there is a reliable, unquestionable, source of the history of database schema changes that allows one to both identify any schema changes that may have happened between two versions and allow you to easily go back and forth between versions.  

Proceed with caution! In the case of databases, migrating the database schema from one version to another, whether you are moving to a new version of the database or reverting to an older version may cause loss of data because columns or whole tables that contain data in the current version but may not exist at all (and hence will be dropped) in the target version. 

Schema Compare for Oracle includes a cool feature: it allows you to take schema snapshots, small footprint, read-only files that contain 100% of the schema information. What does that mean? The schema snapshot is a frozen-in-time "picture" of your database (no data, just the schema). Furthermore, the included command line utility allows you to automate the process of taking the schema snapshots. 


How can you utilize the schema snapshots for database version control and change tracking? Schema Compare for Oracle allows you to compare any two schema snapshots, or any snapshot to a live database, and generate a target compliant synchronization script. Consider the following scenarios:

Scenario 1: you have taken nightly schema snapshots of your database and stored those into a repository. Now you are asked to identify the schema changes that happened between date1 and date2. All you would need to do is pick the right snapshot files from the repository based on those dates and compare them to see the differences. 

Scenario 2: you have stored the snapshots as in scenario 1. The request is to revert the database schema to where it was 2 days ago. In this case you take the snapshot from two days ago and compare it to your live database. Then generate the sync script to make the live database the same as the snapshot. You review the change script, especially the warnings section at the top to see if any data loss may occur in which case you would need take the appropriate steps to preserve that data. Then you execute the sync script and voila, your database schema has been reverted to the 2-days ago version. 

Scenario 3: The request this time is to propagate the latest schema changes to the production database. As in scenario 2 above, you pick the right snapshot from the repository (that is the latest stable - ready to ne published version) and compare that to your production database. Generate the sync script to make the production database the same as the snapshot in question. Take the same precautions described above to ensure no data is lost, and then execute the script. 

Download the Schema Compare for Oracle now. 

Friday, February 5, 2021

How does xSQL Profiler work

xSQL Profiler uses the same underlying technology as Microsoft SQL Server Profiler and adds additional capabilities such as:

  • ability to execute the same trace on multiple SQL Server instances simultaneously.
  • powerful filtering allowing the user to define precise traces that capture exactly what the user wants, nothing more and nothing less.
  • advanced scheduling 
Furthermore, the installation of xSQL Profiler is very simple and does not require any agent on the servers that you want to monitor. You just install xSQL Profiler on your monitoring machine and you can monitor any server to which you can connect to using either SQL Server authentication or Windows authentication.

Trace Structure

The diagram below illustrates how a trace defined in xSQL Profiler is structured. At the lowest level we have the SQL Server events that are defined by SQL Server such as “RPC:Starting” or “Lock:Cancel”. On top of these low level events xSQL Profiler defines its own events called “xSQL Profiler Events”. xSQL Profiler includes some common events, but the full power of it relies on the ability to allow the user to fine tune them or define completely new events. 


xSQL Profiler events can be defined by:
  • Selecting the underlying SQL Server events that you want to monitor.
  • Selecting the columns that you're interested in.
  • Defining filters to get only the results you need. 
For example to define a xSQL Profiler event to monitor DELETE statements, you can select the “RPC:Starting” and “SP:Starting” events and apply a filter on the TextData column similar to “TextData LIKE ‘%DELETE%FROM%’”.

When you want to actually monitor some of your servers, you define a trace. The elements of a trace include:
  • xSQL Profiler events that you will include in the trace.
  • Servers and/or databases you want to monitor.
  • Additional filters on the selected events. For example, if you're interested only in delete statements on the "USERS" tables you will include a filter on the TextData column to get only those results.
You can start and stop the trace manually or you can schedule it to automatically start and stop at specified interval

When the trace is started the necessary SQL traces will be created on each selected server and the data will be automatically loaded in the xSQL Profiler central storage.

Trace Data

As explained above, xSQL Profiler uses the standard SQL Server trace technology to gather the trace result. The diagram below illustrates how this works.

When the trace is started (by the user or by the scheduler) the necessary traces will be created on all servers included in the trace. In this case “Trace 1” will be created on both “Server 1” and “Server 2”. When the trace is running the results are logged on temporary files on each server. At regular intervals (or when the user selects the “Load Data” from the menu) the data is imported from the temporary data files to the Temp database on the remote servers. In this case the corresponding data will be loaded on the Temp database of “Server 1” and the Temp database of the “Server 2”. From the Temp database on each remote server the data are automatically transferred to the “xSQL Profiler Central Storage”. 
During this process the regular expression filters are applied and only the necessary data are moved to the central storage database.

xSQL Profiler supports all editions of SQL Server including MSDE and SQL Server Express. Download the Profiler now and try for yourself. 

Thursday, February 4, 2021

Customizing database deployment package - xSQL Builder

xSQL Software's Builder for SQL Server is designed to automate the database deployment to clients that cannot access the "master" database. The Builder accomplishes that goal by embedding a database schema snapshot in the deployment package and then comparing that snapshot to the target when the deployment package is executed on the client. The process is easy and straight forward, but, every scenario has its own particulars and despite the customizations that the Builder interface allows, it cannot address all of those scenarios. 

Therefore, together with the self-contained database deployment package, the Builder for SQL Server also provides the source code for that package. xSQL Builder uses a set of template files to generate the executable package. The template files are written in C# and contain the code that compares and synchronizes the client database with the imbedded master database (note that only the schema of the master database is imbedded in the deployment package, not the whole database). When you download the xSQL Builder the download package also includes a VS.NET 2015 C# project that you can run or modify as needed.

When you generate an executable package, the xSQL Builder package configuration wizard performs the following actions:

  • Copies the configuration files and the master database snapshot to the \Resources subfolder under the main folder of the code template.
  • Compiles all the template files with extension .cs and generates the executable package. Only the .cs files in the main folder are compiled. Files that are located in subfolders are ignored.

You can choose to run the code template from the VS.NET and get the same result as you would get by running the executable. 

To run the code template project in VS.NET do the followings:
  • Add references to the builder runtime dll. You can find these dll in the xSQL builder installation folder usually under \Program Files\xSQL Software\xSQL Builder
  • Add the files config.xml and the master database snapshot file in the VS.NET project. Both files are located in the \Resources sub-folder. In the VS.NET property window, set the "Build Action" property to "Embedded Resource" for both files.
The xSQL Builder template project requires the following dlls:
  • xSQL.Builder.Core.dll
  • xSQL.Utils.dll
  • xSQL.Schema.Core.dll
  • xSQL.Schema.SqlServer.dll
  • xSQL.SchemaCompare.SqlServer.dll
  • xSQL.Licensing.v4.dll
  • c1.c1zip.dll
All the dll-s are located in the Builder installation folder, usually under \Program Files (x86)\xSQL Software\Builder v<n>\ where n is the version number.

Download xSQL Builder now and try. 

Wednesday, February 3, 2021

Scripting a database object in .NET using Schema Compare SDK

In addition of allowing you to take schema snapshots, compare, and synchronize the schemas of two SQL Server databases, Schema Compare SDK for SQL Server also allows you to easily generate the T-SQL script for any object. The script can be of the type CREATE, DROP, ALTER (if ALTER is supported for the given object type) or a combination of those. 

Here's how easy it is to generate the script in your code:

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);
        }

    }
}

Download Schema Compare SDK now and try this. 

SQL Server SUBSTRING

The Substring function, one of the most used string manipulation functions in all languages, is also available in SQL Server. Just like any function, the SQL Server Substring function takes some arguments in a specific order and returns a value. 

As you can easily guess from the name, the SQL Server Substring function returns a specific part of a given string, and that means it takes a string as an argument and returns part of that string. The question is which part of the original string will it return? That is specified by the other two arguments, the start position (start) of the sub-string to be returned and the number of characters to return (length).

So the function looks like this: SUBSTRING ( OriginalString, start, length )

For example, if we have a string like 'My preferred city in US is Boston' and we wish to extract the name of the city from that string we can write 

SUBSTRING (‘My preferred city in US is Boston’, 28, 6) 

and we would get back the substring ‘Boston’.

As you can imagine the name of the city in the above string can be anything and you do not necessarily know the length, so how can you make sure the substring function will extract the full name of the city from the original string and nothing else? 

Let us assume you know the city name starts at position 28 and it is the last word on the original string, so basically you just want to make sure you return everything from position 28 to the end. You also know that the city name cannot be longer than say 100 characters so you could do:

SUBSTRING ( OriginalString, 28, 100 )

What will this return when the OriginalString is 'My preferred city in US is New York'? Will this return an error since the 'length' we have specified goes outside the boundaries of the OriginalString? No, it will not return an error. If the “start + length” exceeds the length of the OriginalString than the SUBSTRING function will return everything from the start position to the end of the OriginalString. 

However, even though this would work, is not satisfying. It just does not feel right to pass an argument that we know it will be out of range. So, how do we fix that? How about this:

SUBSTRING ( OriginalString, 28, LEN ( OriginalString ) - 28 + 1 )

Let’s review this: as you can see we are using LEN ( OriginalString ) - 28 + 1 to specify the length argument of the Substring function. What does that expression do? It calculates the exact length of the city name (assuming the city name is everything from position 28 to the end of the OriginalString). LEN is a T-SQL function that will return the length of the argument we pass to it, in this case the length of the OriginalString. So if the OriginalString is 'My preferred city in US is New York' then LEN(OriginalString) will return the value 35. Knowing that the city name starts in position 28 we now know that the name of the city in this case would be exactly (35 - 28 + 1) = 8 characters long. So, the SUBSTRING expression above would be the equivalent of writing SUBSTRING ( OriginalString, 28, 8), but we have parametrized it so that it works for any city name regardless of its length. 

Now, let us make this slightly more interesting. Assume your original string contains the city name but it is somewhere in the middle of the OriginalString and you need to extract it. You review the data and notice that the city name is always preceded by the string 'city of ' and is always followed by ' has'. How can we pull out the city name? How about this:

SELECT SUBSTRING ( @OriginalString, CHARINDEX ( 'city of', @OriginalString ) + 8, CHARINDEX ( ' has', @OriginalString, CHARINDEX ('city of', @OriginalString ) ) - ( CHARINDEX ( 'city of', @OriginalString ) + 8 ) )

This may seem convoluted, but it is very simple:

  • The CHARINDEX ( 'city of', @OriginalString ) will return the starting position of the string 'city of'' and since we can count that ‘city of’ contains 7 characters and is followed by a space before the city name appears then we know that the starting position for the city name is CHARINDEX ( 'city of', @OriginalString ) + 8
  • The other expression is calculating the length of the city name. Now, we know that the city name is followed by ‘ has’ however, if we use CHARINDEX ( ' has', @OriginalString) that will return the starting position of the very first ‘ has’ in the original string (there may be more than one ‘ has’ in the original string)  and that is now what we want. We want the starting position of the ‘ has’ immediately after the city name, hence, we say find the starting position of ' has' but don’t look at the whole OriginalString, start looking after the 'city of'
Try this:

DECLARE @OriginalString varchar(1024)
SET @OriginalString = 'The crime rate in the city of New York has increased significantly'

SELECT SUBSTRING ( @OriginalString, CHARINDEX('city of', @OriginalString) + 8, CHARINDEX(' has', @OriginalString, CHARINDEX('city of', @OriginalString)) - (CHARINDEX('city of', @OriginalString) + 8))





Tuesday, February 2, 2021

How does xSQL Builder work?

The main idea behind xSQL Builder is the automation of the deployment of database changes to multiple clients. How does the Builder it accomplish that goal? In one sentence: it generates a self-contained executable that propagates the schema of a master database to the clients on which it's executed. As the above sentence implies, there are two big parts to this:

1. Generating the self-contained executable package. The following diagram illustrates the actions that the Builder performs when generating the ready-to-deploy executable package. All the actions shown on the diagram are performed as the user goes step by step through the wizard. Most actions are self-explanatory but the "update .NET template project" may not be obvious. Together with the executable package, the Builder also generates a .NET project that contains the source code that will run on the client machine. The purpose of the project is to allow you to modify the code if necessary and then generate the executable - this allows you to customize the deployment package beyond the standard customization provided by the wizard. 



2. Executing the package. The following diagram illustrates the actions performed on the client. 


Download xSQL Builder now and see for yourself how it makes deploying database changes to clients easy, safe and efficient. 

What is xSQL's Builder for SQL Server?

The easiest way to explain what xSQL Software's Builder for SQL Server would be to list some of the key challenges that anyone who has ever shipped database driven software to clients is familiar with.

  • Most likely access to the client databases that need to be upgraded is not available - the client has to perform the upgrade on his own;
  • The "master" or "reference" database that you want to push out to the clients may not be reachable from the clients' machines;
  • Your clients don't necessarily upgrade in tandem - some clients may be three versions behind some two versions behind etc. so you cannot push out a standard upgrade script for all;
  • You may have customized your solution for some clients and a standard upgrade path may result in those customizations being wiped out. Therefore, for certain clients you may need to run custom T-SQL scripts before and/or after synchronizing the client's database to the master database;
  • Generating a ready-to-deploy executable package for all your clients, instead of sql scripts that would be alien to many of your clients, is highly desirable;
  • Generate a package for your .NET deployment solution or other setup and deployment utilities so that the database upgrade is presented as an integral part of the software upgrade is also desirable;
  • Every situation is different so you need to be able to customize the deployment package before generating it;
  • Receiving a notifications via email when the database deployment package fails to complete all the tasks would be very helpful to you;
  • Logging all operations that are performed on the client machine during the package deployment is not only the prudent thing to do but it's a necessity.

Builder for SQL Server was designed specifically with those challenges in mind, providing a wizard based interface that allows you to create, fully customizable, self contained, ready-to-deploy executable packages. 


Download the Builder for SQL Server now. 

Monday, February 1, 2021

SQL Server Data Compare - comparing large databases

Comparing the data from two SQL Server databases is kind of like comparing two text files to each other, so it should be easy, right? No, it is not easy. The focus of this article is just one of the challenges - the size of the databases being compared. Here is what a data compare tool, like xSQL Software's Data Compare for SQL Server, should be able to do: 

  • In order to compare the data it first needs to pair the tables and views and determine what comparison key to use for each table/view in order to pair rows from both sides with each other
  • Next, read the data from both sides (imagine millions of rows on each table), compare the data and keep track of the differences
  • Display the data differences in an easy to navigate manner- the user wants to see what's different between Server1.Table1 and Server2.Table2. Again, remember we are talking potentially millions of rows for each table pair so just slapping a data-grid on a window is not going to cut it. 
  • Generate a synchronization script which can get to be very large if there are a lot of differences between the databases. 
  • Execute the synchronization script on the target database. Once again, if the script is very large, executing it on the target is not a trivial matter. 
Except for the pairing of the tables step which is not affected by how much data you have to compare, all the other items in the above list are challenging to handle since your resources are limited. Many data compare tools out there will quickly demonstrate this challenge - as soon as you try to compare two relatively large databases they will crash or just keep "spinning the wheel" forever. We have invested considerable amount of effort into designing and building a data compare tool that efficiently handles any size database, restricted only by the disc space available. Our data compare tool seamlessly serializes data into disc, loading the data into the main memory only if and when necessary, and executing the synchronization script against the target database in manageable chunks the size of which is configurable by the user. 

Furthermore, to ensure an efficient comparison, our Data Compare for SQL Server, allows you to both exclude certain columns from the comparison and use row filters to compare only rows that you want to compare (more on those later) thus significantly reducing the workload. 

Download our Data Compare for SQL Server today. 

Sunday, January 31, 2021

File systems versus database management systems (DBMS)

Every DBMS book and beginner course out there discusses this subject, but we still see a considerable number of visitors ending up on our site when searching for “difference between a flat file and a database”, “difference between an excel spreadsheet and a database” etc., so we decided to provide a brief explanation here.  

First of all it is very important to clearly distinguish between a database and a database management system as they are nowadays often used interchangeably (albeit inaccurately). A database in the general sense is any structured collection of data – you have a file where you keep business cards you have collected, that is a database of contacts; you have an excel sheet where you record your ebay sales, that is a database of sales; you have a text file where you have stored a list of prospects, that is database. In a more narrow sense that the term database has evolved into, it really means a structured collection of data that has been stored in a computer system in a particular format through one of the database management systems like Oracle, DB2, SQL Server, MySQL, Access etc. A database management system is a software application that facilitates the process of creating and managing electronic databases. So remember, SQL Server, SQL Azure, Oracle, DB2 etc. are not databases but rather database management systems (dbms)

So, what do we need a database management system for? Isn’t what the file system that the Operating System provides sufficient for our data storage and administration needs? Consider the following oversimplified requirements – your team has been tasked with building a computer system that will collect and administer data on the employees of the company, products and services the company sells, vendors that supply the company with products and services, customers, orders and so on. You quickly identify the following high level requirements:

  • the amount of the data that needs to be stored will reach in the hundreds of gigabytes;
  • thousands of employees will need to access the information concurrently;
  • the system must answer any queries submitted by the users quickly and accurately;
  • data changes made by different users must be applied consistently;
  • access to the data (ability to read, update or add) must be selective – certain users have certain rights on certain parts of the data. 

If as a programmer / software engineer all you have at your disposal is the operating system and a programming language you soon start to realize the immensity of the challenge you have in front of you:

  • with hundreds of gigabytes of data that will be stored on let’s say an array of hard drives you need to build a system that manages the transfer of data from those hard drives to main memory and vice versa as needed. You realize that how you organize the data into files is going to make or break the system. 
  • thousands of users sending thousands of queries to your system means you need to foresee as best as possible the types of queries the system will receive and write code that will handle each of those queries;
  • What if there is a crash in the middle of the day when hundreds of users were adding and updating data? What happens to your data? When a user enters a sales order for example your application stores the order information and amongst the other things sends an order to the shipping department and updates inventory levels but the system crashed and your code did not complete executing! The inventory numbers were not updated… Your system must be able to handle such situations and ensures that your data does not turn into “garbage” no matter what happens – easy to say but very hard to do.  
  • What happens if two or more users are trying to update the same data at the same time? Your application needs to handle this and other concurrency related issues and this on itself is no small task.
  • How about the selective access to the data? Simply not giving the user a way through the interface to access certain data does not provide sufficient security. Separating the data into files based on security needs and using the operating system security mechanisms sounds good but you will soon discover that such separation is not realistic as it would make every other task your system needs to handle much more difficult. 

Can your team handle all those challenges? Of course it can but at what cost? How long will it take your team to build a stable system that can handle those challenging requirements? 

That is where a database management system (DBMS) comes into play. Companies like Oracle, IBM, Microsoft and others have made billions upon billions of dollars selling their DBMS systems (respectively Oracle, DB2, SQL Server) that at the core are software applications designed to handle all those challenges mentioned above and a lot more.  

A DBMS provides an awesome abstraction layer that saves you a tremendous amount of trouble and effort.

Synchronize database schemas - SQL Compare SDK

Comparing and synchronizing the schemas of two SQL Server databases is very easy with xSQL Software's SQL Schema Compare SDK. As you can see in the code snippet in the image below, there are only 5 simple steps involved in making the schema of database A the same as the schema of database B: 

  1. Read the schemas of both databases
  2. Pair objects based on the object name
  3. Compare the paired objects to each other
  4. Generate the synchronization script
  5. Execute the script on the target
Of course, as we have mentioned in other articles, SQL Compare SDK gives you complete control over the database compare operation allowing you to:
  1. exclude or include certain types of objects - for example, if you do not want to compare database users you can do comparer.Options.CompareUsers = false
  2. create entity filters to exclude for example only stored procedures the name of which starts with sp_PS
  3. exclude / include specific objects, for example don't compare table "employees"
  4. set comparison options and scripting options based on your specific requirements 
  5. set execution options such as transaction size, whether to continue execution on error etc. 

Download the SQL Schema Compare SDK and check out the extensive documentation - you will be able to build your first database comparison and synchronization application in no time. 

Saturday, January 30, 2021

Database Documenter - what is it good for?

When they hear the words database documentation or Database Documenter many software developers and database administrators think "boring", "annoying", "useless", and "outdated before generated". 

What if you could get a tool that makes documenting the database and keeping the documentation up to date, quick and easy, allowing you to completely automate the process? What if the documentation that that tool generates is so useful that you would keep it pinned on your desktop and use it as a reference every time you need to do something with the database? 

Yes, xSQL Software's Database Documenter is exactly that tool which allows you to do all the above with ease, generating extremely useful HTML and/or CHM documentation that makes it very easy and convenient to understand the database structure, to see the object dependency graphs, to quickly find exactly what you may be looking for. Here's a screen shot showing the Employees table: 


Our Database Documenter supports SQL Server, SQL Azure, Oracle, DB2, MySQL, Sybase ASE, Informix IDS, SQLite, Teradata, VistaDB, ENEA Polyhedra, Raima RDM. Download and try the Documenter today. 

Friday, January 29, 2021

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. 

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. 



Thursday, January 28, 2021

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. 


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. 

Wednesday, January 27, 2021

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. 


Tuesday, January 26, 2021

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. 

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. 

Monday, January 25, 2021

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. 

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. 

Saturday, January 23, 2021

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. 

Friday, January 22, 2021

SQL Comparison Bundle review from 13 years ago

13 years ago, in 2007, when we were on version 2 of our SQL Comparison Bundle, the reviewer wrote: "It's frankly hard to write reviews that are for products that just flat out work right." - read the full review here: sswug-review.pdf (xsql.com). 9 versions and 13 years later our SQL Schema Compare (previously known as xSQL Object) and our SQL Data Compare continue to work right and are loved by thousands of users on every corner of the globe. 

You can download the current version of our SQL Comparison Bundle here. We made it free for SQL Server Express in 2007 and we continue to distribute it for free now. 

SQL Comparison Bundle - simply the best comparison tools

SQL Comparison Bundle includes both the SQL Server Schema Compare tool and the SQL Server Data Compare tool that are two of the best SQL Compare tools you can find regardless of what criteria you may be using for evaluation. Here's what really matters and demonstrates the superiority of the SQL Comparison Bundle:
  • Performance: what we like to say is "dare to compare" - both tools included in the SQL Comparison Bundle, the Schema Compare and Data Compare have been crafted with extreme care to deliver the best possible performance in most scenarios. Resource consumption is carefully managed and every optimization opportunity is utilized. 
  • Reliability: the tools included in the SQL Comparison Bundle have been in production for over 15 years and thousands of users rely on them - they simply work. 
  • Interface: whether you are using the GUI or the included command line utilities the tools included in the SQL Comparison Bundle, in the words of our users, are simply a joy and deserve one of the biggest compliments a user can bestow on a software "user manuals are not needed". 
  • Cost: how does FREE sound? Both tools included in the SQL Comparison Bundle are completely free for SQL Server Express, no restrictions, no limitations, just our humble contribution to the SQL Server community. 

Thursday, January 21, 2021

SQL version control - schema snapshots

Database version control is one of the most critical tasks in ensuring the safety and reliability of the software development process and the operation of data-driven applications. Every code deployment coincides with a particular state of the database supporting it - the code and the database must be in sync. If you roll your application back to a point in time you may also have to roll back the database schema changes or your application may become unstable or non-functional. 

Many database administrators and developers commit the database change script or the whole database create script in the repository together with the application code, and that serves the purpose, but it's far from being efficient or convenient. Our SQL Schema Compare tool offers a better way, schema snapshots

A schema snapshot in this context is a small encrypted file that contains 100% of the database schema information in it. You can manually take schema snapshots of a given database any time you decide to through the SQL Schema Compare GUI or, you can use the included schema compare Command Line utility to automate the process and take snapshots on a schedule. Furthermore you can automate the decision on whether you need to store the snapshot in the repository or not by comparing the current snapshot with the previous one: if there are no changes you can discard the new snapshot otherwise you commit it to repository. 

Utilizing schema snapshots you can then do any of the following:

  • Create a copy of the database (schema only). To do that, you create a blank database, then compare the desired source snapshot with the blank database and generate the "sync" script which will make the blank database exactly the same as the database from which the snapshot was taken. 
  • Compare any two snapshots to each other to generate the changes that have occurred in the time period between the two snapshots. 
  • Compare a snapshot directly to a live database and generate synchronization script to revert the database back to the state it was when the snapshot was taken. 


Wednesday, January 20, 2021

Free SQL Server tools

Our product offering includes 6 awesome tools that are free, you don't have to pay a dime for:
  • SQL Schema Compare - allows you to compare and synchronize the schemas of any 2 SQL Server Express databases, no expiration, no limitation and no external dependencies other than .NET framework. Supports from SQL Server 2005 to SQL Server 2019. One of the best schema compare tools you will find in the market. Cost: FREE
  • SQL Data Compare - allows you to compare and synchronize the data between any 2 SQL Server Express databases, no expiration, no limitation and no external dependencies. Cost: FREE
  • Documenter - document any database for free. The only catch is that the documentation will include a "this is an unlicensed version" text throughout the documentation - a bit of inconvenience but does not limit the functionality.
  • Script Executor  - build script execution packages with constraints and conditional branching and run them interactively, via the command line, or deploy them as executable packages. FREE for personal use only. 
  • SQL Search - you can run it as a stand-alone application or you can add it as an add-in to SQL Server Management Studio. No restrictions, no expiration. Cost: FREE 
  • xSQL Profiler - Tracing, monitoring and event collection of multiple SQL Server instances from one location FREE for one SQL Server Express instance.

SQL Server versions

Here's the list of SQL Server versions from SQL Server 2000 to SQL Server 2019:
  • SQL Server 2019 -> v 15.0.x (October 2020 CU v 15.0.4073.23)
  • SQL Server 2017 -> v 14.0.x (September 2020 CU v 14.0.3356.20)
  • SQL Server 2016 -> v 13.0.x
  • SQL Server 2014 -> v 12.0.x
  • SQL Server 2012 -> v 11.0.x
  • SQL Server 2008 R2 -> v 10.50.x
  • SQL Server 2008 -> v 10.0.x
  • SQL Server 2005 -> v 9.0.x
  • SQL Server 2000 -> v 8.0.x
If you want to see what version you are using just run SELECT @@VERSION;

DATETIME vs DATETIME2 - 5 reasons why DATETIME2 is better

Recommendation first: if you are not sure whether to choose Datetime or Datetime2 the answer is choose Datetime2. Why?
  1. Higher precision – up to 7 fractional digits for datetime2 vs 3 for datetime 
  2. Higher level of accuracy – datetime rounds the last digit to an increment of .000, .003 or .007 whereas datetime2 supports accuracy of 100 nanoseconds 
  3. Wider range of values – datetime2 supports dated from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999 whereas datetime supports dates from 1753-01-01 00:00:00 to 9999-12-31 23:59:59.997 
  4. Space requirements – depending on the fractional precision you require datetime2 needs from 6 Bytes to 8 bytes of space whereas datetime requires 8 bytes 
  5. Compliance with standards: datetime2 is compliant with both ANSI and ISO 8601 whereas datetime is not.
For a more in depth review of those reasons you can check our other post here.

SQL Server Editions

SQL Server comes in a variety of editions each of which targets different audiences based on performance, runtime and price requirements. Here is a quick summary of the editions currently available for SQL Server 2019:

Express: SQL Server Express edition is free and it's one of the best choices for small software vendors, developers, and hobbyists learning and building data-driven client applications. There's also a lightweight version of Express called LocalDB which runs in user mode and is installed quickly without having to configure anything. SQL Server Express does have some limitations but it can be seamlessly upgraded (except for the money you need to pay) to higher end versions. Some of the limitations you should be aware of: 

  • max compute for a single instance is 1 socket or 4 cores
  • max memory buffer pool is 1,410 MB
  • max DB size is 10 GB
  • does not support most of the high availability features like log shipping, fast recovery etc.
  • only supports subscriber replication
  • SQL Profiler and SQL Server Agent are not available
Developer: SQL Server Developer edition is the best choice for hard-core developers as it includes all the functionality that the highest edition (Enterprise) includes so you can build and test any kind of application. However, this edition is restricted for development and testing only - it can not be used in production, therefore, as a developer you must consider the costs involved when deciding to utilize certain functionality that may only be available in the expensive Enterprise edition. 

Web: SQL Server Web edition targets Web hosts and Web VAPs aiming to lower the total cost of ownership. Limitations include:
  • max compute 4 sockets or 16 cores
  • max memory for buffer pool 64GB
  • missing some high availability features like backup compression, fast recovery etc.
  • missing some scalability features like resource governor, partitioned table parallelism etc.
  • No encryption for backups
  • Subscriber only merge replication
Standard: SQL Server Standard edition targets departments within large organizations and small organizations delivering basic data management and business intelligence.  Limitations include:

  • max compute 4 sockets or 24 cores
  • max memory for buffer pool 128 GB
  • missing some high availability features like mirrored backups, fast recovery etc.
  • missing some scalability features like resource governor, partitioned table parallelism etc.
Enterprise: SQL Server Enterprise edition is the ultimate edition that includes everything Microsoft has got to offer, it provides high availability, scalability, end-to-end business intelligence, fast performance, unlimited virtualization etc. The only limits on scalability are those dictated by the OS limits and the max relational database size being 524 PB