SQL News during SQL Rally 2015
During the SQL Rally and Microsoft Keynotes a lot of news was presented, of course there was a lot of talk about Azure and the new version that is being released in stages in Microsoft's data centres. There was also a lot of talk about the increasing amount of data in the world, Microsoft's Nigel Ellis talked about connected cows, using activity bands to collect important data to increase farmers' efficiency. With that kind of new data, we're going to need a lot of storage in the future...
SQL vNext and Windows 10
At a demo we got to see the next SQL version and how together with Windows 10 and SMB3 you can read in parallel from 2 Always On databases (1 primary and 1 secondary readonly), with fast SSD we could get up to 5 gb/s reads!
Another new feature is "Stretch tables" i.e. we can place a table both on an on-premise database and in Azure, it seems to be a partitioned table and the idea is that the active part is on-prem and the inactive historical/archived part is in Azure.
Using the Query Store we can more easily debug execution plans as it will version manage plans in the cache so we can compare with previous plans and go back if we have got a new plan that is not working optimally.
Row Level Security (RLS)
New security feature that allows us to restrict access to specific rows in a database in a simple and robust way without having to change the application too much, currently available as Azure preview feature, coming in future SQL releases
Största Azure kunden har 114 000 databaser.
Microsoft har för närvarande 64 klusters i 19 datacenters, varje kluster består av 400-800 noder, nya datacenters kommer att öppnas speciellt på ”nya” marknader såsom tex Kina, i Europa finns 2 datacenter, Irland och Holland.
Aktuella Azure SQL Tiers:
– Basic (max 2GB db storlek) Point In Time Restore 7 dagar
– Standard (max 250 GB db storlek) Point In Time Restore 14 dagar
– Premium (max 500 GB db storlek) Point In Time Restore 35 dagar
Olika Data Throughputs Units ”DTU” kan väljas förutom i Basic där det endast finns 5 DTU, består av CPU, minne och I/O, max är 800 DTU som finns i Premium och heter P3
Premium använder numera SSD, 10x mer IOPS
Data Protection och Disaster Recovery:
Geo-Restore geo-redundant backup RPO < 1 hour – Recovery time: Minutes to hours (alla tiers)
Geo-Replication Asyncronous replication till ett regionalt data center RPO < 5 seconds – Recovery time: < 30 seconds (Standard/Premium)
Data Recovery (vid tex felaktig borttagning)
Point in Time Restore (PITR)
Recovery Time: Minutes to hours
New Azure version - V12
Azure SQL Database V12 - released Europe 29/1 - 2015
Some of the new features:
- Large databases, table partitioning, parallel queries, online + large index rebuild blue page and row compression, HEAPs
- Programming - CLR, XML index, T-SQL windows functions, change tracking bla SELECT INTO, Sequence objects
- Extended events, 100 new DMVs
- Better performance for data analysis, clustered and non clustered columnstore index
SQL Azure MW - Azure Migration Wizard
Open source tool that checks if an on-premise SQL database can be moved to Azure