30 things to know about Microsoft Azure SQL Database

My notes from Microsoft Virtual Academy's Windows Azure SQL Database course:
  1. SQL Database is a Platform as a Service (PaaS) feature of Microsoft Azure. You can also run a on-premise SQL Server database on IaaS VM.
  2. In general, the smaller the database size and table size, the better the use case works for SQL Database (PaaS).
  3. SQL Database has 3 editions: Web, Business, Premium
  4. Web and Business offer relational databases run on shared resources, with built-in replicas within a datacenter. Web edition supports up to 5GB databases, and Business edition supports up to 150 GB databases. 
  5. SQL Database Premium edition is charged based on the reservation size assigned to the database and storage volume of the database.
  6. SQL Database provides the same tabular data stream (TDS) interface as SQL Server. Therefore, you can use familiar tools and libraries to build client applications for data that is in the cloud.
  7. There are currently two Premium database sizes during preview. P2 offers twice the performance of P1 
  8. You can change the maximum capacity or edition of SQL Database after it is created. 
  9. By default, Windows Azure SQL Database supports up to 150 databases in each SQL Database server, including the master database. You can create up to 149 databases in each SQL Database server. 
  10. Windows Azure SQL Database does not support tables without clustered indexes.
  11. Because Windows Azure SQL Database is a service, administration in Windows Azure SQL Database is slightly different. 
  12. SQL authentication is supported but not Integrated authentication
  13. You can connect to master to administer logins
  14. loginmanager is the Server-level security role for creating logins
  15. dbmanager is the Server-level security role for creating databases
  16. You can add users to roles via sp_add_rolemember to grant privileges
  17. SQL Profiler cannot be used with SQL Database – Use DMVs instead.
  18. Currently there is no facility for standard point-in-time backup. Use CREATE DATABASE … AS COPY OF or export copy as BACPAC to Azure Blob Storage
  19. You can use Windows Azure Platform PowerShell cmdlets (or use REST API directly) to automate SQL Database server provisioning 
  20. For securing your data, there is IP Address-based access control for SQL Database
  21. Firewall Rules can be defined at the server and/or database level
  22. No IP authorized by default to access the SQL database, not even Azure itself
  23. Firewall Rules are configurable using the SQL Database Portal and REST API
  24. There is an option to disable/enable access from applications hosted in Windows Azure 
  25. Windows Azure SQL Database throttling ensures that one database cannot consume all available shared resources
  26. While using SQL Server Management Studio to manage a Windows Azure SQL database, you can utilize Object Explorer, Query Windows, Execution Plans but not SQL Agent, Management Plans, SSIS
  27. Microsoft Enterprise Library Transient Fault Handling Application Block can be used with SQL database, Windows Azure Service bus, Windows Azure Storage & Windows Azure Caching Service
  28. To migrate an existing SQL Server database to Windows Azure SQL Database, you can 1) generate a script from the existing Database or use 2) Microsoft Sync Framework 2.1 3)  Data-tier Application Export and Import 4) SSIS 5) bcp 6) SQL Database Migration Wizard tool built by the community 
  29. A SQL Database != A SQL Server. SQL Database has limitations you should be aware of especially if you're migrating your on-premise SQL Server database.
  30. Windows Azure SQL Database does not support distributed transactions, which are transactions that affect several resources.

A couple of slides from the 5 part course

Comments

Post a Comment