From time to time, a database object may need to be renamed for various reasons. When that happens, native features for renaming SQL Server database objects can be very useful. But, there are big differences between just renaming SQL Server database objects in the SQL Server Management Studio and Safe renaming them with ApexSQL Refactor.
This article will explain the differences between renaming database objects with SSMS and the ApexSQL Refactor’s Safe rename feature.
April 27, 2017
It’s often quite a challenge to keep all SQL databases located on different SQL Servers in sync. As time goes by, a lot of schema and data changes are made on QA databases on daily basis that require to be in sync with Production databases.
To keep everything in sync, there should a system that would be either triggered or scheduled to run the comparison of all SQL databases and synchronize the ones were changes are detected. This system should be also aware of any dependencies during the synchronization in order to keep the SQL database integrity.
April 5, 2017
For a SQL Server DBA handling multiple databases on any given time, knowing how to set up regular backup schedules, backups with unique names on a daily basis, making backup mirrors for redundancy, cleaning up old backup files is important. Equally important is automatic confirmation that the backups have been successfully created for the databases with an email notification. There are a couple of different ways to set up email notifications which can be done from Microsoft’s SQL Server Management Studio, or from a third party application for managing MS SQL Server backups like ApexSQL Backup.
April 3, 2017
After initial index creation in a SQL Server database, everything is properly ordered, which means that the logical index page order perfectly matches the physical index page order within the datafile. This is the ideal scenario and it allows for maximum query performance. If the table contains data that never changes, the index will remain perfectly ordered.
March 27, 2017
In this article, the Document step of the Continuous Integration workflow will be described. After the Sync step is successfully completed, in this step, database documentation is created, using the latest changed database version.
March 24, 2017
In order to perform any continuous integration or delivery process, an important requirement – to be able to pull out the database (which was previously versioned in source control), automatically and repeatedly, to recreate the database in a QA environment at a click a button (or scheduled on a commit).
March 10, 2017
Apart from numerous factors, poor index maintenance can be a reason for decreased SQL Server performance. If a database contains tables with numerous entries, that get updated frequently, it is most likely that high index fragmentation will occur. For smaller indexes, high fragmentation does not necessarily degrade the performance of the queries that are run on a table. But for the larger tables, with indexes that consist of 1000 pages and more, fragmentation could cause noticeable performance issues. Luckily, performing index maintenance tasks on a regular basis can eliminate the risk of degrading performance significantly. The most effective ways for treating index fragmentation are reorganize and rebuild index operations.
March 9, 2017
One of the most important things for a DBA when it comes to managing databases is schema change auditing. This involves tracking changes over time, identifying differences between two versions and even rolling back changes to a previous baseline.
March 1, 2017
Determine the replication agents’ status
Insight on replication agent status can be collected manually or programmatically. This article will consider both cases, and also when a replication agents’ status will be determined in relation with all publications, or a particular one.
February 24, 2017
Database status overview
Every database in a SQL Server environment has two basic states: full availability (online state) or full unavailability (offline state).
February 20, 2017
In the Understanding SQL Server database static data and how it fits into Database lifecycle management, we covered topics from the basics (what is the static data, why it is used), followed by the explanation of how to version control the static data, meaning how to initially commit, how to track changes and how to apply static data from SQL source control to a database.
February 13, 2017
When working with databases, availability and performance are of paramount importance. Performance of a database is based on multiple factors, key among them is being able to process queries quickly. That can be facilitated using indexes. Indexes are like the table of contents in a book, providing shortcuts to where the information is located. Otherwise, you would have to start reading the book from start until you find the information neededIt’s the same with databases, if it weren’t for indexes, for every query there would need to be an entire table scan.
February 8, 2017
As described in the article Understanding SQL Server database static data and how it fits into Database lifecycle management, the reasons for version controlling static data were reviewed.
February 8, 2017
As explained in this article Understanding SQL Server database static data and how it fits into Database lifecycle management including static data in SQL Server database source control is important for a successful CI/CD implementation.
February 3, 2017
Database development teams show an increasing interest in getting their SQL Server databases into source control. However, depending on the team’s actual needs, their development plan and the level of source control integration required, there are various ways to achieve getting a database under source control.
January 25, 2017
While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design. Automated backup and restore strategies ensures the recovery plan is successful. However, strategies relying on frequent backups, although part of a sound strategy, can start causing issues when available storage space becomes limited. Old database backups are in many cases unnecessary, and it is often prudent to delete those backups to increase available storage. This can be done manually or automatically. There are several options to delete old backup files automatically in SQL Server:
January 19, 2017
In the article “Understanding SQL Server database static data and how it fits into Database lifecycle management” we covered what is static data in general and numerous reasons why it needs to be version controlled.
January 13, 2017
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
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
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