Deploy and manage applications Marcin Szeliga
[email protected]
Deploy and migrate applications › Migration to Azure SQL Database › Migration to SQL Server in an Azure VM › Protect data › Achieve scalability
Migration to Azure SQL Database › Test for compatibility › Fix compatibility issues, if any › Perform the migration
Test for Compatibility › Database migration tools test SQL Server database compatibility with SQL Database – SQL Server Data Tools for Visual Studio ("SSDT") – SqlPackage – The Export Data Tier application wizard in SQL Server Management Studio – The Microsoft SQL Server 2016 Upgrade Advisor – SQL Azure Migration Wizard ("SAMW")
Azure SQL Database Transact-SQL differences › SQL Database V12 supports some but not all the arguments that exist in the corresponding SQL Server 2016 Transact-SQL statement –
For example, the CREATE PROCEDURE statement is available however all the options of CREATE PROCEDURE are not available
› Features not supported in SQL Database – – – – – – – – – – – – – – – –
Collation of system objects Connection related Cross database queries Data Collector Database Diagrams Database Mail Encryption: extensible key management Eventing: events, event notifications, query notifications Features related to database file placement, size, and database files that are automatically managed by Microsoft Azure. Features that relate to high availability, which is managed through your Microsoft Azure account FILESTREAM Global temporary tables Hardware-related server settings: memory, worker threads, CPU affinity, trace flags, etc Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, and four-part names Sever-level items And more
Migrate a compatible SQL Server database to SQL Database › To migrate with minimal downtime, use SQL Server transaction replication if your database meets the requirements for transactional replication › If you can afford some downtime or you are performing a test migration of a production database for later migration, consider one of the following three methods: – SSMS Migration Wizard: For small to medium databases, migrating a compatible SQL Server 2005 or later database is as simple as running the Deploy Database to Microsoft Azure Database Wizard in SQL Server Management Studio – Export to BACPAC File and then Import from BACPAC File: If you have connectivity challenges (no connectivity, low bandwidth, or timeout issues) and for medium to large databases, use a BACPAC file › With this method, you export the SQL Server schema and data to a BACPAC file. You then import the BACPAC file into SQL Database using the Export Data Tier Application Wizard in SQL Server Management Studio or the SqlPackage command-prompt utility
– Use BACPAC and BCP together: Use a BACPAC file and BCP for much larger databases to achieve greater parallelization for increases performance, albeit with greater complexity
Deploy and migrate applications › Migration to Azure SQL Database › Migration to SQL Server in an Azure VM › Protect data › Achieve scalability
Migration to SQL Server in an Azure VM › The primary migration methods are: – Use the Deploy a SQL Server Database to a Microsoft Azure VM wizard – Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine – Perform a backup to URL and restore into the Azure virtual machine from the URL – Detach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL – Convert on-premises physical machine to Hyper-V VHD, upload to Azure Blob storage, and then deploy as new VM using uploaded VHD – Ship hard drive using Windows Import/Export Service – If you have an AlwaysOn deployment on-premises, use the Add Azure Replica Wizard to create a replica in Azure and then failover, pointing users to the Azure database instance – Use SQL Server transactional replication to configure the Azure SQL Server instance as a subscriber and then disable replication, pointing users to the Azure database instance
Choosing migration method › For optimum data transfer performance, migration of the database files into the Azure VM using a compressed backup file is generally the best method › This is the method that the Deploy a SQL Server Database to a Microsoft Azure VM wizard uses › This wizard is the recommended method for migrating an onpremises user database running on SQL Server 2005 or greater to SQL Server 2014 or greater when the compressed database backup file is less than 1 TB › To minimize downtime during the database migration process, use either the AlwaysOn option or the transactional replication option
Configure the existing Azure virtual machine and SQL Server instance › Configure the Azure VM and the SQL Server instance to enable connectivity from another computer › Configure an open endpoint for the SQL Server Cloud Adapter service on the Microsoft Azure gateway with private port of 11435 – This port is created as part of SQL Server 2014 or SQL Server 2016 provisioning on a Microsoft Azure VM
› The Cloud Adapter also creates a Windows Firewall rule to allow its incoming TCP connections at default port 11435 › This endpoint enables the wizard to utilize the Cloud Adaptor service to copy the backup files from the on-premises instance to the Azure VM
Deploy and migrate applications › Migration to Azure SQL Database › Migration to SQL Server in an Azure VM › Protect data › Achieve scalability
Transparent Database Encryption: Benefits › Entire database is protected › NO application changes › Applications do not need to explicitly encrypt/decrypt data! › No restrictions with indexes or data types (except FILESTREAM cannot be encrypted)
› Performance cost is small › Backups are unusable without key › Can be used with Extensible Key Management
Transparent Data Encryption: Mechanism › Very simple: – – – –
Database pages are encrypted before being written to disk Page protection (e.g. checksums) applied after encryption Page protection (e.g. checksums) checked before decryption Database pages are decrypted when read into memory
› When TDE is enabled, initial encryption of existing pages happens as a background process – Similar mechanism for disabling TDE – The process can be monitored using the encryption_state column of sys.dm_database_encryption_keys › Encryption state 2 means the background process has not completed › Encryption state 3 means the database is fully encrypted
Transparent Data Encryption: Enabling › Create a master key – CREATE MASTER KEY ENCRYPTION BY PASSWORD = '
';
› Create or obtain a certificate protected by the master key – CREATE CERTIFICATE MyDEKCert WITH SUBJECT = 'My DEK Certificate';
› Create a database encryption key and protect it by the certificate – CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyDEKCert;
› Set the database to use encryption – ALTER DATABASE MyDatabase SET ENCRYPTION ON;
Transparent Data Encryption: Backups › A backup of a TDE encrypted database is also encrypted using the database encryption key › To restore the backup OR attach the database, the DEK must be available! – There is no way around this – if you lose the DEK, you lose the ability to restore the backup (that’s the point!) – Maintain backups of server certificates too
Always Encrypted › Help protect data at rest and in motion, on-premises & cloud Apps
SQL Server
Trusted
SELECT Name FROM Patients WHERE SSN=@SSN
Client side
SELECT Name FROM Patients WHERE SSN=@SSN @SSN='198-33-0987'
Column Master Key
Result Set
Query
@SSN=0x7ff654ae6d
Enhanced ADO.NET Library
Result Set
Name
Name
Jim Gray
Jim Gray dbo.Patients
Column Encryption Key
cipherte xt
Name
SSN
Country
Jane Doe
1x7fg655se2e 243-24-9812
USA
Jim Gray
198-33-0987 0x7ff654ae6d
USA
John Smith
0y8fj754ea2c 123-82-1095
USA
Key Provisioning
Column Encryption Key (CEK)
› Generate CEKs and Master Key › Encrypt CEK › Store Master Key Securely › Upload Encrypted CEK to DB
Column Master Key (CMK)
Encrypted CEK CMK Store: Certificate Store HSM Azure Key Vault …
CMK
Encrypted CEK Database
Dynamic Data Masking › Prevent the abuse of sensitive data by hiding it from users › Configuration made easy in the new Azure portal › Policy-driven at the table and column level, for a defined set of users › Data masking applied in real-time to query results based on policy › Multiple masking functions available (e.g. full, partial) for various sensitive data categories (e.g. Credit Card Numbers, SSN, etc.)
Deploy and migrate applications › Migration to Azure SQL Database › Migration to SQL Server in an Azure VM › Protect data › Achieve scalability
AlwaysOn Availability Groups › Load balancing readable secondaries › Increased number of autofailover targets › Log transport performance › DTC support › Database-level health monitoring › Group managed service account
Unified HA Solution
AG_Listener
AG
Asynchronous data Movement
Hong Kong (Secondary)
AG AG
New York (Primary)
Synchronous data Movement
New Jersey (Secondary)
Readable Secondary load balancing › The Always On Availability Groups active secondary capabilities include support for read-only access to one or more secondary replicas › To configure an Always On availability group to support readonly routing in SQL Server 2016, you can use either Transact-SQL or PowerShell – To configure read-only routing for the secondary role, specify the SECONDARY_ROLE option › SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')
– Configure load-balancing across read-only replicas › READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4')
DEPLOY AND MANAGE APPLICATIONS Migration to Azure SQL Database Migration to SQL Server in an Azure VM Protect data Achieve scalability
