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

Datawrapper Makes Data Beautiful & Insightful

Datawrapper is an online data visualization tool available at datawrapper.de that lets you create charts, maps and tables. Here's the list of the different visualizations you can create - Bar Chart Stacked Bars Grouped Bars Split Bars Bullet Bars Column Chart Stacked Columns Grouped Columns Lines Multiple Lines Area Chart Scatter Plot Dot Plot Range Plot Arrow Plot Election Donut Pie Chart Multiple Pies Donut Chart Multiple Donuts Choropleth map Symbol map Locator map Table It is self-funded by its customers’ subscription revenue. It also provides a good Free Plan .  The Datawrapper “Created with Datawrapper” attribution appears at the bottom of the chart if you use the free plan. However, you retain full copyright to your charts, maps, and tables. To remove the attribution, you’ll need to update to Custom or Enterprise plan. The Datawrapper River is a place to exchange charts and maps with other Datawrapper users. After adding a chart to the River, anyone can use it and customiz...
Read more

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

GitHub Copilot Q&A - 1

Image
Key takeaways from the talk in Q&A format - What is GitHub Copilot and how does it work? GitHub Copilot is a suite of tools designed for code completion. It works by analysing the code and comments you type in your IDE and sending that context to GitHub. Copilot then uses OpenAI's language model (in December 2024, it is GPT 4o by default and you can also choose the preview versions of o1-mini & o1-preview if you like) to understand your intent and generate suggestions for code completion. You can then accept, reject, or modify these suggestions. What context does GitHub Copilot consider when making suggestions? Copilot considers the entirety of the file you are currently working on, not just the code from your cursor position upwards. It also analyses the content of other open tabs in your IDE, starting with those closest to the current tab. This helps Copilot focus on the most relevant information. Is it necessary to meticulously manage open tabs for Copilot to work effect...
Read more