Restore database across servers(Azure SQL Database and Azure SQL managed instance)- Azure Automation
In this article, we consider the scenario where customers would like to restore their Azure SQL database and managed database from one Azure SQL server to another Azure SQL server, for example for development reasons. The article will provide you with the steps to achieve this by automating the job using Azure AutomationPrinciple 'XYZ' could not be found or this principal type is not supported - Azure SQL DB and MI
In this blog article, we will be discussing the possible scenarios to get the error "Principle 'XYZ' could not be found or this principal type is not supported" while adding an AAD user or group to your Azure SQL database or Azure SQL managed instance.Azure SQL DB Private Link / Private Endpoint - Connectivity Troubleshooting
In the article Azure SQL DB Connectivity Troubleshooting we have explored regular connectivity troubleshooting to Azure SQL DB, on this one we will explore connectivity troubleshooting using Private Link In this article we are going to explore What is the Private Endpoint for Azure DB? Creation of Private Endpoint Azure VM > Private Link OnPrem VM > VPN (P2S) > Private Link (Hosts File) OnPrem VM > VPN (P2S) > Private Link (Custom DNS) Azure Function > VNET integration > Private Endpoint Failover Groups with Private Link 1 - What is the Private Endpoint for Azure DB? Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and Azure hosted customer-owned/partner services over a private endpoint in your virtual network. Traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to the public internet is no longer necessary Some important information Private Link service can be accessed from approved private endpoints in the same region. The private endpoint can be reached from the same virtual network, regionally peered VNets, globally peered VNets and on premises using private VPN or ExpressRoute connections. When creating a Private Link Service, a network interface is created for the lifecycle of the resource. This interface is not manageable by the customer. The Private Link Service must be deployed in the same region as the virtual network. A single Private Link Service can be accessed from multiple Private Endpoints belonging to different VNets, subscriptions and/or Active Directory tenants. The connection is established through a connection workflow. In the image, below we can see how the connection using private endpoint works. And as mentioned on the other article Azure SQL DB Connectivity Troubleshooting the Azure SQL DB clients by default will all go to the Shared public IP of the regional gateway. With the private endpoint you can close this public path and users can only connect from private endpoint. If you have multiple Azure VNETs you will need to use VNET peering or VNET VPN between two Azure VNETs, or P2S,S2S or Express Route to connect your onprem to Azure Network 2 - Creation of Private Endpoint To create a Private endpoint just follow up the procedure documented at https://docs.microsoft.com/en-us/azure/private-link/create-private-endpoint-portal You may also want to close all public network access to make sure all connection must flow from Private Endpoint. Just need to go to Azure SQL DB instance and "Firewall and Virtual Networks" and Deny public network access. Find below the procedure I've used to create a Private Endpoint Just need to go to "Private Endpoint Connections" and then add a Private endpoint Select the region that should be the same as the VNET region as mentioned above. Select the resource type "Microsoft.Sql/servers" for Azure SQL DB instance Select the Azure SQL DB instance you want to connect Select the VNET / Subnet. Notice also that during the creation you can already create a private DNS zone, that will work for Azure resources that uses the Azure DNS. We will talk more about that when doing the tests 3 - Azure VM > Private Endpoint From an Azure VM deployed to same VNET, if we test command below on command prompt before you create the Private Endpoint. nslookup fonsecanet-westeu.database.windows.net You will get result like below that shows that this server is using public gateway IP: 40.68.37.158. So NOT using any private IP Server: UnKnown Address: 168.63.129.16 Non-authoritative answer: Name: westeurope1-a.control.database.windows.net Address: 40.68.37.158 Aliases: fonsecanet-westeu.database.windows.net After you create the Private Endpoint using the same command above you are expected to see the results below Server: UnKnown Address: 168.63.129.16 Non-authoritative answer: Name: fonsecanet-westeu.privatelink.database.windows.net Address: 10.0.1.4 Aliases: fonsecanet-westeu.database.windows.net Be sure also to open outbound communication from Azure VM VNET to Private Endpoint on Local Firewall, Corporate Firewall, or Azure NSGs. For this test I've opened to allow all communication inside VNET. *Currently (Status on 2020-04-06) redirect is not supported, so only needed 1433 port You must use the FQDN to connect to Azure SQL DB as documented at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview#check-connectivity-using-sql-server-management-studio-ssms Use the Fully Qualified Domain Name (FQDN) of the server in connection strings for your clients. Any login attempts made directly to the IP address shall fail. This behavior is by design, since private endpoint routes traffic to the SQL Gateway in the region and the FQDN needs to be specified for logins to succeed. You can also check if connection is correct using TSQL below. And we will see the client IP is the private IP assigned to Azure VM. SELECT client_net_address FROM sys.dm_exec_connections where session_id = @@SPID You must use the FQDN to connect to Azure SQL DB. Azure SQL DB gateway use the name to route correctly your connection to the SQL host, when information is not provided it will fail If you try to connect using private endpoint IP you are going to get error like below =================================== Cannot connect to 10.0.1.4. =================================== A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (.Net SqlClient Data Provider) ------------------------------ Server Name: 10.0.1.4 Error Number: -2146893022 Severity: 20 State: 0 You should also NOT use DB.privatelink.database.windows.net =================================== Cannot connect to fonsecanet-westeu.privatelink.database.windows.net. =================================== A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (.Net SqlClient Data Provider) ------------------------------ Server Name: fonsecanet-westeu.privatelink.database.windows.net Error Number: -2146893022 Severity: 20 State: 0 4 - OnPrem VM > VPN (P2S) > Private Link (Hosts File) You can use some template like below to create the VPN Point to Site https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-configure-p2s https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-point-to-site-resource-manager-portal After creating the VPN, downloading and installing the VPN client and connecting to it If I check name resolution nslookup fonsecanet-westeu.database.windows.net We can see that it is still using Microsoft internal Corporate DNS where this VM is located Server: XXXXXXXXXXX.corp.microsoft.com Address: 10.221.xx.xx Non-authoritative answer: Name: westeurope1-a.control.database.windows.net Address: 40.68.37.158 Aliases: fonsecanet-westeu.database.windows.net fonsecanet-westeu.privatelink.database.windows.net For this scenario you will need to use your corporate DNS to have the name resolution https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview#dns-configuration When connecting to a private link resource using a fully qualified domain name (FQDN) as part of the connection string, it's important to correctly configure your DNS settings to resolve to the allocated private IP address. Existing Azure services might already have a DNS configuration to use when connecting over a public endpoint. This needs to be overridden to connect using your private endpoint. The network interface associated with the private endpoint contains the complete set of information required to configure your DNS, including FQDN and private IP addresses allocated for a given private link resource. You can use the following options to configure your DNS settings for private endpoints: Use the Host file (only recommended for testing). You can use the host file on a virtual machine to override the DNS. Use a private DNS zone. You can use private DNS zones to override the DNS resolution for a given private endpoint. A private DNS zone can be linked to your virtual network to resolve specific domains. Use your custom DNS server. You can use your own DNS server to override the DNS resolution for a given private link resource. If your DNS server is hosted on a virtual network, you can create a DNS forwarding rule to use a private DNS zone to simplify the configuration for all private link resources. Important It's not recommended to override a zone that is actively in use to resolve public endpoints. Connections to resources won't be able to resolve correctly without DNS forwarding to the public DNS. To avoid issues, create a different domain name or follow the suggested name for each service below. Private Link resource type Subresource Zone name SQL DB (Microsoft.Sql/servers) Sql Server (sqlServer) privatelink.database.windows.net Azure will create a canonical name DNS record (CNAME) on the public DNS to redirect the resolution to the suggested domain names. You'll be able to override the resolution with the private IP address of your private endpoints. Your applications don't need to change the connection URL. When attempting to resolve using a public DNS, the DNS server will now resolve to your private endpoints. The process does not impact your applications. For this test will use HOST file solution And we are able to connect fine. And as before you will be able to check you get the VPN ip range using TSQL below SELECT client_net_address FROM sys.dm_exec_connections where session_id = @@SPID 5 - OnPrem VM > VPN (P2S) > Private Link (Custom DNS) If you do not want to use local hosts file as mentioned above you can add Azure DNS (168.63.129.16) as a forwarder on your custom DNS For more information about more complex DNS relationships follow up the official documentation https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns On Azure Virtual Network settings I've replaced default Azure DNS with your custom DNS (On my tests 10.0.0.5). You can also add 168.63.129.16 (Azure DNS) as secondary DNS just in case 1st is off If your client lives in Azure, the DNS on VM can be a forwarder to Azure DNS - Go to DNS > Domain > Properties > Forwarders - Add Azure DNS (168.63.129.16) Do not forget to add virtual network link from Private Zone to the VNET where your Azure VM with DNS lives. If you are have onprem DNS you may need a Conditional forwarder Go to DNS > Domain > Conditional Forwarders > New DNS Domain should be database.windows.net. DO NOT USE privatelink.database.windows.net And you can check if name resolution works fine nslookup fonsecanet-westeu.database.windows.net Server: fonsecanetad.internal.cloudapp.net Address: 10.0.0.5 Non-authoritative answer: Name: fonsecanet-westeu.privatelink.database.windows.net Address: 10.0.1.4 Aliases: fonsecanet-westeu.database.windows.net If do not want to use forwarder you can also create a forward lookup zone and added manually the host to match the FQDN 6 - Azure Function > VNET integration > Private Endpoint To make Azure Function to connect to private endpoint you will need to use VNET integration https://docs.microsoft.com/en-us/azure/azure-functions/functions-create-vnet https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet *Status on (2020-04-27) There was some limitation on webapp + private DBS that are now gone and just need to set some configurations as documented at https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet#azure-dns-private-zones After your app integrates with your VNet, it uses the same DNS server that your VNet is configured with. By default, your app won't work with Azure DNS Private Zones. To work with Azure DNS Private Zones you need to add the following app settings: WEBSITE_DNS_SERVER with value 168.63.129.16 WEBSITE_VNET_ROUTE_ALL with value 1 These settings will send all of your outbound calls from your app into your VNet in addition to enabling your app to use Azure DNS private zones. And I've also created a sample Azure Function app that accept the connection string as parameter and was able to connect fine You can check the source code at https://github.com/FonsecaSergio/AzureFunctionAppTestConnectivity 7 - Failover Groups with Private Link For this scenario I will just reference link from Product Group article https://techcommunity.microsoft.com/t5/azure-sql-database/using-failover-groups-with-private-link-for-azure-sql-database/ba-p/1272902 Be sure to config private zone network link to both networks https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns#virtual-network-workloads-without-custom-dns-server This model can be extended to multiple peered virtual networks that are associated to the same private endpoint. This can be done by adding new virtual network links to the private DNS zone for all peered virtual networks. Result for my tests C:\Users\FonsecaSergio>nslookup SERVER1.database.windows.net Server: UnKnown Address: 168.63.129.16 Non-authoritative answer: Name: SERVER1.privatelink.database.windows.net Address: 12.1.1.6 Aliases: SERVER1.database.windows.net C:\Users\FonsecaSergio>nslookup SERVER2-westus.database.windows.net Server: UnKnown Address: 168.63.129.16 Non-authoritative answer: Name: SERVER2-westus.privatelink.database.windows.net Address: 11.0.1.4 Aliases: SERVER2-westus.database.windows.net C:\Users\FonsecaSergio>nslookup FAILOVERGROUP.database.windows.net Server: UnKnown Address: 168.63.129.16 Non-authoritative answer: Name: SERVER1.privatelink.database.windows.net Address: 12.1.1.6 Aliases: FAILOVERGROUP.database.windows.net SERVER1.database.windows.net For DNS on more complex environments, please follow up other scenarios as mentioned at https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns REF and LINKS https://docs.microsoft.com/en-us/azure/private-link/private-link-service-overview https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview https://docs.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview https://docs.microsoft.com/en-us/azure/private-link/ https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet#enable-vnet-integration https://channel9.msdn.com/Shows/Data-Exposed/Private-Link-for-Azure-SQL-Database-Part-1?term=Private%20Link%20for%20Azure%20SQL%20Database&lang-en=true https://channel9.msdn.com/Shows/Data-Exposed/Private-Link-for-Azure-SQL-Database-Part-2?term=Private%20Link%20for%20Azure%20SQL%20Database&lang-en=trueAzure SQL DB and TEMPDB usage tracking
Working on a case I got to scenario where customer may would like to better understand the TEMPDB usage and isolation between databases in an elastic pool. First speaking about isolation. Each DB even though they are below one logical instance, behind the scenes each Azure DB will leave on different node. So, each DB will have one isolated DB, except for Elastic Pool that share same hardware and share same TEMPDB. Even though there are some considerations to check reg isolation of data. More information below: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in tempdb and are scoped to the database level. Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same Azure SQL database. User sessions from other Azure SQL databases cannot access global temporary tables. For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL Managed Instance) supports the same temporary objects as does SQL Server. For Azure SQL Database single databases and elastic pools, only master database and tempdb database apply. For more information, see What is an Azure SQL Database server. For a discussion of tempdb in the context of Azure SQL Database single databases and elastic pools, see tempdb Database in Azure SQL Database single databases and elastic pools. For Azure SQL Managed Instance, all system databases apply. One way to test the isolation you can create a global temp table, like sample below. DROP TABLE IF EXISTS ##TEMP_COLUMNS GO SELECT * INTO ##TEMP_COLUMNS FROM sys.columns When trying to select from the global temp connected to another database you should get SELECT * FROM ##TEMP_COLUMNS Msg 208, Level 16, State 0, Line 1 Invalid object name '##TEMP_COLUMNS'. On Elastic pool also same rule applies. Even though they are sharing the same space used, global temp tables are scoped to database level. TEMPDB Space monitoring First you need to check what is your database SLO. The max space for each DB or Pool will depends on SLO for DB SELECT * FROM [sys].[database_service_objectives] DSO database_id edition service_objective elastic_pool_name ----------- ------- ----------------- ----------------- 8 Basic ElasticPool fonsecanetPool DTU Model https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#tempdb-database-in-sql-database vCore https://docs.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-single-databases Then you can use queries like sample below to monitor the TEMPDB usage -- Determining the Amount of Space Used / free SELECT [Source] = 'database_files' ,[TEMPDB_max_size_MB] = SUM(max_size) * 8 / 1027.0 ,[TEMPDB_current_size_MB] = SUM(size) * 8 / 1027.0 ,[FileCount] = COUNT(FILE_ID) FROM tempdb.sys.database_files WHERE type = 0 --ROWS SELECT [Source] = 'dm_db_file_space_usage' ,[free_space_MB] = SUM(U.unallocated_extent_page_count) * 8 / 1024.0 ,[used_space_MB] = SUM(U.internal_object_reserved_page_count + U.user_object_reserved_page_count + U.version_store_reserved_page_count) * 8 / 1024.0 ,[internal_object_space_MB] = SUM(U.internal_object_reserved_page_count) * 8 / 1024.0 ,[user_object_space_MB] = SUM(U.user_object_reserved_page_count) * 8 / 1024.0 ,[version_store_space_MB] = SUM(U.version_store_reserved_page_count) * 8 / 1024.0 FROM tempdb.sys.dm_db_file_space_usage U -- Obtaining the space consumed currently in each session SELECT [Source] = 'dm_db_session_space_usage' ,[session_id] = Su.session_id ,[login_name] = MAX(S.login_name) ,[database_id] = MAX(S.database_id) ,[database_name] = MAX(D.name) ,[elastic_pool_name] = MAX(DSO.elastic_pool_name) ,[internal_objects_alloc_page_count_MB] = SUM(internal_objects_alloc_page_count) * 8 / 1024.0 ,[user_objects_alloc_page_count_MB] = SUM(user_objects_alloc_page_count) * 8 / 1024.0 FROM tempdb.sys.dm_db_session_space_usage SU LEFT JOIN sys.dm_exec_sessions S ON SU.session_id = S.session_id LEFT JOIN sys.database_service_objectives DSO ON S.database_id = DSO.database_id LEFT JOIN sys.databases D ON S.database_id = D.database_id WHERE internal_objects_alloc_page_count + user_objects_alloc_page_count > 0 GROUP BY Su.session_id ORDER BY [user_objects_alloc_page_count_MB] desc, Su.session_id; -- Obtaining the space consumed in all currently running tasks in each session SELECT [Source] = 'dm_db_task_space_usage' ,[session_id] = SU.session_id ,[login_name] = MAX(S.login_name) ,[database_id] = MAX(S.database_id) ,[database_name] = MAX(D.name) ,[elastic_pool_name] = MAX(DSO.elastic_pool_name) ,[internal_objects_alloc_page_count_MB] = SUM(SU.internal_objects_alloc_page_count) * 8 / 1024.0 ,[user_objects_alloc_page_count_MB] = SUM(SU.user_objects_alloc_page_count) * 8 / 1024.0 FROM tempdb.sys.dm_db_task_space_usage SU LEFT JOIN sys.dm_exec_sessions S ON SU.session_id = S.session_id LEFT JOIN sys.database_service_objectives DSO ON S.database_id = DSO.database_id LEFT JOIN sys.databases D ON S.database_id = D.database_id WHERE internal_objects_alloc_page_count + user_objects_alloc_page_count > 0 GROUP BY SU.session_id ORDER BY [user_objects_alloc_page_count_MB] desc, session_id; https://github.com/FonsecaSergio/ScriptCollection/blob/master/SQL/AzureSQL%20-%20Monitor%20tempdb%20usage.sql In this test we can see this DB is part of Basic Elastic Pool. https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#tempdb-database-in-sql-database SLO Max tempdb Data File Size (GBs) # of tempdb data files Max tempdb data size (GB) Basic Elastic Pools (all DTU configurations) 13.9 12 166.7 We can see in the results above TEMPDB max size / current size and File count That depends on the DB Service level as mentioned above TEMPDB file space used where internal_object_reserved_page_count - Total number of pages in uniform extents allocated for internal objects in the file. Unused pages from an allocated extent are included in the count. There is no catalog view or dynamic management object that returns the page count of each internal object. Internal objects are only in tempdb. The following objects are included in the internal object page counters: Work tables for cursor or spool operations and temporary large object (LOB) storage Work files for operations such as a hash join Sort runs user_object_reserved_page_count - Total number of pages allocated from uniform extents for user objects in the database. Unused pages from an allocated extent are included in the count. You can use the total_pages column in the sys.allocation_units catalog view to return the reserved page count of each allocation unit in the user object. However, note that the total_pages column includes IAM pages. The following objects are included in the user object page counters: User-defined tables and indexes System tables and indexes Global temporary tables and indexes Local temporary tables and indexes Table variables Tables returned in the table-valued functions version_store_reserved_page_count - Total number of pages in the uniform extents allocated for the version store. Version store pages are never allocated from mixed extents. For more information, see sys.dm_tran_version_store (Transact-SQL). TEMPDB usage per session We can see session 79 is using around 79 Mb of TEMPDB for user objects TEMPDB usage in running task per session In this view we can see all user requests currently running that are consuming tempdb, this can be also useful to do some specific troubleshooting. In this image we can see some internal objects being currently used. Could be used for sorting / hash join / etc or some other internal operation. For this second test I created a global temp table in another DB in the pool, we can monitor the usage but by design we will miss some metadata like DB name as we cannot look on master.sys.databases and local user db sys.databases only includes master + current user db. With this we can see that all databases in the same elastic pool share same tempdb database space. Even though you still cannot access global temp table from other database in same pool as temp tables are scoped to database level. We can still connect to master db and check sys.databases manually to match database id to name SELECT database_id, name FROM sys.databases Transactions using TEMPDB You can also connect directly to user DB and check if there is any session ID that have a open transaction using TEMPDB. SELECT [Source] = 'database_transactions' ,[session_id] = ST.session_id ,[transaction_id] = ST.transaction_id ,[database_id] = DT.database_id ,[database_name] = CASE WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB' ELSE D.name END ,[database_transaction_log_used_Kb] = CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 ) ,[database_transaction_begin_time] = DT.database_transaction_begin_time ,[transaction_type_desc] = CASE database_transaction_type WHEN 1 THEN 'Read/write transaction' WHEN 2 THEN 'Read-only transaction' WHEN 3 THEN 'System transaction' WHEN 4 THEN 'Distributed transaction' END ,[transaction_state_desc] = CASE database_transaction_state WHEN 0 THEN 'The transaction has not been completely initialized yet' WHEN 1 THEN 'The transaction has been initialized but has not started' WHEN 2 THEN 'The transaction is active' WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions' WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place' WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.' WHEN 6 THEN 'The transaction has been committed' WHEN 7 THEN 'The transaction is being rolled back' WHEN 8 THEN 'The transaction has been rolled back' END FROM sys.dm_tran_database_transactions DT INNER JOIN sys.dm_tran_session_transactions ST ON DT.transaction_id = ST.transaction_id LEFT JOIN sys.databases D ON DT.database_id = D.database_id ORDER BY ST.session_id
