SQL Server Backup to URL

Although this was introduced with SQL Server 2012, as per my experience, the usage of it is very low because it is unknown to many or do not use this facility to have a good backup strategy. When maintaining an important database, an implementation of a backup strategy is a must and it should be based on RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

Backup strategy is a part of Disaster Recovery solution. If you worry about the space for holding the backup, protecting backups from disasters, and securing your backups, one of the best options that can be used is Backup to URL.

Setting it up is very simple. You need to make sure that you or your company has an account with Azure and you have a storage created. Make sure a container is created in your storage for holding backups.

First, you need to create a Credential in your SQL Server instance for connecting with your Azure Storage. You can either use TSQL or GUI given under Security section.

Once the credential is created, it can be used for backing up your database to Azure Storage. Again you can use either TSQL or GUI.

BACKUP DATABASE Sales
TO URL = 'https://dinesqlstorage.blob.core.windows.net/sqlbackups/Sales_20160512.bak' 
      WITH CREDENTIAL = 'AzureCredential' 
     ,COMPRESSION
     ,STATS = 5;​

Once backed up, if you check your storage, you should see the file;

Note that most of the options that are available with general backup statement are available with backup to URL too. However some of the significant differences are;

• Appending to an existing backup file is not possible (INT | NOINT is ignored)
• MIRROR TO option is not supported
• Generally backup device name is allowed with 259 characters. This option needs 36 characters for azure URL and leaving 223 characters to specify name of the account, container and backup name.

Master Nodes, NameNode, Slave nodes, DataNode - understanding Hadoop cluster components

Everyone knows about Hadoop and everyone knows that it is mainly used for Big Data processing, or distributed processing. However, terms used with its components sometime make us confused, specifically items mentioned with the title. Here are some notes made on a discussion we had on Hadoop clusters.

Generally, Hadoop cluster comprises two main components; Master nodes and Slave nodes.

Master node

This node manages all services and operations. One Master node is enough for a cluster but having the secondary one increases scalability and high availability. The main operation Master node does is, running NameNode process that cordinates Hadoop storage operations.

Slave node

This node provides required infrastructure such as CPU, memory and local disk for storing and processing data. This does all slave processes; the main is running DataNode process. Generally cluster comprises at least three Slave nodes but cluster can be easily scaled up by adding many number of Salve nodes.

Namenode

This node is a part of Master node and responsible for coordinating HDFS functions. For an example, when a location of a file block is requested, Master node gets the location from the Namenode process.

Datanode

Datanode is a process that handles actual reading and writing of data blocks from/to storage. This is under Slave node and it is a slave for Namenode.

In addition to these, there are so many other components such as Job Tracker, Task Tracker, HDFC, etc. There are many articles on it, refer following if you need more details;

Master Nodes in Hadoop Clusters

Understanding Hadoop Clusters and the Network

SQL Server Brain Basher of the Week #042 - Identity property

Let's talk about an important property we always use; Identity property. This property has been used for generating Ids automatically, and most of the cases, this has been used as the surrogate key. Usage is very simple, all you have to do is, set it with CREATE TABLE statement, assigning the seed (starting value) and increment value. SQL Server will automatically generate a value for this when an insert is performed.

Since the value is not based on an application, we have no clue on the value generated. If we need the newly generated identity value for performing the next step of the process we have written, we used to call either @@identity or Scope_Identity function. Here is this week question based on it;

What is the different between @@identity and Scope_Identity function? Which one should be used for getting the last generated value within the scope?

Let's try to understand the purpose of these two. @@identity is a function that returns the last generated identity value regardless of the scope but for the current session. But Scope_Identity function returns the last generated value within the scope for the current session. That is the different between these two functions. For most cases, Scope_Identity is the best unless you have an unique requirement for getting the value via @@identity.

See this example, it shows how these two functions works.

-- Creating Customer table
-- Id is an identity, starting number is 1
CREATE TABLE dbo.Customer
(
 Id int identity(1,1) Primary key
 , Name varchar(100) not null
);

-- Creating Customer Log table
-- Id is and identity, starting number is 100
CREATE TABLE dbo.CustomerLog
(
 Id int identity(100,1) Primary key
 , Info varchar(100) not null
);
GO

-- This stored procedure accepts a name
-- and insert a record
CREATE PROC dbo.InsertCustomer @Name varchar(100)
AS 
BEGIN

 INSERT INTO dbo.Customer
 (Name) VALUES (@Name);

 -- Getting CustomerId value via @@identity
 SELECT @@IDENTITY AS CustomerIdFromIdentityFunction;
 -- Getting Customer ID via Scope_identity
 SELECT SCOPE_IDENTITY() AS CustomerIdFromScopeIdentityFunction;
END
GO


-- This trigger will insert a record to
-- CustomerLog table
CREATE TRIGGER dbo.InsertTriggerForCustomer
ON dbo.Customer
FOR INSERT
AS
BEGIN

 INSERT INTO dbo.CustomerLog
  (Info) VALUES ('Record is inserted');
END

-- Executing the first procedure
EXEC dbo.InsertCustomer 'Dinesh';

As you see, @@identity returns 100 and Scope_Identity returns 1. The reason for showing 100 for @@identity is, the insert happened inside the trigger. Trigger inserted a record and its Id is 100. Since @@identity for the current session, not for the current scope, it returned the last generated identity regardless of the scope.

How to check tables and stored procedures for in-memory migration

SQL Server introduced In-memory optimized tables with 2014 and it has been enhanced with SQL Server 2016. This improves the performance significantly and it is not that difficult to implement too. If we create a new database for new set of requirements, then it is easy to determine whether tables and stored procedures can be created using in-memory OTLP but it is bit difficult to check and see whether tables and stored procedures in existing databases are compatible for it.

We have been given a user-friendly wizard by SQL Server 2016 for determining the compatibility. This wizard helps us to go through tables and stored procedures we have in our database and check whether they can be converted. Not only that, if it is not possible, what are the possible reasons and solutions for converting them.

Let's try with one database. I have restored ContosoRetailDW database in my 2016 instance. I can find the wizard called Generate In-Memory OLTP migration checklist as below;

When click, Welcome Screen appears and the next page is for selecting objects to be checked. I can

get checked all or I can select individual items too. Remember the path set to Save checklist to. That is the place to be checked after completion of the wizard.

Last page allows to get the Powershell script generated too. If you need, you can get the code. At the end of the wizard, you see whether it has gone through all objects or not.

Now we can go through the checklist. Open the folder used, you should see three folders for; tables, stored procedures, and user defined functions.

Let's take one checklist and see. Lets open the checklist generated for DimDate table: MigrationAdvisorChecklistReport_DimDate_20160509.html file.

As you see, it clears says whether table is supported and what are the possible reasons if unsupported including possible solutions.

How to create a large database in seconds - SQL Server 2016 - Perform Volume Maintenance Task

In most cases, we do not set the database file large, initial size is always less than 1GB, unless a decision is taken to make it suitable for next few months. If the database has to be created with a larger file, then of course, we need to set the file size with database creation, and it takes long time for creation. Why it takes long time? We dont have data, and it is just an allocation from the space in the disk. But it takes time if the size is in GBs.

It takes long time for initializing the space required, by filling the file with zeros. This makes sure that previously deleted files cannot be accessed and no security violation. This Zeroing Process applies to;

• Create a database
• Add files to an existing database
• Increase the size of the file
• Restore a database

If required, this process can be stopped for SQL Server and reduce the time it takes. SQL Server 2014 and before, it has to be done by adding the SQL Server service account to a policy group called Perform Volume Maintenance Task. However SQL Server 2016 allows us to set this at installation itself.

If you select this checkbox during the installation, it will not take long time for peforming above mentioned operations. Here is a comparison I just did.

This code is based on SQL Server 2014 with default setting. Its service account has not been added to policy task and as you see, it has taken more than 2 minutes for creating a database with 10GB size file.

This shows the same code execution with SQL Server 2016. Note that I have enabled the option during the installation. As you see, it has taken only 14 seconds.

Note that this does not applicable for log files. You should consider this option if you frequently perform these operations against your SQL Server.

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Once I wrote a post on how to take a copy of Azure SQL Database into on-premise server: Taking backups of Azure SQL Database and restoring to on-premise server. It was a simple method, all we have to do is, export the database into a bacpac file and add to the server using Import Data-tier Application... 

With new operating system installation, I was trying to do the same but was hit by an error;

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Internal Error. The database platform service with type

Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid.....

I immediately realized the issue, it was all about the version of Management Studio. I tried the same with Management Studio April Preview and I was able to add the database exported from Azure.

As per the reading I did, if you try with Management Studio that comes with SQL Server 2014, you need Service Pack 1 and CU 5 or above.

Methods to Fix SQL Server Error 4064 Login Failed For User [Guest Post]

This post is written by Andrew Jackson

Overview

SQL Server Database helps users to create their own databases where they can store or retrieve data whenever required. Sometimes, user may encounter error while connecting to the database and it displays an error “Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)” This SQL Server Error message appears when SQL Server Login is not able to connect to the default database. The post will be discussing about the SQL Server Error 4064 and the way to resolve it.

SQL Server Error 4064 Login Failed For User

Each user that has an account on the SQL Server belongs to a database by default. When user tries to connect to a PC running SQL Server without specifying login database, the default database is used. However, if the default database is unavailable at the time of connection, the SQL Server Error 4064 will be displayed. 

Possible Causes of the SQL Server Error 4064

Some of the causes for the unavailability of the database are as follows:

• DB is in single user mode & only available connection is used already.
• When your DB no longer exists 
• Database is in suspect mode 
• DB has been detached 
• Database is offline
• DB is set to emergency status
• Does not have login account mapped to the user
• User trying to login has denied access.
• DB is part of a Database Mirror

How to Fix the Error 4064

One solution to avoid the error when the user’s default database is unavailable, is to logon as a user that can modify logins and change the user’s default database to a database that is currently available for a connection.

Steps for fixing the error 4064 are as follows:

- Before login to the user’s database, go to Options that is in right corner of the login window.

- Go to Connection Properties Tab of the login dialog box, enter the ‘master’ database in dropdown box changing default database to master, and click on Connect.

After successful login in the system, type the following TSQL command for your username and database.

User must make sure that they changes [test] with their own username and master with their database name. 

Alternative Method for Resolving Error Message 4064

• In SQL Server 2000 & SQL Server 7
  
  OSQL Utility can be used to change the default’s database by following the steps:
1. In the command prompt window, type the following ‘C :\> osql -E -d master’ and press Enter.
2. Type ‘1>sp_defaultdb 'user's_login', 'master'’ at the osql prompt and press Enter
3. Type ‘2>go’ at the second prompt and press Enter
• In SQL Server 2005 & later versions
  
  SQLCMD utility is used to change the default database server by following the steps below:
1. Go to Start -> Run. Type cmd and press ENTER.
2. SQL Server Login can use either Windows authentication typing the following in command prompt ‘sqlcmd –E -S InstanceName –d master’ or SQL Server Authentication with ‘sqlcmd -S InstanceName -d master -U SQLLogin -P Password’
   {InstanceName =Name of SQL Server Instance to which user wish to connect
   SQL Login=Server Login whose database created by default has got dropped
   Password=SQL Server Login Password}
3. Type ‘ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName’ at the sqlcmd prompt and press ENTER
4. Now, Type ‘GO’ and press ENTER.

Conclusion

The post is aimed to guide users in resolving one of the most common errors faced by users while trying to connect to the database that is unavailable at the time of connection. The possible causes of the SQL Server Error 4064 login failed for user are discussed as well. It further defines solutions to change the unavailable database to any valid database on the server in order to resolve the 4064 Error.

SQL Server 2016 will be available on 1st of June 2016

Microsoft SQL Server team has announced that SQL Server 2016 will be available on June 1, 2016. SQL Server 2016 comes with end-to-end data management including best way of managing business intelligence on our data on any device.

If you need to have a quick look on What's New, get this PDF.

If you need to know why you should go for Microsoft SQL Server, refer below images;

Gartner Magic Quadrant for Operational Database Management System

Gartner Magic Quadrant for Data Warehouse and Data Management Solutions for Analytics

Gartner Magic Quadrant for Advanced Analytics Platform.

SQL Server Brain Basher of the Week #041 - SQL Server 2016 - Editions

SQL Server 2016 will be available soon and will be surely seen more and more advanced features that will help us to continue with both operational and strategic level activities. Here is the question of this week based on SQL Server Editions.

As we know, generally we have two Premium Editions, two Core Editions and few additional editions;

Considering the main editions, one edition has been removed from SQL Server 2016. Which one has been removed from SQL Server 2016?

As per the current announcements, Business Intelligence edition is not included with SQL Server 2016. There can be changes with official announcements but currently, it is not available.

How SQL Server handles our requests for modifications

This is not something new with latest Microsoft SQL Server but this is still being discussed and it is unknown or unclear to many of database developers. While I was discussing transaction log of SQL Server database with few, as a part of it, how SQL Server accepts our requests and modifies records was discussed. Thought it is useful to everyone, hence making a post on it;

What really happens when we send a request to SQL Server? It can be an update or a delete. Request might be related to one record or many records. Have a look on below image;

This starts with the request. Either using an application or connecting directly to SQL Server using something like SSMS, we send the request. Once SQL Server received the request, it checks data pages related to the records. If data pages required are not in the memory (or buffer cache), it loads relevant data pages from the disk to memory. Then, remember, it modifies records in pages that are in the memory, not pages in the disk. That is what 1st and 2nd steps in the image explains.

Third step is, updating the transaction log in the disk. Once the page (or pages) in the memory are modified, they become dirty pages. Then SQL Server writes redo and undo information to the log file. During this update, pages related are locked until the transaction log is completely updated.

Once the log is updated, the acknowledgement is sent to the application. Note that, even though the data files are not updated, we receive a message saying records are successfully updated. But we do not want worry, even something happen after we received the message, SQL Server can recover committed records, making sure durability which is one of the properties of the transaction, is satisfied with SQL Server.

Later, after one ore more transactions, a process called Checkpoint writes all dirty pages back to the disk, updating data files. This is how SQL Server handles our update requests.

SQL Server 2016 - Manipulating data in Azure Storage using PolyBase

When PolyBase is opened with SQL Server editions other than APS or PDW, I tested it with SQL Server 2016 CTP 2.1. It had some issues but was able to get it worked with some workaround. Thought to do the same with RC3 since there are many enhancements, and it works fine now. If you are interested in Polybase, have a look on the post I have written in June 2015; SQL Server 2016 features: Polybase: SQL Server connector to Hadoop.

What is PolyBase? It is a feature that is available with SQL Server 2016. It facilitates us to use TSQL against data stored in Hadoop or Azure Blob Storage, allowing us to combine structured data with semi-structured data. Data warehousing benefits a lot from this as it reduces the time spending on ETLing and supports real-time analysis. Other than that it can be used with OLTP databases and can be used for archiving data as well.

Currently this feature is available with SQL Server 2016 RC3, I did not come across Edition Comparison related to 2016, hence no idea which Edition will support with final release. Note that there are few prerequisites like .Net framework 4.5 or later, Oracle Java SE runtime. Make sure you have installed all required before installing SQL Server and enabling Polybase. You can read a post I wrote on installation SQL Server RC3: Installing SQL Server 2016 RC1 - Sharing issues.

Generally, we should install PolyBase feature as Standard-alone Polybase. If you want to make a collection of SQL Server instances as a PolyBase Scale-Out Group, then select PolyBase Scale-Out Group during the installation.

PolyBase support two Hadoop providers: Hortonwork’s Data Platform (HDP) and Cloudera’s CDH either on Linux or Windows. In addition to that it can connect with Azure Blob Storage too.

Let's start with a simple one. I have uploaded a file to my Azure Storage. This file holds small number of CDR records and let's see how we can read these records using TSQL.

In order to access this file, we need to make sure PolyBase is configured properly. Make sure two new services: SQL Server PolyBase Data Movement and SQL Server PolyBase Engine are running. In addition to that, make sure that it is enabled and connectivity is set as below.

For understanding the value that needs to be set with Hadoop Connectivity, read this.

Let's create a database and configure Data Source. Note that, we need to create a Database Credential first for using it with the Data Source.

CREATE DATABASE Telco;
GO

USE Telco;
GO

-- Create a master key to encrypt credential secret.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

-- Create a database scoped credential (Azure Storage).
-- Enter your azure storage primary key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential WITH 
        IDENTITY = 'AzureStorageUser'
  , Secret = 'primary key';

-- create the external data source
CREATE EXTERNAL DATA SOURCE DinesqlStorage
WITH
(
 TYPE = HADOOP,
 LOCATION = 'wasbs://cdrcontainer@dinesqlstorage.blob.core.windows.net/'
 , CREDENTIAL = AzureStorageCredential
);

Read more on CREATE EXTERNAL DATA SOURCE.

Next step is creating a File Format for the file we gonna read. PolyBase supports Delimited Text, Hive RCFile, Hive ORC, and Parquet. Let's create the File Format for our file.

-- Create an external file format (delimited text file).
CREATE EXTERNAL FILE FORMAT CDRFileFormat WITH (
        FORMAT_TYPE = DELIMITEDTEXT, 
        FORMAT_OPTIONS (FIELD_TERMINATOR =';', 
                USE_TYPE_DEFAULT = False)
);

Read more on CREATE EXTERNAL FILE FORMAT.

Last step is creating an External Table matching with the file uploaded combining the File Format. Read more on it with CREATE EXTERNAL TABLE. Here is the code I used.

-- create the table using file format created
-- and for the file uploaded
CREATE EXTERNAL TABLE CDRData
(
 MSIDN nvarchar(100)
 , IMSI nvarchar(100)
 , IMEI nvarchar(100)
 , [Plan] nvarchar(10)
 , CALL_TYPE nvarchar(20)
 , CORRESP_TYPE nvarchar(10)
 , CORRESP_ISDN nvarchar(100)
 , Duration int
 , [Time] nvarchar(20)
 , [Date] nvarchar(20)
 )
WITH
(
 LOCATION = '/cdrfiles'
 , DATA_SOURCE = DinesqlStorage
 , FILE_FORMAT = CDRFileFormat
);

Now I can simply query data in my Azure Storage using the table I created. Not only that I can join this dataset with my other tables and do more analysis. I will add more complex examples with next set of posts.

SQL Server 2016 - Connecting to Azure SQL Server using SSMS - RC3

Please note that there is an issue with connecting to Azure SQL Server using latest RC3 build. If you are experiencing the following error, note that it is an issue related your system.

An error occurred while creating a new firewall rule. (ConnectionDlg)...

There is no other options, if you need to connect with your Azure SQL Server, you need to open the portal and add your IP address using Firewall Settings.

SQL Server 2016 - System-Versioned Temporal Tables

Although the word "Versioning" was not used, we have been maintaining versions of records, or more precisely history of records using different techniques. SQL Server offers various ways of handling or maintaining history records, or changes that have been done to our records, using features like CDC, CT, Optimistic Isolation Levels. Most of these features do not support actual "versioning" of records but these features can be used for handling different scenarios.

SQL Server 2016 introduces a new feature called System-Versioned Temporal Tables that provides the entire history of our records related to the changes done. This feature records versions of the records based on  update and delete operations with the validity period of the version, allowing us to see not only the current record, state of the record during any given period, or allowing us to do point-in-time analysis. This feature is based on ANSI SQL 2011 standard but SQL Server current 2016 release does not support all the features that describes with it.

Let's write a code and see how it works. The following code creates a Database called Sales and a Table called Customer in Sales. Code creates Customer as a Temporal Table that requires few additional elements with the CREATE TABLE statement. It requires two datetime2 columns for maintaining the validity period. In addition to that, it needs SYSTEM_VERSIONING = ON and optionally a name for the history table. If the name is not mentioned, system will create one for us.

CREATE DATABASE Sales;
GO

USE Sales;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) Primary Key
 , FirstName varchar(100) null
 , LastName varchar(100) not null
 , CreditLimit money not null
 , ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START -- datetime2(any precistion)
 , ValidTo datetime2(0) GENERATED ALWAYS AS ROW END -- datetime2(any precistion)
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));

Let's insert three records and query both tables;

-- Inserting records
INSERT INTO dbo.Customer
 (FirstName, LastName, CreditLimit)
VALUES
 ('Dinesh', 'Priyankara', 10000)
 , ('Jack', 'Wilson', 15000)
 , ('John', 'Knight', 3500);

-- Checking records
SELECT * FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;

As you see, history table does not show any records and last two datetime2 columns have been automatically filled. Now let's make some changes. Note that records have been inserted on 14/04/2016 and will be updating records on 16th, 17th and 19th.

-- Modifying a record - 2016-04-14 01:56:23
UPDATE dbo.Customer
 SET CreditLimit = 12000
WHERE CustomerId = 1;

-- Deleting a record - 2016-04-17 01:57:17
DELETE dbo.Customer
WHERE CustomerId = 3;

-- Modifying the same record - 2016-04-19 01:57:26
UPDATE dbo.Customer
 SET CreditLimit = 20000
WHERE CustomerId = 1;

-- Checking records
SELECT * FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;

As you see, three records are in the history table; 2 records for the Customer Id 1 for two modifications made and 1 record for the Customer Id 2 for the deletion. This table exactly says how recorded are changed and when they have been changed, not only that it allows us to see the state of the record based on the validity period.

Let's see how we can retrieve records. There are multiple ways for querying records. We simply query the table without considering the history or we can go through the history using new clause given; FOR SYSTEM_TIME. Note the different between BETWEEN and FROM.

-- Retrieving the current record
SELECT *
FROM dbo.Customer
WHERE CustomerId = 1;

-- Retrieving for a date
-- Better include time too
SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME
 AS OF '2016-04-17 00:00:00'
WHERE CustomerId = 1;

SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME
 BETWEEN '2016-04-16 00:00:00' AND '2016-04-19 00:38:43'
WHERE CustomerId = 1
ORDER BY ValidFrom;

SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME
 FROM '2016-04-16 00:00:00' TO '2016-04-19 00:38:43'
WHERE CustomerId = 1
ORDER BY ValidFrom;

You can read more on this at msdn.

Here is the cleaning code if required;

ALTER TABLE dbo.Customer SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE dbo.Customer;
DROP TABLE [dbo].[CustomerHistory];
GO
USE master;
GO
DROP DATABASE Sales;

SQL Server 2016 New Three Releases: SQL Server RC 3, Management Studio, SQL Server Data Tools

Microsoft SQL Server team has announced three new releases related to SQL Server product suite;

1. SQL Server Release Candidate 3
   This is the last Release Candidate and it is available for downloading now.
   Click here to download it and click here to see the release note.
   
2. SQL Server Management Studio April Preview
   New Preview is available with bug fixes and some enhancements. Click here to read more and click here to download it.
   
3. SQL Server Data Tools Preview Update
   An update has been released for SQL Server Data Tools specifically for SQL Server Release Candidate 3. Click here to download the Preview.

SQL Server 2016 - Parameter Sniffing can be controlled at database level

SQL Server uses Parameter Sniffing for creating the best plan for your query (or stored procedure) that sniffs values passed for parameters and generates the plan accordingly. In most of the cases, this is the best for most queries and stored procedures unless the values for parameters are vary with each execution. There was no easy way of enabling and disabling this with previous versions but trace flags 4136 allows you to disable Parameter Sniffing at server level. However the question is, should we disable it, or why should we disable it? 

Let's try to understand this before looking at the setting given with SQL Server 2016. Have a look on the following code;

USE AdventureWorks2016CTP3
GO
SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID = 1
SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID = 1000

SQL Server generates two different plans for these two queries because they are ad-hoc statements and two different values are passed to SalesOrderDetailID. The plans were generated based on the values passed, basically using Parameter Sniffing. That is why we see two different plans for the same query.

With stored procedures, this is bit different. The plan for the procedure is created with its initial execution, based on the values passed for parameters and it stores the plan in the cache. SQL Server does not sniff parameter values with subsequent executions for generating the plan again unless it cannot find the cached plan. This speeds up the execution because SQL Server does not need to spend time and use resources for generating the plan again and again. But, remember, it uses the same plan for all type of values passed, it may not be the optimal plan for all values.

CREATE PROC GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

EXEC GetOrderDetails 1
EXEC GetOrderDetails 1000

Okay, now why we should disable this behavior? If we disable this, SQL Server uses average distribution statistics for generating the plan which is not best for almost all implementation. If you really need a plan that does not depend on values passed and you need sort of average type plan for all executions, then you will benefit disabling Parameter Sniffing.

Read more on this with my article: Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

As I mentioned above, the trace flag can be used for disabling but it applies to all databases. With SQL Server 2016, whether you need it or not, this setting is available at database level and can be enabled/disabled anytime.

This is how it display with database properties.

Let's disable this for AdventureWorks database and see how it works.

Let's clear the cache and execute the procedure again and see.

-- Clearing the cache
DBCC FREEPROCCACHE;

EXEC GetOrderDetails 1;
EXEC GetOrderDetails 1000;

As you see, a plan has been generated without considering values passed and it is being reused. If you think that your codes benefit with this, then this new option can be applied to your database, otherwise, keeping the default is the best.

Installing SQL Server 2016 RC1 - Sharing issues

Just sharing my experiences with installing SQL Server 2016 RC1;

If you are planning to install SQL Server 2016 with Polybase support, make sure you have installed Oracle JRE 7 or highest, otherwise you will be facing this issue;

It can be downloaded at: http://www.oracle.com/technetwork/java/javase/downloads/index.html

In addition to that, you might get time-out  issues during installation, in my case, though it occurred several times, retry worked me and installation was successful.

I renamed my computer, Will my SQL Server instance work? Should I do something on it?

This is something we always worry, what will happen if the name of the host machine is renamed? After renaming, can I connect with my SQL Server instance using new name set with the host, or I have to re-install SQL Server, or I have to do some setting changes? Let me share what I just did.

My computer name was initially set as DESKTOP-QOKBL3L because I forgot to set the name properly when installing the OS. And not only that, I installed an instance of SQL Server 2012 as a named instance too. After realizing that name of the computer is not as I want, I just changed it as DINESH-HM-LAP01. Now can I connect with my SQL Server instance?

I just tried with the old name as I used to;

As you see, it did not allow me to connect. Then I tried with my new computer name;

And I was able to connect with my instance, yes without doing anything. But, does it mean that I do not want to anything?

Let's check the server name using TSQL;

As you see, not all metadata is updated, means it needs some updates. For updating metadata, we need to drop the server and add again. After that, a service restart is required, once done, if the above query is run again, both will show the new name instead of old name.

sp_dropserver 'DESKTOP-QOKBL3L\SQL2012';
GO
sp_addserver 'DINESH-HM-LAP01\SQL2012', local;
GO

For more info on this, read this article in MSDN: https://msdn.microsoft.com/en-us/library/ms143799.aspx

Point-In-Time restoring with Azure SQL Database

We know that Point-In-Time restoration is possible with SQL Server and it is one of useful ways of recovering data in a disaster-recovery situation. This requires backup taken that contains data that needs to be recovered. If no backup is available, we can still achieve this by taking a backup of the database, may be tail-log backup.

However, Azure SQL Database works differently. For Point-In-Time restoration, you do not need backups if the recovery related to certain time period. Azure SQL Database allows you to recover your database (or restore) to any restore point within 7 days if the tier is Basic, 14 days for Standard, 35 days for Premium. This is a very simple process and it restores the database with a different name like Database Name + TimeStamp.

Here are the steps for Point-In-Time restoration;

First login to your Azure Portal and get SQL Servers blade opened. Select the server and get the preferred database server blade opened.

The select the database from Databases section and get Properties of database opened. Click on Restore button for opening the blade for resorting.

You can see oldest restore point based on your tier. Select the Point-In-Time as you want, change other properties if need (example, different server) and click OK to get it restored.

Once restored, it can be accessed via Management Studio just like you access the other database. One thing you need to remember is, you cannot see both databases by connecting to the server, hence when connecting, make sure you mention the database name for connecting to newly created database.

Troubleshooting agent jobs

Administrators always try to automate routine tasks, making sure that all required operations are done on time without failures. The main component used for automation is SQL Server Agent, which is used for creating jobs. If you have noticed that a scheduled job has not run or all jobs are not running, how do you troubleshoot? How do you start troubleshooting?

Here are some guidelines for troubleshooting based on a discussion I had;

• First thing needs to be checked is whether SQL Server Agent service is running or not. You need to make sure that Startup type is set to automatic, this makes sure that service is started when the server is restarted. If, for some reasons, service is not running, and you cannot even manually start it, check following;
• Check and see whether account assigned for the Agent is valid and no issues with the password. The account assigned may have been expired, changed or disabled. Check the system log for more details.
• Check the msdb database. If it is corrupted or offline, Agent will not be started.
• Check the job history. Check whether last run was successful. There can be some issues with business logic implemented.
• Check whether the job is enabled. Someone might have disabled it.
• Check whether the schedule set is either expired or disabled.
• Check and see whether proxy accounts are working properly if you have used. Check credentials used for proxy accounts.
• Check dependencies. There can be steps in the job that run without any issues but some. Check whether all required items, such as files, folders and all required permissions for accessing are available.

SQL Server Developer Edition is free for us

Thought it was part of April fool's joke but it looks like it is not. The Developer Edition which is the exact copy of Enterprise Edition is freely available for developers, it is available for Visual Studio Dev Essentials members. Here is the link for that: https://www.visualstudio.com/products/visual-studio-dev-essentials-vs?wt.mc_id=WW_CE_BD_OO_SCL_TW_DESQLBenefitAnnouncement_SQL.

This is still for SQL Server 2014, once SQL Server 2016 is release, Microsoft will make 2016 available too.

Download free and test all enterprise features.

SQL Server 2016 Release Candidate 2 available for downloading

Have not you tried this yet? Here are some reasons for downloading and trying this out;

• In-memory performance and built-in operational analytics for faster transaction queries and deeper insights.
• Protection and security on data at rest and in motion using Always Encrypted technology.
• Rich-Enterprise ready high availability and disaster recovery solutions with AlwaysOn technology.
• Advance analytics capabilities and rich visualization supportability on any device.
• And much more....

Read more on this:

Download SQL Server Release Candidate 2