Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance January 23, 2018 by Robert Seles This is the third article in Ola Hallengren’s SQL Server Maintenance Solution series. The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts. Read more »
Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check January 10, 2018 by Robert Seles This is the second article in Ola Hallengren’s SQL Server Maintenance Solution series. It will cover the jobs for database integrity, backup history cleanup and job history cleanup. The installation of Ola Hallengren’s Maintenance Solution is covered in the first article in the series: Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution Read more »
Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution December 5, 2017 by Robert Seles Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier. Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting. Read more »
How to solve the Identity Crisis in SQL Server November 14, 2017 by Ed Pollack Description Identity columns provide a convenient way to auto-number an ID column within a table without the need to manage the sequence. This convenience can save immense amounts of time, but also presents a single challenge: What happens when an identity column runs out of space within the data type chosen? Read more »
SQL Server DB Migration – Cloning a database to another collation September 19, 2017 by Prashanth Jayaram Database migration is a vital task in any environment, complex or otherwise. Seamless migrations are the goal but the efforts required to ensure it are tremendous. Backing up and restoring the database is surely a preferred and a robust approach, but does it work well in all situations? How do we plan this when the source and the destination databases need different configurations? How do we make such a migration seamless? Read more »
SQL Server 2016 Maintenance Plan Enhancements September 18, 2017 by Ahmad Yaseen SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user. SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks. Read more »
Synchronizing SQL Server Instance Objects in an Availability Group September 8, 2017 by Derik Hammer Availability groups can no longer be considered new. The feature was released in SQL Server 2012 but I often notice that SQL Server Failover Cluster Instances (FCIs) are much better understood. For a very long time, when people thought about SQL Server they thought about the entire instance as a unit. Microsoft has been implementing new features and services which begin to segment the concept of an instance from the concept of a database. Read more »
How to drop a role in a SQL Server Database September 6, 2017 by Jefferson Elias Introduction Let’s say we have a database role that we don’t need anymore because we defined another security policy using another database role. What are the steps to follow in order to properly drop this database role? That’s the question this article will try to answer, covering as many cases as possible. In the following article, we will consider the simple steps we can follow in order to do this task using both SSMS and T-SQL. Then, we will focus on some facts that will lead us to the conclusion that, if we do it this way, it won’t work every time. We will list some situations where it could fail and define a test case situation in order to create a stored procedure that will do the job correctly, in all cases bymanaging these situations. Read more »
How to drop a SQL Server Login and all its dependencies July 19, 2017 by Jefferson Elias Introduction Sometimes, we need to drop a SQL Server Login because we gave an access to a person who left, this login was used by an application that has been decommissioned for example. Read more »
Overview of Resumable Indexes in SQL Server 2017 June 26, 2017 by Prashanth Jayaram Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article. Read more »
Deep dive into SQL Server Extended events – The Event Pairing target May 30, 2017 by Kaloyan Kosev Extended event sessions can use multiple targets to store captured information. We should use different targets depending on the type of data we are capturing and the structure of the data outcome we desire. The available targets include the following: event counter, ring buffer, event file, histogram, event tracing for Windows (ETW), and Event Pairing. Read more »
Deep dive into the Extended Events – Histogram target May 24, 2017 by Kaloyan Kosev An Extended events target is the destination for all of the information that is captured by Extended Events sessions. You can rely on couple of different targets such as event counter, event file, event tracing for Windows (ETW), ring buffer, event pairing, and histogram. Read more »
How to Spoof Production Environment Resources for Performance Tuning May 16, 2017 by Ahmad Yaseen Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems. Read more »
How to use Microsoft Assessment and Planning (MAP) Toolkit for SQL Server March 31, 2017 by Musab Umair As a Microsoft SQL Server Database Consultant, I have to visit multiple companies with variety of SQL Server versions and patch level. Generally speaking, we need to analyze the environment of these clients and get information about the infrastructure. To do so, we have to run multiple scripts and tools on almost all the machines at client. Though it’s a part of our job but at times it takes hours to get even the basic information about the SQL Server versions, patch level, configuration and small details which even client does not have. Read more »
How to discover and handle orphaned database users in SQL Server March 30, 2017 by Jefferson Elias Introduction Context As SQL Server database administrators, we should all know that, most of the time, a database user is linked to a SQL Server login. We do this to tell SQL Server that a SQL login LoginA has access to database Db1 using the context and permissions of UserA database user. Read more »
CHECKSUM page verification in SQL Server March 21, 2017 by David Alcock CHECKSUM is an option for page verification that is available at the database instance level and we can see what level of verification each of our databases are currently using by the following query: Read more »
Using Group Managed Service Accounts with SQL Server March 14, 2017 by Kaloyan Kosev Standalone Managed Service Accounts, introduced a long ago with Windows Server 2008 R2, were a ray of hope for the database administrators. They promised to provide automatic password management and simplified SPN management, meaning that the time-consuming task of maintaining passwords would be a thing of the past (not to mention the required downtime for this). Read more »
How to query data in a System-Versioned Temporal Tables in SQL Server February 20, 2017 by Marko Zivkovic This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size. Read more »
How to install, configure and use SQL Server Distributed Replay February 1, 2017 by Vitor Montalvão Introduction The Microsoft SQL Server Distributed Replay feature has been provided by Microsoft since SQL Server 2012 and it is used to replay captured traces against an upgraded test environment. Read more »
Using sp_server_diagnostics January 19, 2017 by David Alcock Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server. Read more »