Automatically comparing and synchronizing data changes in a SQL Server database

For the second use case, when only data changes are made to the source control repository, a combination of 3rd party applications – ApexSQL Build and ApexSQL Data Diff, can be used to perform this Sync step. ApexSQL Build can be used to build a database from the source control repository along with its static data, while ApexSQL Data Diff can be used to synchronize data from the built database to a QA database.

January 13, 2017

Automatically comparing and synchronizing SQL Server database schema changes

If only schema changes are made in the source control repository, a 3rd party tool – ApexSQL Diff can be used to perform the schema synchronization. ApexSQL Diff is a tool that can be used to compare and synchronize schema differences between live SQL database, source control repositories, database backups, script folders and ApexSQL snapshots. It can handle any dependencies, triggers, etc. and create an error-free synchronization script, while ensuring a database integrity.

January 13, 2017

How to automatically process SQL Server databases with Unit tests

In order to begin this process, first the tSQLt, (SQL Server database unit testing framework) has to be installed on the database which will be used for unit testing. This can be done manually in SSMS by right-clicking the selected database in the Object explorer, find the Unit tests menu and select Install tSQLt.

January 13, 2017

How to automatically create synthetic test data for a SQL Server database

ApexSQL Generate is a tool that can populate SQL databases with test data using multiple generators for each column. It supports a full range of SQL data types and has an ability to recognize them in a loaded SQL database and provide real-world test data based on contained data types. It can generate: randomized test data using the Random generator and various options, and using the Regular expression generator, sequential data using the Incremental generator, and much more.

January 13, 2017

How to monitor SQL Server failover events automatically

Failover event overview

In general, the term “failover” refers to switching from a previously active machine, other hardware component, or network to a passive (or unused) one, to sustain high availability and reliability. In most cases, a failover event is an automatic process, while a the similar event, switchover, requires manual intervention in switching between the active/passive elements.

January 13, 2017

How to automatically monitor a SQL Server database for schema changes and create an audit log in source control

The challenge

As a DBA considering version controlling a database, there are a lot of challenges to setting up the team with software to check in changes, to manage the development environment (shared or dedicated models) and set the rules of the game – locking vs not locking etc. But before committing to this fully, a potentially preliminary stage would be to set up a centralized system, where the database was automatically committed to source control each night. This could be done by a single person and wouldn’t require developer participation or even knowledge.

January 10, 2017