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

Popular posts from this blog

Maven Crash Course - Learn Power Query, Power Pivot & DAX in 15 Minutes

Image
In the Maven Crash Course " Learn Power Query, Power Pivot & DAX in 15 Minutes ", Chris Dutton walks through a hypothetical business case while showcasing the power of modern Excel tools like Power Query, Power Pivot and DAX. Power Query is used to extract, transform, and load data from external sources like SQL databases, PDF and CSV files. A relational model is created to join the tables without writing a single formula. Exploratory analysis is done using Power Pivot and Data Analysis Expressions (DAX). Data Analysis Expressions is the language that we can use to define new calculated columns and measures to enhance our Data Model instead of the traditional cell formulas that we'd typically use if our data was stored in worksheets. Pivot Charts and slicers are used to design a simple interactive report that the sales managers can use to analyze revenue trends and product performance.
Read more

"Data Prep & Exploratory Data Analysis" course by Maven Analytics

Image
The Maven Analytics course on " Data Prep & Exploratory Data Analysis " by Alice Zhao is a beginner-friendly introduction to machine learning with Python. It covers the key steps of the data science workflow, including: Gathering Data Scoping a Project Modeling Data Sharing Insights Cleaning Data Exploring Data Modeling Data  Sharing Insights The presentation deck which is available as a download crisply summarizes the essential details and is a great reference to keep going back to. The course is well-suited for aspiring data scientists looking to get hands-on experience with the data prep and EDA stages of the machine learning workflow using Python and Jupyter Notebook. 
Read more

Gartner Magic Quadrant for Strategic Cloud Platform Services

Image
Gartner defines strategic cloud platform services (SCPS) as standardized, automated, public cloud offerings integrating infrastructure services (e.g., computing, network and storage), platform services (e.g., application, data and value-added services such as AI/ML) and transformation services (resources to help customers adopt cloud-oriented IT delivery models). Image - Gartner Insights I found interesting from the Gartner Magic Quadrant for Strategic Cloud Platform Services report: The eight SCPS providers & global hyperscalers featured in this Magic Quadrant control over 97% of the total CIPS market. While AWS has maintained profitability and a leading global infrastructure as a service (IaaS) and platform as a service (PaaS) market share of over 42%, its growth rate relative to its key competitors slowed in 2023. Azure public cloud market share is 26%. GCP's overall market share stands at 11.5%. Oracle's strategy of positioning OCI as the top cloud for Oracle software i
Read more