MS Azure : Restore of Azure Database

Backup of Azure Database

In Microsoft Azure Database, Restores can be done to the logical server hosting the database or to another region.
Local Restore

1. A new database on the same logical server recovered to specified point in time.
2. A database on the same logical server recovered to the deletion time.
3. you cannot overwrite an existing database.

Note : If you want to replace a database with a restored copy, you can rename the orignal and then rename the restored database.

Geo Restore

1. A new database on any server on any region up to the latest geo-replicated backup.
2. Full and differentials are geo-replicated.

Note : Geo-Restores have an 1hour RPO due to the possible delay in geo-replication.

Restore Demo
Lets restore database with name t1_portalRestore, here are the steps:
1. Click on the t1 database, then click on Restore button and provide the Restore point(UTC):

2. In notifications, it will show the progress:

3. Once its done, here it will show:

Thats it!!

MS Azure : Backup of Azure Database

Backup of Azure Database

In Microsoft Azure Database, Backup happen automatically as part of the service, but the retention period of the Backup is as per the selected DB Service tier.
Retention Period of MS Azure Database

Backup Schedule of MS Azure Database

Important Points:
1. In Microsoft Azure, Backups are Geo-Replicated so that:-
a. Backups are replicated to a paired Azure region by the Service.
b. In the event of the region outage, you can restore your backup to another region.

2. All databases support point-in-time recovery with a 12 hour RTO and 5 minute RPO for the in-region backups.

3. Azure provides 2x your max storage size as included backup storage. For E.g, a Standard database has up to 500GB of backup space included.
When reaching to backup storage limit,
a. Reduce the retention period by contacting Azure support.
b. Pay for extra backup storage billed at the standard Read-Access Geographically Redundant Storage rate.

4. For Archiving Backup files,
a. Manually export the database to BACPAC and keep copies on Azure Blob Storage.
b. Use Long-Term-Retention to Azure Backup Vault

5. After deleting Backup files,
a. If you delete a database, you can restore as long as you're still inside the retention period.
b. If you delete the server, all backups are deleted with no restore capability.

MS Azure : SQL Server Vs Azure SQL Server

Differences between SQL Server and Azure SQL Server

Following main points of SQL Server for which we don't have to take the responsibility:

Max Server Memory Settings
Max Worker Thread
Sizing data files
Setting up Tempdb
Backup and Restore Setup
Applying CU update and Patches

Basic features, Benefits and Drawbacks in comparison to SQL Server

1. The service provides a database engine
No Reporting Service, No Integration Service or No Analysis Service.
2. You can't restore a SQL Server backup directly to Azure SQL Database.
But can use import a .bacpac file, use migration tools or use SQL Server replication to restore a SQL Server database to Azure SQL database.
3. The Azure SQL Service provides its own High Availability, No Log shipping or No Database Mirroring or No Failover Cluster or No Availability Groups.
4. Linked Servers are replaced by External Table features.
5. Replication is limited to Subscriber for an Azure SQL Database.
6. There is no Error log file to open but System log viewer.
7. Database mail or Service Broker messaging are not available.
8. To track performance counter use System view that tracks the resource consumption level.
9. There's no SQL Agent Service.
10. The alerting capabilities are handled by Azure Metrics Alerts.

From Security point of view

Azure SQL Database has security feature that are new or different compared to SQL Server:
1. Transparent Data Encryption (TDE) is an Enterprise feature on SQL Server, available in all tiers on Azure SQL Database.
2. Auditing is another Enterprise feature available in all tiers of Azure SQL Database.
3. Coupled with auditing, Azure also has a threat detection service that can alert you if it detects SQL injection attempts, logins from new locations or other unusual patterns.

In Next Post, lets have a look at Azure SQL Server and compare with SQL Server.

MS Azure : Demo of Firewall and Authentication

Demo of Firewall and Authentication

To check the current firewall rules at Azure SQL Database level follow below steps:

Step 1 : Login to Azure Portal https://portal.azure.com with your credentials.

Step 2 : Once you Logged In successfully, portal will look like below snapshot.
From the left pane, you have to find out SQL Database on which you would like to check Firewall rule as highlighted with red block in below snapshot.

Once you clicked on firewall, portal will look like below snapshot:
There is No firewall rules configured.

In the same way, you can check Server level Firewall rules.

Step 3 : Now lets connect with Azure Database via SQL Server Management Studio
Connect with database via SSMS , Server Name : demo.database.windows.net Once clicked Connect

It will prompt New Firewall Rule window saying that
" Your client IP address doesn't have access to the server. Sign in to an azure account and create new firewall rule to enable access."
Click on Sign in...
Provide your access and it will connect to an Azure account.

Once Sign in, it will again prompt New Firewall Rule and give you two options:
1. Add my client IP
2. Add subnet IP range
As you can see in below snapshot: choosing to add my client IP and clicked OK

Finally we are connected to demo Azure SQL Instance through SQL Server Management Studio by adding client IP at SQL Server level.
To view the same in Azure Portal, go to SQL server and click on Firewall:
you can see the IP is added under Firewall rule.

Step 4 : Now lets create firewall rule at DB level by adding Client IP at database level.

Execute below command through SSMS to set firewall rule at database level:

use demodb
Go
EXEC sp_set_database_firewall_rule @name = N'ClientFirewallRule',
@start_ip_address = '10.xxx.xxx.115', @end_ip_address = '10.xxx.xxx.115'

Now added firewall rule at Server level and To view what all firewall rules are added at DB level, query below commands:

SELECT * FROM sys.firewall_rules

SELECT * FROM sys.database_firewall_rules

Now after adding firewall rule at database level then lets Delete firewall rule at server level to test the db level rule functionality.

Step 5 : Now lets first test the connectivity for master db by choosing database through Options from SSMS connection window.

Click on Options and change database to master as shown below

Now once you click to Connect Button, It doesn't allow to connect directly and prompt you to add New Firewall Rule for master db, as firewall rule were added only for demodb database.

Now lets finally test the connectivity for demodb:

Click Connect, it will directly connect you to demodb database.

That's it!! This is how you can keep the tight security and create firewall rule at DB and Server level.

MS Azure : Demo of SQL Database Changing Pricing Tier

Demo of SQL Azure Database Changing Tier

Today in this demo, we cover changing pricing tier and the performance by executing query in iteration through one of the application.

Demo Details and Highlights
Database Name : t1
Current Pricing Tier : Basic (5 DTUs)
Changing to Pricing Tier : Standard S1 (20 DTUs)

As you can see in below, We have one database name t1 which is set to pricing tier Basic limited to 5 DTUs.

Performance at Database t1 with Basic 5 DTUs Pricing Tier
Checkout the time taken by simple SELECT query's 4 iteration with 50 no. of threads.
Total Elapsed Time = 21.448 Seconds

Azure SQL Database Changing Pricing Tier
To change the pricing tier, we have to select/search the database (in this case i.e. t1), from the settings click on the option Pricing tier (scale DTUs) it will show all the options of pricing tier available for Single Database model to scale up or down service tier and the performance levels within the service tier.

Note : Change may cause a downtime, according to utilization of the database and also depends on the size of database.

So in this case we are changing database t1 pricing tier from Basic (5 DTUs) to Standard S1 (20 DTUs).
Once you select the desired pricing tier, it will start the process of changing tier and duration of the change is depends on the amount of data and once new selected tier is effected then only charges will change.
You can check the change in the azure portal as shown in above figure or by running below SELECT T-SQL command in SQL Server Management Studio.

SELECT
db.name [Database],
ds.edition [Edition],
ds.service_objectives [Service Objective]
FROM
sys.database_service_objectives ds
JOIN sys.databases db ON ds.database_id = db.database_id

Performance at Database t1 with Standard S1 20 DTUs Pricing Tier
Now lets again run that simple SELECT query's 4 iteration with 50 no. of threads and Checkout the time taken by the database.
Total Elapsed Time = 7.848 Seconds

Result : Just by scaling up from Pricing Tier from Basic 5 DTUs to Standard S1 20 DTUs, performance of the database has been increased from 21 to 8 Seconds.

MS Azure : Firewall and Authentication

Firewall and Authentication

Firewall

There are two ways to connecting to a database: 1. Go through the firewall 2. Authenticate yourself
Now through Firewall, there are two type of Firewall:
1. Server level Firewall: Server level firewall rules for administrator and when you have many database that have the same access requirements.
2. Database level Firewall : Use database level firewall rules to enhance security and to make your database more portable. (You can specify IP range)

Authentication Workflow

First Step where user is attempting to made a connection at Server level, If requested IP is in range as per firewall then it is authenticated by server level firewall it goes to the database level and authenticate yourself.

If IP is not in range then it is not authenticated by server level firewall then goes to next level firewall which is database level and again it checks that IP is within given firewall range of the database then it goes to authenticate yourself. If NO then request to connect with database got failed.

Authentication

There are two type of authentication:
1. SQL Authentication : SQL Authentication is the alternate to directly gets authenticate to Azure SQL Database.
- Individual accounts Authentication Only.
- Multi-Server access requires multiple users.
- No built-in password expiration.

2. Azure Active Directory : Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Database and SQL Data Warehouse by using identities in Azure Active Directory (Azure AD). With Azure AD authentication, you can centrally manage the identities of database users and other Microsoft services in one central location.
- Individual and group accounts Authentication.
- One Identity can be granted access to multiple Server.
- Password expiration follows the ADD policy.

In next post will be cover the demo of firewall and authentication module.

MS Azure : Create Elastic Pool

Steps to Create Elastic Pool

Today we will cover the demo to create Elastic Pool.
As you know Elastic pools are well suited for large no. of databases with pattern of low average utilization with relatively infrequent utilization spikes.
Fixed cost per eDTUs, eDTUs are tied to storage.

Lets understand the benefits of Elastic Pool in terms of Pricing Tier through Example:
- 16 S3 Standard Databases : 16X$150 per month = $2400, each db has static 100 DTU. Maximum 250GBX16 Storage = 4TB Storage Total.
- One Standard Pool : (800 eDTU X$2.23) per month =$1784, each db can go 10-100 DTU. 800 GB Storage.
As per above calculations, you can see it is only beneficial for no. of database which are small in size and low utilization.

Demo to Create Elastic Pool
1. Login to Azure Portal with your credentials.

2. Search your SQL Server and click on New Pool.

3. Once you clicked, it will prompt to provide following details to create Elastic Pool:
a. Name : Provide Elastic Pool name.

b. Pricing Tier : Select pricing tier, here we have selected Standard S.

c. Configure pool : Select the no. of Pool eDTUs and Storage Pool GBs both are directly proportional to each other, here we have selected:

Once you clicked on Configure pool, it will shows the below screen where we have to provide details for Elastic database pool Setting.

Both Pool eDTUs and Pool GBs both are directly proportional to each other, once you increase the value of one parameter, second will get auto increased.

Once provided all the configuration setting for elastic pool, then have to choose the database which will be the part of the elastic pool.

Select the databases, here we have select t1 , t2 database.

Now as you can see in below snapshot, t1 & t2 databases are the part of Elastic Pool (Elastic Pool-1).
Elastic Databases : Max limit 200 dbs, as of now 2 databases out of 200 dbs.
Pool Storage : Max size limit 100 GB, .004GB is in used out of 100 GB.

d. Summary & Cost : Once you provided above information, this label field will auto filled with summary and overall cost for Elastic pool.

That's it!! So conclusion is multiple databases can share resources through elastic database pools. The service also offer other elastic database capabilities.

MS Azure : Create Azure SQL Database using Single Database Model

Create Azure SQL Database

To create new Azure SQL Database follow the below steps:
Step 1 : Login to Azure Portal https://portal.azure.com with your credentials.

Step 2 : Once you Logged In successfully, portal will look like below snapshot.
From the left pane, you have to find out SQL databases as highlighted with red block in below snapshot.

Step 3 : Once you clicked on SQL databases, it will show you below screen and to create new SQL database, click on Add button as highlighted below.

Step 4 : Once you clicked Add button, it will prompt a new window to provide basic information to create new SQL database:
1. Database name : provide database name
2. Subscription : This is the drop box, Choose from the available subscription created by you.
3. Resource group : This is radio box, two options Create new or Use existing and Select the resource group.

4. Select source : This is drop down with 3 options, Choose from options 1. Blank database 2. Sample 3. Backup

5. Server : Select the server, once you clicked it will give you the option to choose from the list of available server or either create new server.

6. Pricing tier : Once you click, it will you all options to choose your pricing tier for the database.

7. Collation : Choose the collation, by default its SQL_Latin1_General_CP1_CI_AS.
Once provided all required information, click Create button. It will start the new SQL database deployment.

Step 5 : Once deployment successfully completed then click on Refresh button, it will shows the database under the list of SQL databases on the portal as shown below:

Choose the database to know the properties/settings of the SQL database, it will give you the screen as below

That's it!! Only in few minutes Azure SQL Database is ready to use.

MS Azure : Create Azure SQL Servers

Create Azure SQL Servers

To create new Azure SQL Server follow the below steps:
Step 1 : Login to Azure Portal https://portal.azure.com with your credentials.

Step 2 : Once you Logged In successfully, portal will look like below snapshot.
From the left pane, you have to find out SQL servers as highlighted with red block in below snapshot.

Step 3 : Once you clicked on SQL servers, it will show you below screen and to create new SQL database, click on Add button as highlighted below.

Step 4 : Once you clicked Add button, it will prompt a new window to provide basic information to create new SQL database:

1. Server name : Provide server name
2. Server admin login : Provide admin login name.
3. Password : Provide password
4. Confirm password : Confirm password
5. Subscription : This is the dropdown box, Choose from the available subscription created by you.
6. Resource group : This is radio box, two options Create new or Use existing and Select the resource group.

7. Location : This is dropdown box, Select the region which near to your application server.

Step 5 : Once deployment successfully completed then click on Refresh button, it will shows the database under the list of SQL databases on the portal as shown below:

Choose the Server to know the properties/settings of the SQL server, it will give you the screen as below

That's it!! Only in few minutes Azure SQL Server is ready to deploy new SQL Database.