SQL Server Enterprise Architect Summit (SEAS)
SQL Enterprise Architect Summit was held in Microsoft Melbourne Office on 27 and 28 February 2013. SEAS was a 2 day, intensive 400-level event designed for experienced database architects and database administrators. Link to Microsoft site
I attended this event. The biggest word now in Microsoft community is “Cloud”, cloud-hosted varieties, technologies and platforms. Microsoft is the only vendor who has range of integrated service in the cloud. Here are my notes.
Learning and leveraging the new features and functionality of SQL Server 2012 and SQL Azure 2012 for the business critical solutions. Discover best practices, real world experience, design and deployment of mission-critical SQL Server enterprise solutions.
Nicholas Dritsas and Emil Velinov – members of SQL Server Customer Advisory Team (CAT) in Redmond. Nicholas is a Principal Program Manager for SQL Server Product group at Microsoft. Emil Emil is a Senior Program Manager in the Windows Azure Customer Advisory Team (CAT) at Microsoft.
1. How to scale Windows Azure with SQL Azure databases?
- Windows Azure offers multiple services to manage enterprise data and applications in the cloud across a global network of Microsoft-managed datacenters
- Subscription requirements
- Microsoft provides cloud architecture fit for the purpose / performance/ high availability (3 physical nodes with a balancer)
- PaaS – platform as a service (includes SQL Service licences)
- IaaS – infrastructure as a service (provision for own VHD/ VMs)
- Windows Azure ideal for applications needing: Scalability, Availability and Fault Tolerance
- Windows Azure Candidate: dispersed users and data, elastic demand (moving from Capex -> Opex), quick scale out
- End 2013. Hybrid cloud. Data in house, secure VPN, supporting encryption
- Win Azure consist of: Azure Compute | Azure Storage | Azure SQL databases
2. Best practices for installing and configuring SQL Server 2012 on Windows Azure IaaS
- Cloud Offerings: IaaS, PaaS; SaaS
- Microsoft has offers all 3 areas
- Win Azure service: VM Role (stateless machines) and VM. Consist of infrastructure to deploy an application, persistent OS disk and data disks, internal/ external networking
- You to define the Availability Set of your Virtual Machine – machines on different nodes and racks for HA
- Best practice making sure that your databases are indeed highly available, whether you are using AlwaysOn Availability Groups or mirroring
- General recommendation is to attach a separate data disk with no read or write caching and use it for database files. Attaching separate disks also enable you to customize disk capacity up to 1 terabyte.
- If you need to store database files on the OS disk, it is recommended that you disable write caching when you provision the VM. You cannot disable read caching on the OS disk.
3. AlwaysOn HA/DR Design Patterns, Architectures and Best Practices
- AlwaysON ≠ Availability Group
- AlwaysOn is a integrated HA and DR solution consist of SQL Server Failover Cluster Instances (FCI) and Availability Groups
- Availability Group is the ability to failover multiple databases as a unit to ensure the availability of all databases in the application
- Works on contained databases (Logins is part of database backup)
- Read only replicas can be used for backups, database maintenance, reporting
- Advantages: multi-database failover, built in compression and encryption, automatic page repair, application failover using virtual name
4. Learn from the Largest Azure Projects in the World
- Largest sharded Database – 20 TB, No of databases – 11000, No of cores – 24000, Microsoft Application size – 1 Exabyte, Customer Application size – 50 PB
- Examples: Florida Election 2012, BING Games, Samsung Smart TV, MYOB, PotterMore.com, TVB
- Common for all projects: Application bursting (Azure can provide dedicated cache), Application servers and database growing very fast with large number of connections and large number of deployments; frequent updates of application and/or software; large No of synchronised connection to Azure framework
5. Microsoft Big data
- What is big data: high number of data velocity, data variety and data complexity
- HDInsight is Microsoft’s 100% Apache compatible Hadoop distribution available Windows Server or as an Windows Azure service
- Answers new type of questions eg What is social sentiment on …? How traffic / weather affect my business etc?
- Microsoft Solution market Place:
- Data Management – managing enterprise relational and non-relational data, streaming and multimedia
- Data Enrichment – Discover, transform and govern data
- Insight – real time and predictive analysis
- PDW – Parallel Data Warehouse, appliances from HP/ Dell
- Hadoop – distributed file system, processing done at the node through map reduce scheduling and execution component
6. Columnstore Index Implementation and Performance
- ColumnStore (CS) –technology for query performance in data warehouse
- In memory technology for relational engine
- No need for summary / fact tables, indexed views, cubes -> lower hardware cost
- CS in a new index type
- Query processing in new mode – batches compare to rows
- Accelerates targeted workload with highly efficient algorithm
- Min/max values stored in metadata