Friday, May 1, 2015

Migrating On-premise .Net Web Applications to Microsoft Azure - Part 2

SQL Date time Considerations

SQL Datetime manipulation is one of the most questioned topic while dealing with applications with time sensitive data. Applications hosted on single server or hosted in multiple servers located within the same datacenter will not impact datetime processing as it is located within a single time zone, but this is not the same in Cloud. As Microsoft offers datacenter in many different regions in many different time zones, it’s important to carefully deal with time sensitive information.

Conventionally developers used Datetime object of .Net 2.0 and SQL Server to store Datetime data with additional overhead of serializing, parsing and datetime conversion.

Datetime object provides Date and Time of a particular server’s calendar where the server resides and doesn’t give any other additional information like Time Zone etc. and hence it is very ambiguous. Some applications store Datetime in UTC format and handle the convention at the application layer which is a good practice to follow.

The alternate is DateTimeOffset, which represents a point in time, typically expressed as a date and time of day, relative to Coordinated Universal Time (UTC) which uniquely and unambiguously identify a single point in time. It’s advisable to update all the Datetime Objects in the application and Database objects to DatetimeOffset for better handling of datetime value in Azure SQL.

Alter table [8kmiles].[Employees] 
Alter column JoinedDate DatetimeOffset

SQL Database tables usually have primary key AKA Clustered indexes on each of the tables when they design the database, but sometime they leave if they don’t have proper awareness or don’t understand the database design principles. If Azure SQL is chosen as the targeted database, it doesn’t entertain tables which doesn’t contain Primary Key constraint, so it’s important to make sure that all the tables updated with Clustered index before migrating to Azure SQL.

Along with clustered index, it is also good practice to create non clustered indexes for the columns that are usually queried upon. Indexes, helps you to improve the performance of the OLTP queries.

While migrating the database, we have to consider index fragmentation aspect i.e. rebuilding and reorganizing the index’s in each and every tables. Best practice for index is always measuring fragmentation in percentage.  

Syntax for reorganizing and rebuilding the index

--Index Rebuild
USE <>
--Index Reorganize
USE <>

Pricing & SLA Considerations

Azure SQL is a No Frill Database (SQL Server) as-a-Service on a pay as you go model also priced very competitively compared to regular SQL Server licensing model. Azure SQL offered in three service tiers namely Basic, Standard and Premium Tier which are in (General availability) and SQL Database Service Version (V12) in (Preview). Irrespective of the tier, all the editions provide 99.99 % Uptime SLA and Security, but the primary differences are Database Size, Replicability and Database Throughput Units (DTU).

Standard tier is recommended for most of the applications which has moderate to large I/O, because it provides 250GB of max storage and up to 100 DTUs and standard geo replication capability. However, if your database is currently huge and requires more DTUs its recommended to choose premium Tier or the new edition i.e. SQL Database Service Version (V12) in (Preview) which offers 5X times better performance than the Premium Edition.

Few benefits of SQL Database Service Version (V12)

  • T-SQL support with common language runtime 
  • XML Indexing support
  • Support for In-memory column store for better performance

Azure SQL Security

Azure SQL is inherently secure in all respects. By default only 1433 TCP port is opened and the internal firewall must be instructed to accept connection from your local desktop or from specific server. All the SQL connections are accessed through Encrypted connection, if your application try connect without https, intruders can do man in the middle attack. To make sure, you access only encrypted connection set “Encrypt=True and TrustServerCertificate=False” in the connection string.

As a best practices of security, it is generally recommended to use the latest and updated Azure SDKs and libraries to prevent security vulnerabilities. It is also advisable to prevent your application from Cross Site Scripting issues, usage of parameterized queries is advised to avoid SQL injections.

User Defined Datatypes

If the applications that you are migrating contains user defined common language runtime (CLR) data types or objects, you must update your application to adopt to Azure SQL Supported Datatypes. Please refer the Azure SQL datatypes supported.

There are many applications suffers due to this,  Developers have developed user defined data types for cases like phone numbers in a specific format, alpha numeric employee ID's, IP addresses, etc for better consistency in the application, but Azure SQL doesn’t support this user defined data types at this moment. The alternative solution if you don’t want to change the application code is to choose SQL Server installed on VM.

Other Database options

Generally existing applications primarily used SQL Server irrespective of the type of data, be it Relational Data, Non-Relational Data, Map Data, Object Data, Graph etc, due to the unavailability of database technologies which are available now. Hence all the different variety of data was dumped only to SQL server or any other Relational Database for that matter. 

But this is not the case today, with the services like 

  • Document DB (Document based non-relational storage, Ideal for building next general scalable web applications), 
  • Azure Table, a columnar storage to store Key pair Values
  • Azure Queue Store, a large message storage service

While migrating the Database to cloud, it is also best to analyze the kind of data resides in the SQL Store and find the right storage solution for better performance and scalability. NoSQL type Databases like Azure DocumentDB and Table Storage are best suitable for Heavy traffic intensive, user content generated web applications which requires massive scale and performance, but on the other hand if your applications heavily depends on ACID principles such Atomicity, Concurrency, Integrity, and Durability and fine with the current performance, it’s wise to stick with SQL store like Azure SQL or SQL Server.

Database Backup and Restore

Back in onpremise, you might have configured different kinds of complex both manual and automated database backup and recovery mechanisms. Moving to Azure SQL will certainly help you to achieve higher resiliency and advanced database replication and redundancy. By default, Azure SQL creates 1 Primary DB and 2 secondary replicas of your database in a separate physical node away from the primary server. In the event of primary DB outage, Azure SQL will automatically promote the secondary DB as the primary DB without you noticing the outage without any data loss. 

Along with the default data backup and recovery options, Azure provides additional database recovery solutions for the Risk Averse Data oriented customers. They are

1. Standard Geo Replication
2. Active Geo Replication

These solutions helps you to build a highly available and resilient Data driven web solutions. Apart from Basic Tier, these options are available with both Standard and Premium tiers.  To understand the difference between these 2 options it is important to understand the below terminologies.

Estimated Recovery Time (ERT) - Time taken to restore your database to active state when there some disaster happens at your primary datacenter) 
Recovery Point Objective (RPO) -The amount of most recent data changes (time interval) the application could lose after recovery.

Note: Standard GEO Replication and Active Geo Replication offers ERT* < 30 seconds & RPO† < 5 seconds.  

Standard GEO Replication allows us to create a non-readable secondary replica to another region which can be auto/manual auto failure in case of Primary goes down. On the other hand, Active Geo replications allows us to create up to 4 readable replicas which can solve 2 major purpose, 1. We can use it for database load balancing and 2. Database Failover.

To Summarize, Standard Geo Replication is targeted towards application Medium to Large applications with moderate update rates and cost centric customers. Active Geo replication is for high intensive applications with heavy write data load with a bit high pricing. Based on your application, you can evaluate these pointers and choose the right disaster recovery solutions from Azure SQL.

SQL Server to Azure Migration Wizard

Finally the migration (Schema + Data) is the final and the most important part of Azure Migration process. Database migration is the 3 step process

1. Create Schema objects in Azure SQL
2. Load or Move the Data from Local Database to Azure SQL
3. Make sure the Schema and Data in Azure SQL 100% syncs with local Database

There are handful of Tools to help developers with Data migration process, but it is up to the developer to make sure the Schema and the Data are up to the Azure SQL Guideline and Limitations (Click here). Make sure, there are no Custom user defined data types, Clustered indexes are created etc before moving the Schema objects to Azure SQL.

With respect to the Data migration and Sync Tools, there are 3 major ways to do the data movement, they are

1. Using SQL Server Management Studio to create Scripts for the schema and insert scripts for the Data and executing the same against Azure SQL
2. SQL Database Migration Wizard (SQLAzureMW) is a Community developed Open source tool designed to help you migrate your SQL Server 2005/2008/2012/2014 databases to Azure SQL Database. As a word of precaution, this tool doesn’t validate UDFs or existence of Clustered index, hence it is highly advisable to make sure those attributes are defined and validated before using the tool.

Note:  SQLAzureMW Community has also released a CookBook for migrating Databases to Azure SQL Database update V12, here.
3. SQL Server 2008 Integration Services (SSIS) 
4. The bulk copy utility (BCP.exe)
5. System.Data.SqlClient.SqlBulkCopy class

Once the SQL DB migration is completed, make sure the schema, objects and data are identical and same as onpremise Database setup.

To summarize, we highly recommend you to review the limitations and see if they affect your current SQL server implementations. Often these limitations are relaxed and removed with the introduction new versions Azure SQL.

Azure SQL
SQL on Azure VM
Max 500 GB
50+ Tb
only 1433
User based customization is also available
Backup database
Either we can script the database or table using standard customized sql scripts.
By default backup and restore via SSMS
Restore database
Create a database and then execute the series of scripts
By default backup and restore via SSMS
Distributed transaction
Not supported
on demand we can use MSDTC
Scheduling and automation
Not available
By default sql agent service will be executing series of TSql command
Login and securable
2 basic Server level security not allowed for windows authentication
By default Server level security/Database level security
database HA
Highly Available
Log shipping /Mirroring
Table level HA
Highly Available
Replication[Transactional/merge/peer to peer replication]
Instance level HA
Highly Available
Clustering [active/active, active/passive]
Disaster Recovery Solution
Active Geo Replication up to 4 online secondary’s only for premium tiers
Multiple availability Zone or native database technology
Max No Database support
150 including Master
32767 including system database
SQL Server Collation Support
Only  database level col SQL_LATIN1_GENERAL_CP1_CI_AS
It is available for Instance/database/table/Column