0

When a database is fully contained, it is having all the objects within the database boundary. It manages connection also at database level. Contained Database

I have few questions:

  • If I host a contained database in Azure SQL Single DB then what is the difference between a Azure SQL single db and a contained database as Azure SQL single db ?

  • Why do we have a separate offering as Azure SQL Single Db, when contained database also is something similar to it ?

  • Does making a database as contained database will help in easier migration to Azure SQL single DB ?

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58

1 Answers1

1

In Azure speak, "single" is a deployment option that differentiates Azure SQL Database from Managed and Elastic Pools. There's really no difference from a containment perspective because Single and Azure SQL Database are one and the same.

Azure SQL Database inherently provides containment features like database-level user authentication. With on-prem SQL Server databases, one must opt-in for with CONTAINMENT=PARTIAL to permit database-level authentication.

Does making a database as contained database will help in easier migration to Azure SQL single DB ?

CONTAINMENT=PARTIAL permits database-level authentication in on-prem versions, facilitating migration of database security principals. As long as user database entities stay within the database boundary and one doesn't need features unavailable in contained databases (e.g. CDC), migration is typically easy.

A consideration, though, is that partially contained databases implicitly use catalog collation Latin1_General_100_CI_AS_KS_WS_SC whereas Azure SQL Database catalog collation must be either the chosen DATABASE_DEFAULT collation or SQL_Latin1_General_CP1_CI_AS. This is generally an issue only when case-sensitivity of object/variables names is desired.

Existing uncontained references can be identified by querying sys.dm_db_uncontained_entities:

SELECT * FROM sys.dm_db_uncontained_entities;

The above query will also identify dynamic SQL references that will need to be examined manually.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • can you please elaborate, why Azure SQL Database is partially contained. Also, how can we tell whether a database is fully contained ? – Venkataraman R Feb 26 '20 at 13:03
  • @VenkataramanR, I didn't word my answer as clearly as I should have but what I basically meant is that contained features like database-level authentication are available by default in Azure SQL Database whereas on-prem databases require opting in with the `PARTIAL` containment option. You can identify uncontained entities with `SELECT * FROM sys.dm_db_uncontained_entities;`. I'm off to my day job now but will improve my answer when I get back. – Dan Guzman Feb 27 '20 at 12:50
  • Sure. thanks. Just wanted a clear explanation, so that it is helpful for others in future. I will accept the answer, once you have updated the same. – Venkataraman R Feb 28 '20 at 06:52
  • Thanks for taking time to answer. I have accepted the answer – Venkataraman R Mar 02 '20 at 05:29