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.