Logins are stored in the master database.

Authentication and authorization

Authentication is the process of proving the user is who they claim to be. A user connects to a database using a user account. When a user attempts to connect to a database, they provide a user account and authentication information. The user is authenticated using one of the following two authentication methods:

• SQL authentication

With this authentication method, the user submits a user account name and associated password to establish a connection. This password is stored in the master database for user accounts linked to a login or stored in the database containing the user accounts not linked to a login.

• Azure Active Directory Authentication

With this authentication method, the user submits a user account name and requests that the service use the credential information stored in Azure Active Directory (Azure AD).

Logins and users: A user account in a database can be associated with a login that is stored in the master database or can be a user name that is stored in an individual database.

• A login is an individual account in the master database, to which a user account in one or more databases can be linked. With a login, the credential information for the user account is stored with the login.

• A user account is an individual account in any database that may be, but does not have to be, linked to a login. With a user account that is not linked to a login, the credential information is stored with the user account.

Authorization to access data and perform various actions are managed using database roles and explicit permissions. Authorization refers to the permissions assigned to a user, and determines what that user is allowed to do. Authorization is controlled by your user account's database role memberships and object-level permissions. As a best practice, you should grant users the least privileges necessary.

https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

Process for developing solutions

Your goal in the real world, is to understand:

• The problem that the company needs to solve.

• Any requirements and constraints that go along with a solution.

This goal is often in the form of a problem statement. It's a formal set of paragraphs that clearly define the circumstances, present condition, and desired outcomes for a solution. At this point, you want to avoid exploring how to solve the problem and focus on what you want to solve.

After you (and likely your team and stakeholders) agree on a problem statement, you should pull out as many requirements (goals) for the project as you can find. Then lay in any constraints that the solution has.

At this point, it's acceptable to have unrealistic constraints. Later, you can pull those back after showing a cost/benefit ratio on each requirement and constraint.

In production, there are normally six phases to create a solution. Developing the problem statement is just the beginning.

1. Discovery: The original statement of the problem from the customer.

2. Envisioning: A "blue sky" description of what success in the project would look like. It's often phrased as "I can..." statements.

3. Architecture design session: An initial layout of the technology options and choices for a preliminary solution.

4. Proof of concept (POC): After the optimal technologies and processes are selected for the solution, a POC is set up with a small representative example of what a solution might look like. If a currently running solution in a parallel example is available, you can use that.

5. Implementation: Implementing a phased-in rollout of the completed solution based on findings from the previous phases.

6. Handoff: A postmortem on the project with a discussion of future enhancements.

https://docs.microsoft.com/en-us/learn/modules/azure-sql-case-studies/2-scenario-1

Columnstore indexes

Columnstore indexes were introduced in SQL Server 2012 and offer improved performance for queries that run large aggregation workloads. This type of index was originally targeted at data warehouses, but over time columnstore indexes have been used in a number of other workloads in order to help solve query performance issues on large tables. As of SQL Server 2014, there are both nonclustered and clustered columnstore indexes.

Like b-tree indexes, a clustered columnstore index is the table itself stored in a special way, and nonclustered columnstore indexes are stored independently of the table. Clustered columnstore indexes inherently include all the columns in a given table. However, unlike rowstore clustered indexes, clustered columnstore indexes are NOT sorted.

Nonclustered columnstore indexes are typically used in two scenarios, the first is when a column in the table has a data type that is not supported in a columnstore index. Most data types are supported but XML, CLR, sql_variant, ntext, text, and image are not supported in a columnstore index. Since a clustered columnstore always contains all the columns of the table (because it IS the table), a nonclustered is the only option. The second scenario is a filtered index—this scenario is used in an architecture called hybrid transactional analytic processing (HTAP), where data is being loaded into the underlying table, and at the same time reports are being run on the table. By filtering the index (typically on a date field), this design allows for both good insert and reporting performance.

Columnstore indexes are unique in their storage mechanism, in that each column in the index is stored independently. This offers a two-fold benefit — a query using a columnstore index only needs to scan the columns needed to satisfy the query, reducing the total IO performed, and it allows for greater compression, since data in the same column is likely to be similar in nature.

Columnstore indexes perform best on analytic queries that scan large amounts of data, like fact tables in a data warehouse. Starting with SQL Server 2016 you can augment a columnstore index with an additional b-tree nonclustered index, which can be helpful if some of your queries do lookups against singleton values.

Columnstore indexes also benefit from batch execution mode, which refers to processing a set of rows (typically around 900) at a time versus the database engine processing those rows one at time. Instead of loading each record independently and processing them, the query engine computes the calculation in that group of 900 records. This processing model reduces the number of CPU instructions dramatically. This works best for a query like the following:

1. Transact-SQL
2. SELECT SUM(Sales) FROM SalesAmount;

Batch mode can provide up to a 10x performance increase over traditional row processing. SQL Server 2019 also includes batch mode for rowstore data. While batch mode for rowstore does not have the same level of read performance as a columnstore index, analytical queries may see up to a 5x performance improvement.

The other benefit columnstore indexes offer to data warehouse workloads is an optimized load path for bulk insert operations of 102,400 rows or more. While 102,400 is the minimum value to load directly into the columnstore, each collection of rows, called a rowgroup, can be up to approximately 1,024,000 rows. Having fewer, but fuller, rowgroups makes your SELECT queries more efficient, because fewer row groups need to be scanned to retrieve the requested records. These loads take place in memory and are directly loaded to the index. For smaller volumes, data is written to a b-tree structure called a delta store, and asynchronously loaded into the index.

In this example, the same data is being loaded into two tables, FactResellerSales_CCI_Demo and FactResellerSales_Page_Demo. The FactResellerSales_CCI_Demo has a clustered columnstore index, and the FactResellerSales_Page_Demo has a clustered b-tree index with two columns and is page compressed. As you can see each table is loading 1,024,000 rows from the FaceResellerSalesXL_CCI table. By using the SET STATISTICS TIME ON option, SQL Server keeps track of the elapsed time of the query execution. Loading the data into the columnstore table took roughly 8 seconds, where loading into the page compressed table took nearly 20 seconds. In this example, all the rows going into the columnstore index are loaded into a single rowgroup.

If you load less than 102,400 rows of data into a columnstore index in a single operation, it is loaded in b-tree structure known as a delta store. The database engine moves this data into the columnstore index using an asynchronous process called the tuple mover. Having open delta stores can impact the performance of your queries, because reading those records is less efficient than reading from the columnstore. You can also reorganize the index with the COMPRESS_ALL_ROW_GROUPS option in order to force the delta stores to be added and compressed into the columnstore indexes.

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

Deadlocking occurs when one transaction has a lock on a resource, and another transaction has a lock on a second resource. Each transaction then attempts to take a lock on the resource which is currently locked by the other transaction.

Theoretically, this scenario would lead to an infinite wait, as neither transaction could complete. However, the SQL Server engine has a mechanism for detecting these scenarios and will kill one of the transactions in order to alleviate the deadlock, based on which transaction has performed the least of amount of work that would need to be rolled back.

The transaction that is killed is known as the deadlock victim. Deadlocks are recorded in the system_health extended event session which is enabled by default.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/deadlock-analysis-for-sql-azure-database/ba-p/368883

Security Principal is an Azure object (identity) that can be assigned to a role (ex. users, groups or application/service objects like service principals, or managed identities).

Security Principal assignment is an answer to the question “Who can do it?”

https://docs.microsoft.com/en-us/azure/role-based-access-control/built-in-roles

It's not possible to keep a db_owner from viewing user data with permission only. If highly sensitive data is in a database, you can use Always Encrypted to safely prevent even db_owners from seeing it.

You can manage the Always Encrypted keys with role separation. The security admin doesn't access the database, and the DBA doesn't access the physical keys in plaintext. You can also use SQL Audit, Data Classification, and Dynamic Data Masking in combination to monitor.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

Azure Resource Manager templates have the benefit of being able to deploy a full set of resources in one single declarative template. You can build dependencies into the templates, as well as using parameters to change deployment values at deployment time. Once you have a template, you can deploy it several ways including using an Azure DevOps pipeline, or through the custom deployments blade in the Azure portal.

The benefit of these deployments is that they use a declarative model, which defines what should be created. The Azure Resource Manager framework then determines how to deploy it. The alternative to the declarative model is the imperative model.

Imperative frameworks include PowerShell and the Azure CLI, which follow a prescriptive order of tasks to be executed.

A common term used around cloud computing deployments is “infrastructure as code”, which means all of your resources are defined a set of scripts that are stored in source control and can easily be deployed to a new environment. While stateful resources like databases are not deployed as frequently as application code, by defining your infrastructure, you ensure that resources are deployed in a consistent fashion, reducing the configuration risk and the impact of human error. As mentioned above, there are two programming models that are used for cloud deployments: imperative and declarative.

In imperative programming, you are defining a set of prescriptive tasks for the target system to execute. A simple example of this model is using a batch script to install SQL Server and its prerequisites. In declarative programming, you are describing a set of resources to be defined, typically by a framework. A simple example is a CREATE TABLE statement, which describes the columns and keys to be built by the SQL Server engine. The statement acts as the framework for building the table.

PowerShell

PowerShell provides a core module known as Az, which has child resource providers for nearly all Azure services. For example, 'Az.Compute' would cover Azure Virtual Machines. PowerShell is more commonly used for resource modification and status retrieval. While it is possible to create resources using PowerShell, it is not typically used for complex deployments. PowerShell can also be used to deploy Azure Resource Manager templates, so in a sense it supports both declarative and imperative models.

https://docs.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-5.3.0

Azure CLI

The Azure Command Line Interface, or CLI, is similar to PowerShell in that it can be used either imperatively or declaratively. Much like PowerShell and Azure Resource Manager templates, the Azure CLI provides a mechanism to deploy or modify Azure Resources. Some commands for Azure PostgreSQL and Azure MySQL Databases are only available in the Azure CLI.

https://docs.microsoft.com/en-us/cli/azure/what-is-azure-cli

Azure Portal

The Azure portal is a graphical interface to Azure Azure Resource Manager. Any resources you build and deploy using the portal will have an Azure Resource Manager template that you can capture by clicking "Export Template" in the Settings Blade:

https://docs.microsoft.com/en-us/azure/azure-portal/azure-portal-overview

Data Discovery & Classification

The Data Discovery & Classification service forms a new information-protection paradigm for Azure SQL Database and Azure SQL Managed Instance. The service is aimed at protecting the data and not just the database.

This paradigm includes:

• Discovery and recommendations for potentially sensitive data that should be classified.

• The ability to persistently add labels to columns by using metadata attributes.

• The ability to audit and query sensitive data access.

The portal view is available only for Azure SQL, but SQL Server supports similar functionality through a wizard in SQL Server Management Studio.

https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview

The deployment options include:

• SQL Server on virtual machines

• Azure SQL Managed Instance

• Azure SQL Database

• Azure SQL Managed Instance Pools

•Azure SQL Database Elastic Database Pools

https://cloudblogs.microsoft.com/sqlserver/2020/03/17/tips-to-select-the-best-azure-sql-deployment-option/

Use an offline index rebuild because online index rebuilds aren't supported on Azure SQL.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-maintain-azure-sql-indexes-and-statistics/ba-p/368787