SQL SERVER – 2016 IF EXISTS Functions on SQL Azure Databases and More

The experimentations of working with the next release of SQL Server is always fun and when I wrote the blog SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause, lesser did I know such capabilities are already existing with Azure SQL DB. When I was presenting at one of the local usergroups, I told most of the capabilities come to Cloud first, get test and the very reason that it scales without problem on cloud gives one the confidence that it would work in our enterprise. One of the attendees asked, has all features first introduced to cloud only?

That got me curious and I started to hunt to find is the rollout always to cloud first. I thought let me experiment first and see. I took my DIE (DROP IF EXISTS) post to see if I can run it on SQLAzureDB to know if it worked seamlessly. First to my connection to the latest database I created.

 SQL SERVER   2016 IF EXISTS Functions on SQL Azure Databases and More

As I was running the CTP2.0 of SQL Server 2016, I wanted to see what version was running on cloud already. It was a surprise that the production was already running the latest bits. It was a pleasant surprise and I know by the time you read this article and try out – maybe Microsoft would have upgraded to the latest bits too. So is the scale and agility that cloud brings to the table.

Now coming back to the core, I was now getting a feeling that my DIE (DROP IF EXISTS) code will work now perfectly. I went ahead with the following code and it executed just fine.

DROP TABLE IF EXISTS my_test
GO
DROP PROCEDURE IF EXISTS my_test_procedure
GO

Now, it was time to search for something interesting that I can do on cloud that was not available on-premise SQL Server. So I went to the documentation to search for something interesting. I found a new function DATEDIFF_BIG() that was interesting. Currently the DATETIME functions that we use with SQL Server cannot give the high precision values. Let me take a typical value of – number of nanoseconds in a year?

SELECT DATEDIFF_BIG(nanosecond, '2015-1-1 00:00:00.0000000', '2016-1-1 00:00:00.0000000')
GO

This returns “31536000000000000” which is not available with SQL Server 2016 on-premise version currently. It was a great learning to see the rate at which innovations happen on cloud. If you run this on an on-premise SQL Server – you will get the following error today.

Msg 195, Level 15, State 10, Line 1
‘DATEDIFF_BIG’ is not a recognized built-in function name.

I am curious to know, how many times have you wanted the higher level of precision when working with DATEDIFF functions? What are some of your usecases for the same? I would surely like to learn some from you. Do let me know via the comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Connecting to Azure SQL DB

Learning new methods of working with SQL Server can always be challenging. I found the new learning coming my way while connecting to Azure SQL DB. I was confronted with an error and I was not sure where to start. So this blog is like a travel log of my learning to connect to an Azure SQL DB which looks almost same as what an onpremise SQL Server looks like. When I was connecting to Azure SQL DB, I was getting an error as shown below:

azure db 01 SQL SERVER   Connecting to Azure SQL DB

But the basic information will be around how did I get the connection string to connect to my database. It can be got from the azure portal. As I goto the server and check on the information, I will be presented with the server details which was being used in our SQL Server Management Studio.

azure db 02 SQL SERVER   Connecting to Azure SQL DB

Now to manage the error message, look at the Dashboard and click on the “Manage allowed IP addresses” from your machine where we are getting this error.

azure db 03 SQL SERVER   Connecting to Azure SQL DB

Once in this page, you will be able to see the current IP address. This is the same address that you are currently using to connect to Azure SQL DB. Click on the next and this must add the same to the grid below. This is an important step because you don’t want any IP address to connect to SQL Server and this has been used by a number of companies when accessing databases on the cloud to control access.

azure db 04 SQL SERVER   Connecting to Azure SQL DB

When I tried to the same using SQL Server 2016 Management Studio, I saw it also gave a different point of entry and a pop-up which can be used to connect to Azure and add the current IP address or the IP address ranges.

azure db 05 SQL SERVER   Connecting to Azure SQL DB

Once this has been done, we will be able to connect to Azure SQL DB via SQL Server Management Studio. As you can see, the icon for the server connected also looks modern and is a great way to work with another form of SQL Server.

azure db 06 SQL SERVER   Connecting to Azure SQL DB

As I sign off now, would like to understand how many of you have had an opportunity to work on Azure? What has been your experience? Do let me know via comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – InMemory Tables on SQL Azure DB

There have been tons of enhancements in the InMemory space with SQL Server 2016. I am due to write about them in this blog. Having said that, one of my DBA friend asked me based on the Pluralsight course on SQL Server 2014 Administration – Does SQL Azure support InMemory capabilities?

This feature was not available for quite some time now, but ever since the V12 version was introduced. I saw Azure now supports InMemory. On reading some of the SQLAzureDB documentation, I saw they have extended this support. Immediately, I created a database on Azure SQLDB and connected it with SQL Server Management Studio. Our Object Explorer will look like below:

inmemory azure 01 SQL SERVER   InMemory Tables on SQL Azure DB

Create a Query window and let us identify if our DB (sqldude as shown above) supports our InMemory objects (tables and procedures).

SELECT SERVERPROPERTY('IsXTPSupported');
GO
CREATE TABLE tbl_InMemory
(  ID INT NOT NULL,
Name VARCHAR(50) NOT NULL
CONSTRAINT Const_InMemory PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000)
)  
WITH (MEMORY_OPTIMIZED=ON)
GO

As you can see the above SERVERPROPERTY it returns 1 to confirm this database supports InMemory objects.

inmemory azure 02 SQL SERVER   InMemory Tables on SQL Azure DB

Yet another method to identify if a database support InMemory OLTP capability, we can use the below function:

SELECT DATABASEPROPERTYEX ('sqldude', 'IsXTPSupported');

This will also return 1 if it supports. This can be used for Azure DB’s and on-premise installations too. At this point in time, I wanted to bring some restrictions of using InMemory OLTP with Azure:

  • It can be only used in premium DBs
  • It can be used only in new DBs. You cannot use in copies of pre-existing database unless the pre-existing database was InMemory OLTP enabled from private preview.
  • Downgrade: Databases with InMemory OLTP objects cannot be downgraded to basic or standard editions. But if you remove all InMemory OLTP objects, then we can downgrade.
  • Performance tier downgrade within premium is blocked for databases with InMemory OLTP objects.
  • A database with InMemory OLTP objects cannot be downgraded to a Premium performance tier with a smaller memory size

I am sure I might be missing something, but many a times the Azure SQL database restrictions are time bound and I am sure many be 6 months or 1 year down the lane, some of these might go away so always refer to the documentation for the latest restrictions (if any).

I am curious to understand how many of you are Azure SQL database users? What have you been using it for? What are some of the enhancements on Azure SQL DB’s that you have got excited about? Let me know via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL AZURE – How to Disable and Enable All Constraint for Table and Database

Recently I wrote blog to enable and disable all constraints in the database SQL SERVER – How to Disable and Enable All Constraint for Table and Database.

One of my reader told me that this trick of sp_msforeachtable doesn’t work in Azure SQL Database (WASD). It is interesting that a lot of things that we assume when working with on-premise SQL Server are not completely available when working on SQL Azure because it is Database as a service. Though some of the system commands are not exposed, we can always write something using the Dynamic Management Views (DMVs) something similar to achieve the same effect.

Here is the modified version of script which can be used on SQL Azure database. You can check the same on your servers and let me know.

-- Get ALTER TABLE Script for all tables to Disable all constraints for database
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] WITH CHECK CHECK CONSTRAINT [' + i.name + ']'
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GO
-- Get ALTER TABLE Script for all tables to Enable all constraints for database
SELECT 'ALTER TABLE [' + s.name + '].[' + o.name + '] NOCHECK CONSTRAINT all'
FROM sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

Note that Executing above would NOT make any change. By executing above script, there would be an output in Management Studio. This output is a set of commands that you will need to explicitly run it on the servers post that.

Let me know if you ever required such a capability when working with SQL Azure and what have you been doing to achieve the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Finding Top Offenders in SQL Server 2012 – Notes from the Field #068

[Note from Pinal]: This is a 68th episode of Notes from the Fields series. Performance tuning gets easier with SQL Server 2012. For the first time, you can capture a workload and find your top offenders inside of Management Studio without having to write a single line of T-SQL or use any third party tools.

JohnSterrett SQL SERVER   Finding Top Offenders in SQL Server 2012   Notes from the Field #068

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how to find top offenders in SQL Server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the biggest mistakes with performance tuning is not knowing where to start.  Sometimes performance tuning can be like finding a needle in a haystack.  This is because you might not have a great process to help you find your top offenders. In this short five minute video below you will be able to utilize extended events to aggregate and group completed statements so you can find your top offenders without having to write any T-SQL code.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Proof of Concepts with Windows Azure – Notes from the Field #056

[Note from Pinal]: This is a 56th episode of Notes from the Fields series. If you are DBA and Developer there is always a situation when you want to prove yourself by building a small proof of concepts of your idea. However, most of the time, it is way more complicated than we think. Building proof of the concepts required many different resources and skills. Above all there are chances that what we have built is not upto the mark and we have to rebuild the example one more time. Trust me it is never simple those tasks which looks simple from the beginning.

JohnSterrett SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how to build proof of concepts and how to maximize the power of Azure. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


Whether you know it or not cloud services are here and they are changing the way we will provide information technology services. For example, in many information technology shops it can take weeks if not months to get an instance of SQL Server up and running.  Here are some minimal action items that must be completed before DBA’s get access server to install SQL Server.  You have to order a physical server, your procurement team must approve the order, and the server has to be shipped. Once the server is received the server must be racked in the data center, cables must be connected, and the data center team needs to document their changes. Then the operations team needs to install and configure windows. I could keep going but there are a lot of things that should be done to a server before the DBA team gets its hands on it. What are you going to do if you’re a DBA and you need instance up in 30 minutes for a proof of concept? It’s becoming more common that the cloud is the answer.

Every time I need a server for a proof of concept I jump to Windows Azure.  I can quickly build a Windows Azure Machine with SQL Server provided within 30 minutes.  In this tip, I am going to walk through the steps to create your first Windows Azure Machine.

1. Get Azure Windows Account. If you don’t have one you can get a free trial. At the time of writing this tip your free trial would include $220 of credit.
http://azure.microsoft.com/en-us/pricing/free-trial/

Are you an MSDN Subscriber who isn’t leveraging Microsoft’s cloud service? If so, you can sign up for free monthly credit.

2. Log In to Azure Portal
https://manage.windowsazure.com

3. Create New Virtual Machine

On the left hand side, click on Virtual Machines and then the add button on the bottom of the left side of the screen. This will load our wizard for creating our first virtual machine.

notes56 1 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056

Now that wizard is loaded as you can see below we can select virtual machine and create it from the gallery. In the Gallery we will be able to select one of many images used that already includes SQL Server baked in.

notes56 2 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056

Looking at the SQL Server images you will see you can access Enterprise Edition, Standard Edition and Web Edition for SQL 2014 down to SQL 2008 R2.notes56 3 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056

 

Next you can customize your image by release date. This will allow you to have different service packs or CUs. You can also select between two different tiers and sizes.  You will have to create a user name and password and you will want to keep this credential as it will be your first account.

notes56 4 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056 

Next you will be able to select more machine configuration options. You will get to determine where the Azure Virtual Machine is located.  Below you will see I am using my MSDN Subscription.

notes56 5 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056 

Finally, you will get to configure more configuration extensions to help automate or secure your virtual machine.

notes56 6 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056

Finally, you will see your server being provisioned. What once use to take weeks or months can now be done in the cloud in minutes.

notes56 7 SQL SERVER   Proof of Concepts with Windows Azure   Notes from the Field #056

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Question on SSMS Prompt about Connect to Server

Today is Sunday, so letting us have some fun thing to discuss and think about. If you have not read my earlier blog post SQL SERVER – Connecting to Azure Storage with SSMS. I have discussed about how to connect to the SQL Azure storage via SSMS. Here is a follow up question I have on the same subject.

Question: When we connect to object explorer via the main toolbar menu, we get a prompt where we can connect to Azure Storage but when we open first object explorer why do we see the option to Azure Storage.

To better explain my question, please see the image attached herewith.

connectobjectexplorer SQL SERVER   A Question on SSMS Prompt about Connect to Server

Do you have any answer for the same? If yes, please share.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Connecting to Azure Storage with SSMS

SQL Server Management Studio can now connect to Azure Storage.

It is very simple to connect to Azure Storage. First open the Object Explorer in SQL Server Management Studio (SSMS). Now click on Connect. Upon clicking on the connect it will bring up five options as displayed in the image below.

azureconnect1 SQL SERVER   Connecting to Azure Storage with SSMS

Click on “Azure Storage” and it will bring up another screen asking for storage account and account key.

azureconnect2 SQL SERVER   Connecting to Azure Storage with SSMS

Now go to your Azure account and get the name of the storage account, and account Key.

azureconnect3 SQL SERVER   Connecting to Azure Storage with SSMS

It will bring up following screen.

azureconnect4 SQL SERVER   Connecting to Azure Storage with SSMS

Enter the name of the account and account key at the prompt in SSMS.

azureconnect5 SQL SERVER   Connecting to Azure Storage with SSMS

Once you enter the details, it will immediately connect you with your Azure Storage. Next, expand the node and you will notice your containers.

azureconnect6 SQL SERVER   Connecting to Azure Storage with SSMS

The same containers will be visible in your Azure account online.

azureconnect7 SQL SERVER   Connecting to Azure Storage with SSMS

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio

Recently, while working on a demo about backup, I realized that in management studio, now there is a new option in the backup screen called a URL.  This feature is called as “backup to URL”.

I checked earlier version of SSMS and found that this was not there in the SQL 2012 management studio. There the options were “disk” and “tape” in SSMS 2012.

azurebackups1 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

As soon as the URL is selected, it would ask for various options about backup destination in Azure.

azurebackups2 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Let’s go step by step and take a database backup to Microsoft Azure Blob storage.

To use this feature, you need to have is a Microsoft Azure account. If you do not have a Windows Azure account, visit Windows Azure 3-Month free trial. After logging it to the portal, I have created a “storage” called sqlauthority.

azurebackups3 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Under sqlauthority, I have created a container called “backup”.  This container was created as “Public Blob”.

azurebackups4 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

If we closely look at URL it is http://<StorageAccount>.blob.core.windows.net/<ContainerName> Going back to SSMS, this is what we have to provide. But wait, how would SQL Server connect to Azure Blob storage? Well, that’s where the credential comes into the picture. Note that authentication to the storage account is required for SQL Server backup and restore even if we choose to create a public container. There are multiple ways to connect to the storage – publishing profile, certificate or SQL Credential. To use SQL credential, we need to get the secret key from the portal using below the screen. The screen would appear once we click on “Manage Access Key” in the third image of this blog.

azurebackups5 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Once we have the secret key, we can go to SSMS and right click on “Credential” under “Logins” to choose “New Credential”.

azurebackups6 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Once this is done, we are all set. Go back to UI and choose “backup-cred” in the drop down. Here is the final screen.

azurebackups7 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Once the backup is complete, we should see the backup on the portal. Notice URL http://sqlauthority.blob.core.windows.net/backup/Sales_backup_2014_06_04_101547.bak

azurebackups8 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Once the backup is available, we can also restore it. We can use management studio as well as T-SQL to restore the backup taken on the URL. While choosing the device, we need to use “URL” option, browse to storage container and choose correct file.

azurebackups9 SQL SERVER   Backup to Azure Blob using SQL Server 2014 Management Studio

Go ahead and play with this feature.Let me know what you think of the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Microsoft Whitepaper – SQL Server 2014 and Windows Azure Blob Storage Service: Better Together

azureblog SQL Authority News   Microsoft Whitepaper   SQL Server 2014 and Windows Azure Blob Storage Service: Better TogetherMicrosoft has released SQL Server 2014 earlier this month and now have released very long but interesting white paper on Windows Azure Blog Storage Services. SQL Server Data Files in Windows Azure feature is available in all editions of SQL Server 2014. This feature is enabled by default and at free of cost. This feature makes SQL Server 2014 hybrid cloud database as it provides native support for database files stored as Windows Azure Blobs. If you notice this is a big leap forward where data stored locally as well as on a cloud are accessed by SQL Server 2014 at the same time. You can also host SQL Server Instance in Azure cloud and it can also use the Windows Azure Blobs just like on-premises SQL Server.

Let me give you another example, if you have a situation that you have stored your database files in the cloud as well as backup on Windows Azure Cloud. Now if you want to restore your backup you do not have to bring that back to on-premises before restoring it. You can just restore your data directly from Windows Azure Storage to your SQL Server 2014 instance in a Virtual Machine. It will remove the necessity to do lots of data movement between cloud and on-premises servers.

This white paper discusses exactly the same feature very much in detailed. This white paper has over 116 pages and the size of the word file is 2.5 MB. However, after a long time I have found a Whitepaper which explains concepts from the beginning and walks users step by step with the examples. The appendix file to this Whitepaper contains all the necessary code as well as very interesting reference material. I think I am going to spend my entire weekend reading and learning from this Whitepaper.

Image included in this blog post is courtesy to the same white paper and it explains the story of entire white paper in just one image. Brilliant.

Download the white paper SQL Server 2014 and Windows Azure Blob Storage Service: Better Together.

Reference: Pinal Dave (http://blog.SQLAuthority.com)