This article explains how to create customized policies for index defragmentation jobs for SQL Server
When creating indexes, database administrators should look for the best settings to ensure minimal performance impact and degradation. However, over time, indexes will get fragmented, which can severely impact server performance.
September 18, 2017
There are several ways to Export/Import SQL Server data to an Excel file. In this article, a couple of ways to import and export SQL Server data to an Excel file will be explained:
- Export SQL Server data to an Excel file using
- the SQL Server Import and Export Wizard
- T-SQL code
- Export to Excel from ApexSQL Complete
- Import SQL Server data into Excel using the Data Connection Wizard
September 11, 2017
In a busy system, many things can cause a SQL query to run more slowly than expected or desired. Sometimes, getting a handle on the root cause can involve digging into wait statistics, CPU, memory or network pressure, ferreting out problems in the I/O subsystem, looking for issues in a virtual machine configuration and many other things. Often though, the problem comes down to a simple symptom: the query is doing too much I/O.
August 31, 2017
As database administrators, we obsess over a few things, things we don’t compromise on. One of them is SQL Server availability. We may use several tools, scripts, and/or configurations, in addition to constant oversight, to ensure that the database is always available. We sometimes even go that extra mile to ensure availability, because we know it is easier to ensure a database is available than deal with the outcome of unavailability. In this post, let us look at some of the easiest ways to ensure availability.
August 29, 2017
Installing SharePoint farms can go from “click-click-next” to a full day work depending on the configuration level for the server farm. For administrators that are new to the farm, it can be quite troublesome to learn the farm’s configuration. In those cases, it is important to have a farm documentation with all the setting information. SharePoint documentation is also important for recreating sets of configurations for when failure occur.
August 25, 2017
Working with a database, at some point, usually means working with CRUD operations. Although not complex, CRUD operations are often used with SQL, so it is of great importance for developers to learn about how to create them efficiently and easily.
August 25, 2017
We sometimes may find ourselves in a stressful situation at work, where for example we need to update or delete some records in our database. We’ve all been there. Right after we click that “Execute” button, we realize we forgot to include a WHERE clause and the entire table is wiped instead of only one row. Although things like this can happen to the best of us, we can plan ahead and take preventative measures to make sure, we don’t get negatively impacted by the consequences of such a mistake.
August 17, 2017
Once you have a SQL Server query working correctly – that is, returning the correct results or properly updating a table with update, insert or delete operations, the next thing you usually want to look at is how well the query performs. There are simple things that you can do to improve the performance of a critical query; often those improvements can be quite dramatic!
In this article, we’ll look at one of the most-frequently-seen performance killers: SQL Server index scans. Starting with a simple example, we’ll look at what SQL Server does to figure out how to return the requested result, at least from a high level. That will allow us to zero-in on any bottlenecks and look at strategies to resolve them.
August 10, 2017
As it was explained in article on How to automatically compare and synchronize SQL Server database objects with a shared script folder, this article will explain the solution for the reverse process, when changes needs to be pulled from a shared script folder to a local database. This might be helpful if a developer returns from vacation and wants to catch up to the team with all changes or if a build has been tweaked, as part of a recent test/delivery and the latest version needs to be re-propagated directly to all developers via their local development database.
July 4, 2017
In some cases, source control systems are not an option for a particular SQL developer team, due to cost concerns, lack of approval etc., but the requirements for such a system, or close approximation, for managing changes across the developer team can still be a priority.
June 5, 2017
SQL Server maintenance is not a one-time event, but rather a part of a continuous process. Apart from regular backups and integrity checks, performance improvements can be achieved with index maintenance. If done at regular intervals, it can free the server to focus on other requests rather than losing time scanning for fragmented indexes.
June 5, 2017
In situations with few databases, maintaining the regular backup routine can be achieved easily, either with the help of a few simple scripts or by configuring a SQL Server agent job that will perform the backup automatically. However, if there are hundreds of databases to manage, backing up each database manually can prove to be a quite time-consuming task. In this case, it would be useful to create a solution that would back up all, or multiple selected SQL Server databases automatically, on a regular basis. Furthermore, the solution must not impact the server performance, or cause any downtime.
June 1, 2017
Each event that causes data loss or disruption of regular daily operations on a SQL Server can be defined as a “disastrous” event. These events include power outages, hardware failure, virus attacks, various types of file corruption, human error, natural disasters, etc. Although there are many methods that are focused on preventing these events, they still occur from time to time and therefore require proper measures to be addressed. One of the most effective methods for this purpose is the creation of a suitable disaster recovery plan.
May 15, 2017
Even now there are some isolated SQL Servers (without internet access or a limited one) that need to have databases updated with data changes on a daily basis. These kinds of SQL Servers need a way to get the latest data changes from the master SQL database.
May 8, 2017
The article How to detect whether index fragmentation affects SQL Server performance explains how and in what cases the Index fragmentation affects SQL Server performance, and when a DBA should and should not have to deal with fragmented indexes. This article will deal with the situation when Index fragmentation affects SQL Server performance and has to be dealt with
May 5, 2017
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.
April 3, 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