Limitations in Stretch DB in SQL Server 2016 CTP 3
As per Microsoft article, we have many limitations that we need to understand before start enabling the stretch database in your environment.
I have discussed few limitations here and also what will happen when we override those limitations.
Limitations
1. We cannot perform UPDATE and DELETE operations on stretch enabled tables
2. We cannot create a index for a view that includes the stretch tables. “New Index” option is disabled.
3. We cannot perform update or delete a view that includes stretch enabled tables however insert is possible.
[Step by Step] Create Database from .bacpac File
We discussed before about how to export the database from azure and stored the bacpac file in azure storage container.
This article about how to create a database in on-premises SQL Server using bacpac file.
Step 1: Download the bacpac file from azure storage container
Step 2: Use data tier application to import the bacpac file in SQL Server
Step 3: Follow the wizard instructions. You can able to create database using your azure storage container as well.
Now, you have successfully created a database using bacpac file.
SAS Token for Azure Storage
Creating a Shared Access Signature for your azure storage is bit complex. Microsoft suggesting to use console application to generate the SAS (article here).
If you are not familiar with ADO.Net application then very difficult to get the SAS key.
To make it easy, Azure Storage Explorer providing the GUI based SAS generator.
Download the Azure Storage Explorer, here.
in storage explorer, add your azure storage account by using “Add Account” option.
Once you have configured then select your container and choose “Security”.
Select the options as per your wish and generate the signature.
[Step by Step] Export Azure SQL Database
Follow the below steps to export the database. This database can used again in azure SQL database with different server also in VM SQL Server or on-premise SQL Server.
Step 1 : As usual log in to your portal.azure.com
Step 2: Select your database which you want to export.
Step 3: choose export option.
Step 4: Provide the Azure storage container where you want to store the backup file.
Step 5: Select OK. Finished. You have successfully back up your database.
Step 6: Verify the back up in your storage
Note: The extension of the back up file is not .bak as a typical format. You cannot restore this file in your SQL Server Management studio just like .bak restore.
Named SQL Server in Azure
There is no option to create a your own named server in Azure management portal.
While creating a SQL database, azure will create a SQL Server with random name. This is not a good practice in real time environment as we are maintaining the proper naming conventions.
We can create a our own named SQL Server in Portal.azure.com
Create a new database and select the “Create a new Server” option to create your own named server.
Steps to Upgrade Azure SQL Server
Azure has released a new version of SQL Server V12.
If you are using V2 SQL Server then you can upgrade to V12. This upgrade option is available in Preview Portal.
Follow the below steps to upgrade your SQL Server
Step 1: Visit Preview Portal and open your SQL Server then select server version.
Step 2: select the “Upgrade this server” in right hand side window of your SQL Server
Step 3: New window will ask you to enter your SQL Server name
Step 4: Enter your SQL Server name and select “OK”. Upgrade process will be initiated.
Wait for sometime and your SQL Server will be upgraded to newer version V12.
To compare the feature with older and newer version, visit my previous article.
Azure SQL Server
Azure has released a new version of SQL Server which is named as V12. The previous version of SQL Server is V2.
If we create a SQL database using Quick create method in manage portal then there is no option to choose the latest version V12 server. This is possible when you select a custom create option.
Steps to choose latest version,
1. Choose the custom create option and fill the required details and select new server.
2. Select the check box to enable the latest version.
To know about the latest features in V12, visit here.
Check the preview portal where we can see the difference in features between V2 and V12.
Features in V2 & V12
SQL Server Management Studio Firewall Settings
We need to follow the below steps to connect the azure based SQL Server in SSMS
1. Add the client IP address in azure firewall
2. Use the azure SQL Server name with proper credentials.
The above steps should be carried out until SQL Server 2016 to access the SQL databases in SSMS.
If IP is not configured then SSMS will not allow to connect, it will through the below error message.
SQL Server 2016 CTP 3.3 has built in feature to add the IP in azure SQL Server firewall from SSMS itself.
Follow the below steps,
1. There is no IP configured in Azure SQL Server Firewall
2. Enter the server name and credentials and click connect. It will open a Azure account sign in window
3. Server will be connected and can access the databases. Client IP address added into firewall.
