Downloaded 12 times
![Demo - Row Level Security CREATE TABLE Protegido (ID int IDENTITY PRIMARY KEY, Nombre varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, Apellido varchar(100) NOT NULL, NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL, UserID int ); select * from sys.database_principals INSERT Protegido (Nombre, Apellido, NrTlf, Email, UserId) VALUES ('Roberto', 'Torres', '91551234567', 'RTorres@contoso.com', 5), ('Juan', 'Galvin', '95551234568', 'JGalvin@contoso.com', 5), ('José', 'Garcia', '95551234569', 'Jgarcia@contoso.net',1); CREATE FUNCTION SecPred(@userId int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 as valor WHERE @userId = user_id() CREATE SECURITY POLICY [secpol] ADD FILTER PREDICATE [dbo].[SecPred]([UserId]) on [dbo].[Protegido] GRANT SELECT ON protegido TO Peter; GRANT SELECT ON protegido TO Ken; EXECUTE AS USER = 'Kent'; SELECT * FROM protegido; REVERT; EXECUTE AS USER = 'Peter'; SELECT * FROM protegido; REVERT;](https://image.slidesharecdn.com/jotechies-azuresqldb-170514121213/75/JoTechies-Azure-SQL-DB-35-2048.jpg)
Uploaded byJoTechies
JoTechies - Azure SQL DB
The document provides an extensive overview of Azure SQL Database, covering its various types and features such as DTU performance metrics, elastic databases, and migration processes. It also highlights security measures, high availability, and disaster recovery options, detailing how to manage and monitor databases effectively. Additionally, it includes practical examples and tools for troubleshooting, as well as SQL auditing and threat detection strategies.
JoTechies - Azure SQL DB
- 1.
- 2.
- 3. Jo Techies JoTechies isa Tech community that provides education, help and social events for IT professional in Jordan and helps you network with local IT peers. @JoTechies JoTechies www.JoTechies.com
- 4. Understanding AzureSQL What is it Five Goals Database Levels/DTUs Migration Process Performance and tuning Secure and protect Data Geo-Replication and Geo-Restore
- 5. Low Control Shared Lowercost Dedicated Highercost High Control HybridCloud 100% Compatibility Rapid self-service provisioning SQL Server in WA VM - IaaS Virtualized Machine SQL Server Physical Machine (raw iron) Full h/w control Roll-your-own HA/DR/scale Managed database service Focus on business logic Virtualized Database WA SQL Database - PaaS Elastic/Self-Service capabilities Full h/w control SQL Server PrivateVirtualized Machine SQL Server on Azure - IaaS
- 6. SQL Serveras Service Service managed by Microsoft Almost 0 maintenance Resources oriented to use your database Based on DTU (Data Throughput Unit) (Basic, Standard, Premium, Elastic DB Pool, ADW)
- 7. → Each databasein Azure SQL Database is associated with a logical server. The database can be: •A single database with its own set of resources (DTUs) •Part of an elastic pool that shares a set of resources (eDTUs)
- 8. DTU is definedby the bounding box for the resources required by a database workload and measures power across performance levels.. % CPU % read % write % memory Across Basic, Standard, and Premium, Premium RS, each performance level is assigned a defined level of throughput Introducing the Database Throughput Unit (DTU) which represents database power and replaces hardware specs Redefined Measure of power
- 9. Single Database ElasticPool Database Basic — 5DTU S0 — 10 DTU S1 — 20 DTU S2 — 50 DTU S3 — 100 DTU P1 — 125 DTU P2 — 250 DTU P4 — 500 DTU P6 — 1,000 DTU P11 — 1,750 DTU P15 – 4,000 DTU IO-intensive workloads that do not require the highest availability guarantees PRS1 — 125 DTU PRS2 — 250 DTU PRS4 — 500 DTU PRS6 — 1000 DTU
- 10. Area Limit Description Databasesper server Up to 5000 Up to 5000 databases are allowed per server on V12 servers. DTUs per server 45000 45000 DTUs are allowed per server on V12 servers for provisioning standalone databases and elastic pools. The total number of standalone databases and pools allowed per server is limited only by the number of server DTUs. Other SQL Database limits
- 11. Tools to manage& develop with Azure SQL Database
- 16. Elastic Databases Provisioning resourcesfor the entire pool rather than for single databases simplifies your management tasks. Plus you have a predictable budget for the pool. eDTUS consumption Individual databases are given the flexibility to auto- scale within set parameters. Under heavy load a database can consume more eDTUs to meet demand. Databases under light loads consume less, and databases under no load don’t consume any eDTUs. 16
- 17.
- 18. Workers & requestslimits equivalent to single DBs DTU % CPU % read % write % memory Elastic Databases
- 21. Memory impact on performance - I/O Incase of problem, scale up •Online Process •Found the issue with QDS. Check your Database •Metrics •Alerts •Monitoring tools Migration Process •Transactional Replication without downtime. •SQL Azure Migration Wizard Choose the Model •Single/Elastic •Performance counters. •Reviewed instance configuration. •Azure SQL database calculator tool. http://dtucalculator.azurewebsites.net/
- 22. • Azure SQLDB as a subscriber of SQL Server Transactional Replication. • Two main scenarios: 1. Migrate your data to Azure SQL DB with no downtime. 2. Bridge SQL Server on-premises/on VMs to Azure SQL DB.
- 24. for archiving orfor moving to another platform. database schema and data to a BACPAC file. BACPAC file can be stored in Azure blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation no write activity is occurring during the export. exporting from a transactionally consistent copy of your Azure SQL database.
- 25. SQLPackage utility SQL ServerManagement Studio PowerShell
- 26. a snapshot ofthe source database as of the time of the copy request. same server or a different server. different performance level within the same service tier (edition). fully functional, independent database. can upgrade or downgrade it to any edition logins, users, and permissions can be managed independently. Azure portal, Transact-SQL,Powershell Copy Azure SQL DB
- 28. Long Term Retention –10 years of backup High availability Disaster Recovery 3 copies of the data Geo-Replication (additional) Geo-Replicated Storage Point-In-Time Restore with Data Center change Recover a DB deleted. • Backup Full, Differential and Transaction Log every 5-10 minutes. * Estimated Recovery Time (ERT) - The estimated duration for the database to be fully functional after a restore/failover request. † Recovery Point Objective (RPO) - The amount of most recent data changes (time interval) the application could lose after recovery.
- 29. •The vault mustbe created in the same Azure subscription that created the SQL server and in the same geographic region and resource group. •You then configure a retention policy for any database. The policy causes the weekly full database backups be copied to the Recovery Services vault and retained for the specified retention period (up to 10 years). •You can then restore from any of these backups to a new database in any server in the subscription. The copy is performed by Azure storage from existing backups and has no performance impact on the existing database
- 30. GEO-Replication 30 • Recovery froman outage • Standard Geo-replicación • Geo-restore • Configure geo-replication for Azure SQL Database with the Azure Portal
- 31. Security Approach inSQL Server Sessions Active Directory Security Azure Active Directory Security Firewall Encryption Transparent Data Encryption Always Encrypted TLS (connection string) Data protection Row-level security Dynamic data masking Analysis SQL Auditing Threat Detection C2 common SQL Server Audit Azure SQL PaaS SQL Server IaaS/On-Premise All implementations
- 32. Security Approach –Control Database Access Port: 1433 Protocol: TCP Encrypted Proxy: • Protect connection • Check firewall rules Authentication Method. ADO .NET 4.6 ADALSQL AAD
- 33. Security Approach –Control Application Access Dynamic Data Masking Row-Level Security Centralize your row access logic within the database. Limit the exposure of sensitive data by obfuscating query results for app users and engineer
- 34. Demo - DynamicData Masking CREATE USER Peter FOR LOGIN Peter; GRANT SELECT ON Contacto TO Peter; CREATE TABLE Contacto (ID int IDENTITY PRIMARY KEY, Nombre varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, Apellido varchar(100) NOT NULL, NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL); INSERT Contacto (Nombre, Apellido, NrTlf, Email) VALUES ('Roberto', 'Torres', '91551234567', 'RTorres@contoso.com'), ('Juan', 'Galvin', '95551234568', 'JGalvin@contoso.com'), ('José', 'Garcia', '95551234569', 'Jgarcia@contoso.net'); EXECUTE AS USER = 'Peter'; SELECT * FROM Contacto; REVERT;
- 35. Demo - RowLevel Security CREATE TABLE Protegido (ID int IDENTITY PRIMARY KEY, Nombre varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, Apellido varchar(100) NOT NULL, NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL, UserID int ); select * from sys.database_principals INSERT Protegido (Nombre, Apellido, NrTlf, Email, UserId) VALUES ('Roberto', 'Torres', '91551234567', 'RTorres@contoso.com', 5), ('Juan', 'Galvin', '95551234568', 'JGalvin@contoso.com', 5), ('José', 'Garcia', '95551234569', 'Jgarcia@contoso.net',1); CREATE FUNCTION SecPred(@userId int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 as valor WHERE @userId = user_id() CREATE SECURITY POLICY [secpol] ADD FILTER PREDICATE [dbo].[SecPred]([UserId]) on [dbo].[Protegido] GRANT SELECT ON protegido TO Peter; GRANT SELECT ON protegido TO Ken; EXECUTE AS USER = 'Kent'; SELECT * FROM protegido; REVERT; EXECUTE AS USER = 'Peter'; SELECT * FROM protegido; REVERT;
- 36. Security Approach –Proactive monitoring SQL Auditing Threat Detection Logged Database events Detects suspicious database activities indicating possible malicious intent to access, breach or exploit data in the database Analyzing
- 37. Security Approach –How Connections work SQL Auditing servername.database.windows.net TCP, Port: 1433 P S S S servername.database.secure.windows.net TCP, Port: 1433 Connection is redirected via TDS protocol TDS <3.2 – JDBC SQL Auditing Enabled
- 38. Security Approach -SQL Auditing Audit Records Example
- 39. Security Approach -SQL Auditing Setup
- 40. Security Approach -SQL Auditing Data Captured Visualization 40
- 41. Security Approach -SQL Auditing Threat Detection Example Threat Detection detects anomalous database activities indicating potential security threats to the database.
- 42. Troubleshooting Steps Always • Investigate Update Statistics. Then •Investigate Missing Indexes. Consider • Reducing Fragmentatio n levels. Queries • Right Workload for DTU Level? Tools • QDS • Extende d Events • QPI. Engage Microsoft • We can help! TroubleshootingSteps
- 43. Query Data Store It is a persisted database with query execution information for SQL Server 2016 Azure SQL DB V12 Query Performance Tuning and Troubleshooting SQL Profiler replacement in some parts. Minimum impact for SQL Engine in SQL Server or Azure SQL DB. Other tools like Query Performance Insight, Index Advisor are using QDS Information. SSMS Reports Supported for SQL Server and Azure SQL Database. Catalog Views all available. Active or disable this feature demand. Use Extended Events to capture the information.
- 44. Alerts and events Define rule Specify threshold Threshold violated Alertrule active Registers an alert Send E- mail (optional)
- 45. Alert Rules Blocked by Firewall Failed Connections Successful Connections CPU Percentage Deadlocks DTU Percentage LogIO Percentage Data IO Percentage Total Database Size
- 46.
- 47.
Editor's Notes
- #7 Simplicity where nothing changes in the perspective of the programmer to manage the data. We can climb simply and simply. Safety in different models and capacities We have asguerado the business continued. And very important is that we will always be updated to the latest version of SQL.
- #9 DTU (Database throughput unit ) : is a unit of measure of the resources that are guaranteed to be available to a single Azure SQL database at a specific performance level within a single database service tier. A DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. EDTU: unit of measure of the set of resources (DTUs) that can be shared between a set of databases on an Azure SQL server - called an elastic pool. DTU Calculator http://dtucalculator.azurewebsites.net/
- #11 Resources Governance : One of the design goals of the Basic, Standard, and Premium service tiers is for Azure SQL Database to behave as if the database is running on its own machine, isolated from other databases. Resource governance emulates this behavior. If the aggregated resource utilization reaches the maximum available CPU, Memory, Log I/O, and Data I/O resources assigned to the database, resource governance queues queries in execution and assign resources to the queued queries as they free up. Enforcement of limits : Resources other than CPU, Memory, Log I/O, and Data I/O are enforced by denying new requests when limits are reached. When a database reaches the configured maximum size limit, inserts and updates that increase data size fail, while selects and deletes continue to work. Clients receive an error message depending on the limit that has been reached.
- #21 Create Azure SQL DB on the Portal
- #22 SQL Server 2005 or later database to Azure SQL Database. database is compatible with Azure SQL Database using the Data Migration Assistant (DMA) “https://www.microsoft.com/en-us/download/details.aspx?id=53595”
- #24 Create Azure VM Create DB from adventureworks folders Install data migration Assistant to check compatibility Assess the database for compatibility using the latest version of Data Migration Assistant (DMA). Prepare any necessary fixes as Transact-SQL scripts. Make a transactionally consistent copy of the source database being migrated - and ensure no further changes are being made to the source database (or you can manually apply any such changes after the migration completes). There are many methods to quiesce a database, from disabling client connectivity to creating a database snapshot. Deploy the Transact-SQL scripts to apply the fixes to the database copy. Export the database copy to a .BACPAC file on a local drive. Import the .BACPAC file as a new Azure SQL database using any of several BACPAC import tools, with SQLPackage.exe being the recommended tool for best performance. -----------------------------
- #26 SQL Package utility : SqlPackage command-line utility, Where is SQLPackage located at?. C:\Program Files (x86)\Microsoft SQL Server\<SQLSERVERVersionInstalled>\DAC\bin> Command line to import the data using SQLPackage. sqlpackage.exe /a:import /sf:filename.bacpac /tdn:MyDatabaseName /tsn:myserverlocal|myAzureServer.database.windows.net /tp:MyPassword/tu:MyUser Command line to export the data using SQLPackage. sqlpackage.exe /a:export /tf:filename.bacpac /sdn:MyDatabaseName /ssn:myserverlocal|myAzureServer.database.windows.net /sp:MyPassword! /su:MyUser
- #28 Copy , Export
- #29 RPO: Recovery Point Objective is the amount of data you can afford to lose if a server had a failure. For example, if you back up your server once a night your RPO could be 24 Hours if you replicate your server real time your RPO could be seconds. RTO: Recovery Time Objective is the time that it could take to get your systems back up and running after a failure.
- #30 Enable LTR ; Create an Azure Recovery Services vault in the same region, subscription, and resource group as your SQL Database server. Register the server to the vault Create an Azure Recovery Services Protection Policy Apply the protection policy to the databases that require long-term backup retention LTR :currently in preview and available in the following regions: Australia East, Australia Southeast, Brazil South, Central US, East Asia, East US, East US 2, India Central, India South, Japan East, Japan West, North Central US, North Europe, South Central US, Southeast Asia, West Europe, and West US. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-long-term-retention ----------------------------------------------
- #31 Standard Geo Replication: With Standard Geo Replication there is a non-readable replica of the primary database. This is useful if a disaster happens on the primary database. However, this database cannot be used for load balancing. Active Geo Replication: Active Geo Replication provides a readable copy of the primary database so the secondary database can be used for reporting purposes or to distribute the application workload where the only requirement is to read the data. We can have multiple online secondary databases.
- #32 TLS: https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx y https://blogs.msdn.microsoft.com/sqlserverfaq/2012/04/04/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire/
- #33 TLS: https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx y https://blogs.msdn.microsoft.com/sqlserverfaq/2012/04/04/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire/
- #34 TLS: https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx y https://blogs.msdn.microsoft.com/sqlserverfaq/2012/04/04/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire/
- #35 TLS: https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx y https://blogs.msdn.microsoft.com/sqlserverfaq/2012/04/04/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire/
- #36 TLS: https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx y https://blogs.msdn.microsoft.com/sqlserverfaq/2012/04/04/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire/
- #37 TLS: https://technet.microsoft.com/en-us/library/cc784450(WS.10).aspx y https://blogs.msdn.microsoft.com/sqlserverfaq/2012/04/04/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire/