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.
October 7, 2016
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.
September 28, 2016
While Performance Monitor can be used for monitoring performance parameters of SQL Server, it doesn’t provide an option to get notifications when certain performance metrics values breach specified thresholds. The main reason why this is important is to be able to identify SQL Server performance issues as soon as possible, and react before they affect too many users. In addition, notifications on potential SQL Server performance issues could help avoid more serious problems by forecasting when they are likely to occur in order to avoid them. Therefore, one of the primary tasks for the database administrator is to establish a system that will ensure that he/she is going to be notified in case when certain performance metrics values are beyond the expected/desired range
September 8, 2016
Probably the most common issue when maintaining SQL Servers are slow-running queries. It is not unusual that a DBA gets information that the application or user database is slow, or even that users are getting timeout messages when working with SQL Server or SQL Server applications. Generally, when such SQL Server performance related issues are encountered, the first step in troubleshooting such an issue is to quickly identify whether and what slow-running queries in SQL Server are the cause of such a problem. The next step is then to determine why these queries run slow and what is the root cause for such behavior.
September 8, 2016
In Part 1 of this series using native SQL Server tools for monitoring AlwaysOn Availability Groups performance is described. In this part the focus will be on a third party solution – ApexSQL Monitor; designed to overcome most of the limitations of the aforementioned native SQL Server tools
August 9, 2016
This article explains how to set up the SSRS items documentation process using ApexSQL Doc, a SQL Server documentation tool which is used to document databases, SSIS packages, SSAS cubes and SSRS reports.
July 14, 2016
In this article, the last step of SQL Server database continuous integration (CI) workflow, or the first step of a continuous delivery (CD) workflow) the Sync step.
In the previous article, the Test step described how to create and run unit tests against a database. If all tests are passed, the tested database can be compared to a final QA environment or even Production databases, and a synchronization script will be created to publish the changes.
July 4, 2016
When working with a large number of databases on multiple SQL Servers, creating a foolproof disaster recovery plan can be challenging. Well organized backup and restore strategies will definitely help with this. In order to successfully implement these strategies in a larger environment, configuring automated backup and restore processes is a must. Some database administrators use the batch or power shell scripts for automation, while other prefer to use various 3rd party solutions. In both cases, it is necessary to format database backup names properly. Properly formatted backup names make the job of organizing and maintaining of the backup sets much easier. Old backup files are usually obsolete, and they can be easily identified and deleted from the drive either manually, or by using a script.
June 30, 2016
Deleting or changing objects may affect other database objects like views or procedures that depends on them and in certain instances, can “break” the depending object. An example can be that if a View queries a table and the name of that table changes. The View will no longer function.
June 9, 2016
In case of database development, in the same way as for the application development, there are always tasks such as developing a new feature, fixing bugs from the current release, experimenting with code in order to improve performance, usability in any way and so on. Because of all of this, it is essential that any changes that have to be committed, but not immediately, are segregated to an isolated environment, that does not affect the rest of the team or the main code. For instance, when developing a new feature, it may require a lot of changes to be committed, before the feature, or even a functional part of the feature becomes useful, so the rest of the team can apply it on their local copies of a database. Without having the isolation while developing, the team will lack the freedom to code, without having to worry about breaking the existing code base.
May 25, 2016