Yet another SQL Server enthusiast

2017-12-28

Azure SQL DB – Database backups

Filed under: Uncategorized — sa @ 2:59 AM

Azure SQL DB automatically creates full, differential and log backups.

First full database backup is created after database creation and all other backups are scheduled weekly. Generally speaking, differential backups are created every few hours and transaction log backups are created every 5 – 10 minutes. The exact schedule for database backups is automatically computed based on system workload.

The retention period for database backups depends on service tier thus: 7 days for Basic or 35 days for Standard/Premium tier.

Advertisements

2017-12-27

Azure SQL DB – Firewall settings

Filed under: Uncategorized — sa @ 2:39 AM

Azure SQL DB allows us to configure firewall settings at server level and/or at database level by using following two stored procedures:

  1. sp_set_server_firewall_rule
  2. sp_set_database_firewall_rule
-- Create database-level firewall setting for your public IP address
EXECUTE sp_set_database_firewall_rule @name = N'<rule name>',@start_ip_address = '<start ip>', @end_ip_address = '<end ip>';

2017-12-22

Azure SQL DB – Monitoring

Filed under: Uncategorized — sa @ 8:01 AM

In order to monitor the current workload we could use the following SQL DMV views and functions:

  • sys.dm_exec_sessions
  • sys.dm_exec_requests
  • sys.dm_exec_connections
  • sys.databases
  • sys.dm_exec_sql_text

Example:

SELECT s.session_id, s.last_request_start_time, s.host_name, s.program_name, db.[name] AS dbname, OBJECT_NAME(t.objectid, t.dbid) AS objname, t.[text], r.open_transaction_count, r.wait_type, r.last_wait_type, r.wait_resource
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
LEFT JOIN sys.databases db ON t.dbid = db.database_id
ORDER BY s.last_request_start_time DESC

Azure.SQL.DB.Monitor

Also, we could use Azure Portal > SQL databases > AdventureWorks > Performance overview:

Azure.SQL.DB.Monitor.Performance.Overview

Note: blog post available @ 2017-12-22 7:50 EET

 

2017-12-21

Azure SQL DB – Check resource consumption #2

Filed under: Uncategorized — sa @ 2:00 PM

In order to check the consumption of resources (CPU, I/O, storage) at server level we could use sys.resource_stats 

resource.consumption2

Performance data is collected every 5 minutes and the retention period is 10 days.

Azure SQL DB – Check resource consumption

Filed under: Uncategorized — sa @ 7:59 AM

In order to check the consumption of resources (CPU, I/O, memory) at database level we could use sys.dm_db_resource_stats 

resource.consumption

Performance data is collected every 15 seconds and the retention period is 1 hour.

2017-12-20

Microsoft Professional Program for Data Science track – A short description

Filed under: Uncategorized — sa @ 11:00 PM

(a) Starts on January 1, 2018

(b) It includes 10 [mandatory] courses as follow:

  1. Microsoft Professional Orientation : Data Science
  2. Querying Data with Transact-SQL
  3. Analyzing and Visualizing Data: with Excel or with Power BI
  4. Essential Statistics for Data Analysis using Excel
  5. Introduction to R / Python for Data Science
  6. Data Science Essentials
  7. Principles of Machine Learning
  8. Programming with R / Python for Data Science
  9. Applied Machine Learning / Implementing Predictive Solutions with Spark in HDInsight / Analyzing Big Data with Microsoft R Server
  10. Final project Microsoft Professional Capstone : Data Science

(c) Enrollment

Azure SQL DB – Elastic pools

Filed under: Uncategorized — sa @ 1:57 PM

Problem

Working with Azure SQL DBs requires to allocate resources for databases. The unit measure for resources is DTU (link) and it takes into account: CPU, memory, IO reads and IO writes. Let’s assume that we have to manage 3 Azure SQL DBs having following service tiers associated (link with all STs):
DB1 – S1 (20 DTU)
DB2 – S1 (20 DTU)
DB3 – S2 (50 DTU)
Scenario: we assume that these database are used as follow:
⦁ In the morning (4 hours): DB1/DB2 need 15 x 2 DTU, DB3 needs 2 DTU
⦁ Afternoon (4 hours): DB1/DB3 need 2 x 2 DTU while DB3 needs 45 DTU
If the resources of these databases are manage separately then we have to provision 90 DTU x 8 hours.
The question that raises from this scenario is: how we can manage the scalability of these databases in a costly effective manner ?

Solution

The answer is to group these databases into a elastic pool which does allow resource sharing. In this case, we could create a single elastic pool with 50 e[elastic]DTU (service tier S1) and we could include these three databases in this elastic pool. In this case:
⦁ In the morning (4 hours): DB1/DB2 need 15 x 2 eDTU + DB3 needs 2 eDTU = 32 eDTU (resources needed for all databases included into elastic pool)
⦁ Afternoon (4 hours): DB1/DB3 need 2 x 2 eDTU + DB3 needs 45 eDTU = 49 eDTU (resources needed for all databases included into elastic pool)
The conclusion of this section is that instead of provisiong 90 DTU x 8 hours ($0.0404/hour x 8 hours + 0.0404/hour x 8 hours + $0.1009/hour x 8 hours = $1.4536), because of elastic pools we have to provision only 50 eDTU x 8 hours ($0.1511/hour x 8 = $1.2088). In terms of money, maybe the difference is not that big for one day but we could extrapolate this computation to one month/year, to many databases, to superior service tiers and the difference will be higher.

How

SQL elastic pools > Add

Azure.SQLDB.Elastic.Pool.Add

 

Azure SQL DB – DTU / eDTU

Filed under: Uncategorized — sa @ 7:59 AM

DTU (or Database Transaction Unit) represents the level of resources allocated for a database (Azure SQL DB) and it depends of following elements:

  • IO Reads / Writes
  • Memory
  • CPU

DTU applies to a single DB while eDTU applies to a elastic pool (many) of databases. In this scenario the unit used to measure the resources consumption is eDTU (elastic DTU) and it’s computed as an aggregation of resources of resources of all databases from current elastic pool.

2017-12-19

Azure SQL Datawarehouse vs Azure SQL Database – The main difference

Filed under: Uncategorized — sa @ 12:59 PM

The main difference between SQL DB and SQL DW is [from my point of view] how queries are executed:

  1. SQL DB is a SMP solution, meaning that is using only one computing node to execute queries while
  2. SQL DW is a MMP solution meaning that it would use one, two or more computing nodes.

In order to check number of computing nodes (or SERVICE OBJECTIVE) for SQL DW we can use following query:

AzureSQLDW

In this case, service object DW100 means that current SQL DW has only one computing node.

In order to change it to 10 nodes we have to use:

ALTER DATABASE MySQLDW
MODIFY (SERVICE_OBJECTIVE = 'DW1000')

Here you may find differences between these two databases.

2017-12-17

Connecting to Azure SQL Database #learning

Filed under: Uncategorized — sa @ 2:11 PM

Problem

If we want to connect to a Azure SQL Database by using a app (ex. SQL Server Management Studio) we may get following exception:

Azure.SQL.Database.01.Connection.Exception

Cause

According to message associated with this exception, the cause of this issue is related to configuration of server-level (see also sp_set_firewall_rule) or database-level (see also sp_set_database_firewall_rule) firewall rules used to allow the access to SQL DB.

Solutions

[1] First solution implies to configure the server-level/database-level firewall in order to allow connections from a specified IP of IP range: Select Azure SQL DB > Overview > Set server firewall

Azure.SQL.Database.02.Firewall.Configuration

Then Add client IP > Save:

 

Azure.SQL.Database.03.Firewall.Configuration.Client.IP

[2] Second solution is to allow connections to Azure SQL DB by activating Allow access to Azure services.

[3] If none of above solutions work for you then all details regarding FW configuration is available here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure .

Next Page »

Blog at WordPress.com.