SQL Server

SQL Server: DBCC CHECKDB does not detect corruption

During my audits at customer places, it still happens very often to find SQL Server databases with page verification option configured to “none”. I always alert my customers on this configuration point because it can have an impact on the(…)

Backup a SQL Server database from On-Premise to Azure

SQL Server database backup & restore from On-Premise to Azure is a feature introduced with SQL Server 2012 SP1 CU2. In the past, it could be used with these three tools: Transact-SQL (T-SQL) PowerShell SQL Server Management Objects (SMO) With(…)

Master Data Services installation for SQL Server 2012

This posting is a tutorial for installing Master Data Services on your Windows Server 2012. Microsoft SQL Server Master Data Services (MDS) is a Master Management product from Microsoft, code-named Bulldog. It is the rebranding of the Stratature MDM(…)

SQL Server 2014: Are DENY ‘SELECT ALL USERS SECURABLES’ permissions sufficient for DBAs?

SQL Server 2014 improves the segregation of duties by implementing new server permissions. The most important is the SELECT ALL USERS SECURABLES permission that will help to restrict database administrators from viewing data in all databases. My article is(…)

SQL Server: How to find the default data path?

I have read a lot of SQL Server blog postings and articles in order to find the default data path. This post covers different SQL Server versions (SQL Server 2012, SQL Server 2014, SQL Server 2008, SQL Server 2005)(…)

SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature

Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles. This feature will help the database administrators and experienced developers to troubleshoot long running queries in real-time. I’m pretty sure(…)

Check SQL Server Error Log size with a policy

In many blogs or articles, we can read that it is recommended to recycle the error log to be sure that the size is not too big In the Best Practice Analyzer (BPA) for SQL Server, this(…)

Improving your SharePoint performance using SQL Server settings (part 1)

SharePoint performance is a recursive problem and preoccupation. As a Database Administrator, we have to deal with SharePoint when configuring SQL Server databases. In this article, I will propose a list of best practices in SQL Server settings(…)

SQL Server 2014: SELECT INTO operators can run in parallel

A couple of weeks ago, I had to provide some best practices in term of architecture for a “heavy writes” environment that consists in importing data from different sources into SQL Server tables. At the customer place, I was(…)

Transparent data encryption, key management and backup strategies

Transparent Data Encryption requires the creation of a database key encryption. The database key is a part of the hierarchy of SQL Server encryption tree with at the top of the tree the DPAPI. Then if we traverse the(…)