The main purpose of SQL Server Agent is executing administrative tasks within SQL Server, mostly as on-demand user actions. It is Windows service which runs continuously in the background, but it stores necessary information within SQL Server itself, in the msdb system database.
In this part of the continuous integration aka “CI” workflow, the Unit test step will be described. After the Populate step is successfully finished, where the new database test data is generated, the Test step is initiated, that will use SQL Server unit tests.
SQL Server database transaction log files are continuously pumped with the transactional information and details on database changes by the SQL Server itself. Even though the information from the transaction log files and backups can be used as a solid resource for database auditing, SQL Server does not provide a solid solution to utilize transaction log files to their full potential neither does it offer any simple way to explore those transaction log files or analyze the information within in order to perform continuous SQL Server database auditing.
Part 1 of this series describes some basics about performance baselining, while Part 2 explains how to establish and use baselining for detecting SQL Server performance issues in SQL Server using user defined tables, scripts and baseline calculations. In this final article, we’ll demonstrate how to implement baselining with a 3rd party tool, including some value added functionality
Generating test data for SQL Server database should be a process that easily populates a SQL database with data (where needed). Adding of test data can be done by manually creating scripts or by using a specific tool. But for repeatable processes like continuous delivery (aka DLM, CI, continuous integration), automation of test data generation is an important consideration.
It’s important to run SQL Server database unit tests to ensure that any changes made will not “break” the database or in other words violate the integrity, relationships or the functionality of the database itself. Ideally, many issues can be found in development, even pre-QA, if unit test coverage is created and run early and often. So even though we may understand that running unit tests against database changes is important, we may also experience the fact that creating and running SQL Server database unit tests manually can be time consuming, especially for lager databases which are under active development.
It is not that uncommon to find yourself in a situation when some inadvertent delete operations have deleted important data from a SQL Server database. The highest priority in these situations is to discover what exactly was deleted and how to get the deleted data back as quickly as possible. In this article, we are going to show you how to quickly recover data lost due to inadvertent delete operation using ApexSQL Log and ApexSQL Recover tools. Even though both tools generate the same output, a recovery script which rollbacks unintended deletes, the mechanisms used by them to acquire recovery information and create rollback script are quite different, so if the recovery with one tool doesn’t help, the other tool may.
Once a SQL database is committed to a source control repository with all of its objects, the next task is to commit the static data. Static data is non-transactional data from tables that generally don’t change often like postal codes, department names etc. Many teams treat this type of data akin to the database structure itself, by versioning it to track changes and deploying it from source control just like database objects. As data is versioned in source control, managing it can be automated as well.
Maintaining a before-and-after audit trail for sensitive tables can be time consuming, especially with a database that is under continuous development, and particularly in teams that use continuous integration. Most table changes will break existing triggers and necessitate their update. The ability to automate the refactoring and re-creation of a trigger based auditing layer, to keep up with underlying database changes, and run this process unattended or as part of a continuous integration process can be a huge time saver.