Friday, July 25, 2014
  SQL Server Tags

Let there be Backups! Automated Backup Management Using SQL Server 2014

Jun 18 2014

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didn’t have proper backups in place.

Wouldn’t it be great if there was a way that DBA’s and engineers didn’t have to worry about backups and SQL Server was smart enough to perform the backup based on workload pattern (data changes) or based on some defined recovery interval and the backups are auto-magically stored offsite without having to worry about putting them on tapes and then shipping them.

Let’s welcome Managed Backups in SQL Server 2014. The SQL Server product team has done an awesome job in delivering this new feature that takes away the headache of managing and maintaining database backups. SQL Server Managed Backup automatically performs backups of your databases and stores them to Windows Azure Blob Storage Service.

SQL Server Managed Backups can be enabled at the database or Instance level. With the database level backup, you can only perform managed backup per database. However, if you enable instance level managed backups then you can perform managed backups for all user databases and for any new future databases. (Note: System databases are not currently supported with Managed backups) SQL Server Managed backups can be used for both on premise and hosted environments (ex: VM).

The good thing about SQL Server Managed Backups is that you do not have to specify the type of backup or frequency of the backup. You only specify the retention period which will take care of determining both the type and frequency of the database backup. You can also use the new backup encryption feature introduced in SQL Server 2014 along with SQL Server Managed Backups.

Encrypt Database Backups with SQL Server 2014

May 22 2014
Imagine this: You have sensitive information in your database (ex: SSN or Employee Payroll Information). You perform weekly full database backups, daily diffs and hourly T-log backups. You ship out one copy of the database backup to an offsite location. At this point the backup copy of the database is not in your control as it has already left your office. It is very possible for someone to restore the copy of the database since Microsoft supports restoring the database on any SQL Server with the right version in which the backup was performed. However, you would still feel uncomfortable knowing that your database has sensitive information and that it is possible for someone to get their hands on to the backup copy of the database. What would you do?

Pagesplits are Killers and Killers are Pagesplits

Say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely affect I/O operations.

Remotely Executing Stored Procedures, Functions, Queries and Commands Using EXECUTE

We use EXEC or EXECUTE command almost every day of our database life. It’s a command that allows us execute Stored Procedures (extended, system, user, CLR, etc.)  and Functions. However, EXEC command also has a powerful feature that allows you to run any command remotely. Not many developers are aware of this feature. Historically, EXEC/EXECUTE executed commands locally, meaning if you have a SPROC you can only run that on your local instance of SQL Server. That’s changed now.

Trigger-Less Triggers Using OUTPUT Clause

Feb 24 2014

You can now audit your changes using DML statements without even using TRIGGERS:


SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable.


Nov 08 2012

Author: Saleem Hakani (Microsoft Corporation)

You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:


Jul 18 2012

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time?

There are many things that happen behind the scenes that could contribute to the slowness of index operations.

Some of the reasons include:

1. Load on SQL Server,

2. Out of date statistics,

3. Server configuration setting for degree of parallelism,

4. Amount of available memory

5. Amount of available resources,

6. etc.

SQL Server can intelligently detect the load on the server and auto-adjusts the amount of resources that can be allocated to a process. In the case of Index operations; if SQL Server is busy, it will automatically adjust the Max Degree of Parallelism server configuration setting to accommodate the process or the load. This means it could either grant or limit the number of CPU’s Index operations can use. In many cases this is the best practice and you should not change the default value.


May 07 2012

Author: Saleem Hakani (Microsoft Corporation)

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference.

SQL Server 2012 introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns.


Jan 28 2012

Author: Saleem Hakani (Microsoft Corporation)

SQL Server 2012 introduces a brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.


HOME   |   Article Library   |   Scripts & Tools   |   Training Videos   |   Job Board   |   Resources
Copyright 2010 by SQLCOMMUNITY.COM