Comparing and synchronizing databases programmatically

ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations

But, what can you do when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable API

Click to continue »

Synchronize SQL Server databases in different remote sources

The scenarios

The applications used by travelling sales representatives, or other field workers – delivery drivers, visiting nurses, etc. are designed to collect data on remote locations and then send it to a data center. Also, the data from the data center occasionally needs to be sent back to these remote locations, to keep them up to date

For example, whenever nurses pay a visit to a patient, they enter the information about the visit into the database on mobile devices. At the end of the day, all these entries created during the day are sent to the central database in a hospital. After that, the nurses can synchronize mobile devices with the database in the hospital data center, so they get the new information about their patients, and also, the information about any new visits they need to make the next day

In the scenario such as this, there’s constantly a need to synchronize the information from a mobile device to a central database

Click to continue »

Check whether you can safely deploy to production database

The time when source control systems were used only for code development is gone. Today, database versioning is quite common, even in small companies

As there are many different source control systems, with different features and options, there are no strictly defined rules how to use them. Each company can create its own standards and rules for using a source control system for database versioning, following the best practices and recommendations

Click to continue »

Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – you might need a specific object (table, view, stored procedure, user, etc.) recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If your database is versioned under source control, you can easily find object there but let’s assume you only have SQL backups

Click to continue »

Script a database for specific DML records only

Sometimes the best test data is the data you have in your live, production SQL Server database. Since using production data is most often not an acceptable option, this requires retrieving the data from a live database table and inserting it into a new table. The challenge arises if you don’t want to insert all records from the original table but only the records that comply with certain conditions

How to create INSERT INTO script that moves only specific data from one table to another

Click to continue »

SQL Database deployment on several different SQL Server instances and databases

When work on a database in a development environment is completed, developers are required to send the new version in for testing. If the testing is done on one or two machines – this is not a problem. However, if you need to test the new version on 10 or 15 machines (with different operating systems, SQL Server versions, or service packs installed), this can be tedious

Another situation where scripts need to be executed on multiple servers is if you have many databases for different clients that were all derived from the same database and share many common objects. Any change you make in your development must be pushed to all users

Click to continue »

Highly customizable SQL Server auditing solutions

SQL developers and DBAs are increasingly required to include data management and SQL Server auditing into their custom applications. For companies that have to comply with a certain compliant policy and obey all security and privacy rules and standards, this is a must

One of the ways to audit a SQL Server is by mining database transaction logs. To be able to do this the database has to be in the full recovery model, and regular transaction log backups need to be created. This way, there will be enough information about the transactions and the online database transaction log will not grow too much in size

Click to continue »

Automatically compare and synchronize SQL Server data

There are numerous scenarios when data synchronization between two databases is needed, such as distributing data to different locations, integration of data from different locations into a central repository, or just a simple synchronization between your test and production database

The recommended solution is to use database replication

However, even with database replication there’s a need to check whether all the data is synchronized and to synchronize it if needed

Click to continue »

Compare SQL Server database schemas automatically

It’s common knowledge that running database changes directly in production, without thorough testing first, should be avoided

If you have enough resources in your environment, you will have at least one testing and one production SQL Server instance. However, that introduces another challenge. When everything is set up in the test, and runs smoothly and as expected, how can it easily be applied to the production instance?

Click to continue »