How to Sync Two SQL Server Databases Part 3


By:   |   Updated: 2020-07-28   |   Comments   |   Related: 1 | 2 | 3 | More > Comparison Data and Objects

Problem

In the first part of this tip series, I presented how to save a database schema to a JSON file, and how to create a database using that Jason file. In the second part I presented how to compare a database against the schema saved in part 1, and show all differences, if any.

Part 3 presents how to make the database have exactly the same structure (if possible) as described by the schema we provide as a template (or skeleton).

Solution

Making the database have exactly the same schema implies several steps:

  • Compare the database schema against the schema we provide (this is described in the second part)
  • Remove any extra elements (tables, columns and constraints)
  • Make columns to be exactly same type and length (this is not possible in any scenario, and we will let database server to decide this)
  • Add any missing elements (tables, columns and constraints)

In order to simplify the story, we will use the following conventions:

  • By table/column/constraint/index we refer to a table/column/constraint/index which exists in both database and provided schema
  • By missing table/column/constraint/index we refer to a table/column/constraint/index which exists only in the provided schema (and is missing from the database)
  • By extra table/column/constraint/index we refer to a table/column/constraint/index which exists only in the database (and is missing from the provided schema)
  • By schema we refer to the provided schema we use for making the changed

Removing extra elements from the database has to be done in a certain order. Let's say we have an extra table, which has a primary key and a foreign key in one of the tables. Removing the extra table is possible only after we remove the extra foreign key.

Compare Database (second comparison)

As you may remember from the second part, we presented how to compare the database against the schema. If there are differences, we will do a second comparison, this time with an extra parameter which says to remove extra columns, indexes and constraints.

If we have extra elements, they have to be removed in a certain order, otherwise SQL Server will complain. For example, if we have an extra column which is part of a primary key, and we also have a foreign key which refers to that primary key, we have three extra elements which we need to remove. In order to do this, we have to remove them in the following order: foreign key, primary key, and the column last.

This means that the second comparison with the parameter is done in the following order: constraints (foreign keys followed by other constraints), indexes and columns. Each comparison will remove extra elements if any.

I will present here only the code for comparing columns, the other code is in attached project.

Compare Columns

I recall the function CompareColumnsFromTable() which compares columns of the same table. This function has an extra parameter which says whether we remove the extra elements. The full code of this function is below, with the code which says to remove extra elements in red.

private void CompareColumnsFromTable(string tableName, JArray tableDb, JArray tableJson,
bool remove)
{
JObject missingColumnsFromDb = new JObject();
JObject missingColumnsFromJson = new JObject();
JObject differentColumns = new JObject();

// Compare each column of database with each column of schema
foreach(var (coldb, colnamedb) in from JToken coldb in tableDb
let colnamedb = (string)coldb["Column Name"]
select (coldb, colnamedb))
{
bool found = false;
foreach(var coljs in from JToken coljs in tableJson
let colnamejs = (string)coljs["Column Name"]
where colnamejs != null && colnamedb == colnamejs
select coljs)
{
found = true;
if(!JToken.DeepEquals(coldb, coljs)) // we have differences
{
_diffmsg += "\r\nIn table " + tableName + " column [" +
colnamedb + "] is different in database";

// Nqw we store the full columns definition
// We need this when deciding if column change is possible
JObject differences = new JObject
{
["database"] = coldb,
["json"] = coljs
};
_haveDiff = true;
if(remove)

differentColumns[colnamedb] = differences.DeepClone();
// See if the column (which will be changed) is part of a constraint or index constraint
RemoveConstraintIfContainsColumn(tableName, colnamedb);
RemoveIndexIfContainsColumn(tableName, colnamedb);
}
break;
}
}
if(!found)
{
_diffmsg += "\r\nIn table " + tableName + " column [" +
colnamedb + "] is missing from schema file";
_haveDiff = true;
if(remove)
missingColumnsFromJson[colnamedb] = coldb;
// See if the column (which will be changed) is part of a constraint or index contraint
RemoveConstraintIfContainsColumn(tableName, colnamedb););););););););););););););
RemoveIndexIfContainsColumn(tableName, colnamedb);
}
}
}

// Compare each column of schema with each column of database
foreach(var (coljs, colnamejs) in from JToken coljs in tableJson
let colnamejs = (string)coljs["Column Name"]
select (coljs, colnamejs))
{
bool found = false;

// Here we look ONLY for columns in json file which are NOT in the database
// These columns need to be added in the database
foreach(var _ in from JToken coldb in tableDb
let colnamedb = (string)coldb["Column Name"]
where colnamejs == colnamedb
select new
{
})
{
found = true;
break;
}

if(!found)
{
_diffmsg += "\r\nIn table " + tableName + " column [" +
colnamejs + "] is missing from database";
_haveDiff = true;
if(remove)
missingColumnsFromDb[colnamejs] = coljs;
}
}
if(remove)
{
// Add elements collected
if(missingColumnsFromDb.Count > 0)
_extraColumnsInJson[tableName] = missingColumnsFromDb.DeepClone();
if(missingColumnsFromJson.Count > 0)
_extraColumnsInDb[tableName] = missingColumnsFromJson.DeepClone();
if(differentColumns.Count > 0)
_differentColumns[tableName] = differentColumns.DeepClone();
}
}

In column comparison we do not remove extra columns, we remove only constraints and indexes these columns are eventually in. Before comparing columns we already compared constraints and indexes, and at this stage we might not have any extra constraints and indexes left.

Remove extra tables

After we compare database elements a second time and remove any extra constraints and indexes, we can remove extra tables, because there are no links (like constraints) between them and the rest of the database.

We remove extra tables using this code:

// Each table which is in _tablesFromDb and not in _tablesFromJson needs to be removed
// At this point we don't have any links (like FK) between these new tables and other tables
foreach(var schema in _tablesFromDb)
{
foreach(var table in schema.tables)
{
if(!FoundTable(schema.schemaName, table, _tablesFromJson)) // table not found, delete it
{
string tblname = "[" + schema.schemaName + "].[" + table + "]";
cmd.CommandText = "DROP TABLE " + tblname;
try
{
cmd.ExecuteNonQuery();
_diffmsg += "\r\n\tRemoved table " + tblname + "...";
}
catch(Exception e)
{
_error += "\r\nError delete extra table " + tblname + ": " + e.Message;
_haveErrors = true;
}
}
}
}

After we remove extra tables, we can add missing ones. The code is very similar with the one we use for creating tables when we create a new database, presented in the first part (see references), so we will not present here.

Make columns the same

Having new tables in the database, we need to make sure the columns which existed before adding new tables are the same. This means adding missing columns (these columns are missing from an existing table!), remove extra columns, and change column definition such that they will meet the schema. We mention here that this change is not always possible. For example, we had a varchar field which we defined initially as 100, but we decided later that 50 would be enough. If we already have a value in that table which is over 51 characters long, we cannot change the column definition.

A particular case of changing the column definition involves always encrypted columns. Regarding always encrypted columns we have the following cases:

  • If the column is encrypted and the keys are not the same, an error will be signaled
  • If the column is encrypted in the schema only, it will be encrypted in the database
  • If the column is encrypted in the database only, it will be decrypted in the database

We present here only encrypting/decrypting the column. The idea is to create a temporary table with the column encrypted or decrypted as described in the schema, copy the column from the table into this temporary table, delete the column from the table, recreate the column in the table as described in the schema, and copy back the column from the temporary table. This way we do not lose the information contained in that column.

The code which does this is:

/*
* We try to encrypt or decrypt a column. This is done as follow:
*
* - create a temporary table with that column
* - copy the column into the new table
* - delete the existing column from the table
* - create the column according to the new rules
* - copy the column from temp table back
* - delete temp table
*
* We need to build the correspoding strings for both columns:
* - for the column in the table ADD COLUMN according to the definition in the json
* - for the column in the temp table with no encryption, regardless the status of the
* column in the table
*/
if(!string.IsNullOrWhiteSpace(encryptionTypeDb) || !string.IsNullOrWhiteSpace(encryptionTypeJson))
{
SqlTransaction transaction = _conn.BeginTransaction();
cmd.Transaction = transaction;
try
{
// Create a temp table and copy table into it
string tmptablename = " [$__" + tableName.Replace("[", "").Replace("]", "") + "__$]";
query = "Drop Table IF EXISTS " + tmptablename + ";\n";
query += "select * into " + tmptablename + " From " + tableName;
cmd.CommandText = query;
cmd.ExecuteNonQuery();

// Remove column from table
query = "Alter table " + tableName + " drop column IF EXISTS [" + columnName + "];";
cmd.CommandText = query;
cmd.ExecuteNonQuery();

// Empty original table
query = "Truncate table " + tableName;
cmd.CommandText = query;
cmd.ExecuteNonQuery();

// Add the column according to the definition in json file
if(!string.IsNullOrWhiteSpace(encryptionTypeJson))
encrypttext=" COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=["+
_cek + "], ENCRYPTION_TYPE = " + encryptionTypeJson +
", ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')";
else
encrypttext = string.Empty;

query = "ALTER TABLE " + tableName + " ADD [";
query += columnName + "] " + datatypejson;

if(precisionJson.HasValue)
{
query += "(" + precisionJson.Value;
if(scaleJson.HasValue)
query += ", " + scaleJson.Value;
query += ") ";
}
else if(maxLengthJson.HasValue)
query += "(" + maxLengthJson.Value + ") ";
if(!isNullableJson)
query += " NOT NULL";
if(!string.IsNullOrWhiteSpace(encrypttext))
query += encrypttext;
if(!isNullableJson)
query += " NOT NULL";

cmd.CommandText = query;
cmd.ExecuteNonQuery();

// Copy back the column from temp table using bulk copy
query = "select * from " + tmptablename + ";";
cmd.CommandText = query;
using DataTable dt = new DataTable();
using(SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.Text;
adapter.SelectCommand.CommandTimeout = 240;
adapter.Fill(dt);
}
using SqlBulkCopy bulkCopy = new SqlBulkCopy(_conn,
SqlBulkCopyOptions.AllowEncryptedValueModifications | SqlBulkCopyOptions.KeepIdentity,
transaction);
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = 1000;
bulkCopy.BulkCopyTimeout = 240;
bulkCopy.NotifyAfter = 1000;
bulkCopy.WriteToServer(dt);

// Remove the temporary table
query = "Drop Table IF EXISTS " + tmptablename + ";";
cmd.CommandText = query;
cmd.ExecuteNonQuery();

transaction.Commit();
}
catch(Exception e)
{
_error += "\r\nError changing column [" + columnName + "] in table " + tableName + ": " +
e.Message;
_haveErrors = true;
transaction.Rollback();
}
cmd.Transaction = null;
continue;
}

Using the Code

This part contains the attached project, together with some JSON schemas and SQL scripts to test the functionality. As the database has always encrypted columns you can use it from the article with import from MS Access (see references). If you want to encrypt or decrypt a column, see the JSON schema to see how to add the information to encrypt a column, or just remove that information to decrypt the column.

I assume you know that a foreign key can refer to a table in a different schema.

References

Next Steps


Last Updated: 2020-07-28


get scripts

next tip button



About the author
MSSQLTips author Mircea Dragan Mircea Dragan is a Senior Computer Scientist with a strong mathematical background with over 18 years of hands-on experience.

View all my tips





Comments For This Article





download


Recommended Reading

How to Compare Database Schema in SQL Server - Part 1

Compare SQL Server Databases - Part 2

Ways to compare and find differences for SQL Server tables and data

Different Ways to Find SQL Server Object Dependencies

SQL Server tablediff command line utility





get free sql tips
agree to terms


Learn more about SQL Server tools