Modernizing large-scale Text-Search from legacy data systems to Azure SQL
Introduction Text-search is an essential aspect of contemporary data management, enabling users to effectively extract information from natural-language text. This technology allows for the retrieval of specific details from a text corpus, providing insights that would be unattainable through conventional search methods. Most modern databases include text-search capabilities by default, while legacy databases and data systems typically do not. In legacy systems, users may need to configure these features separately or use external services to implement text-search functions. SQL Server natively supports Full-Text search (and has been available from SQL Server 2005 onwards), which removes the requirement for additional component installations and thereby negates the need for any external data movement. The effectiveness of any feature is contingent upon proper design and implementation. For handling text-search on large volume tables and VLDBs (i.e. TBs or PBs of data), adhering to established techniques and best practices ensures optimal performance and manageability. This article will specifically focus on the performance considerations and optimization techniques required for implementing Full-Text Search functionality on Azure SQL Database. We will discuss a use case that demonstrates how to optimize full-text search innovatively to deliver quick and efficient results. Modernizing legacy workloads This article delves into the text search feature in Azure SQL and its detailed implementation for a specific use-case. This is a common scenario that customers may encounter during migration or modernization from legacy mainframe, midrange or on-premises x86 systems. For instance, certain legacy systems employ an OLTP database for standard searches and a separate server specifically catering to the text-search use case. This design necessitates regular data transfers between these two systems, potentially leading to instances where customers cannot access up-to-date information and must accept staleness in their data processing. There have been cases where text search requests were submitted to backend systems as separate queries, queued to run after business hours in batch jobs, with results being returned to the end-user on the following business day. Such implementations were common in legacy systems and continue to impact businesses today. When modernizing such workloads, customers can adopt and utilize native features like Full-Text Search which enables functional benefits to end-users like providing them with results in real-time and allowing them to focus more on their core business activities. Below table shows how long these searches take when implemented through different technologies. The table volume considered here is ~5 billion rows Technology Preferred Execution Type Feature utilized for Text-Search End-to-End Timeframe Legacy Systems Nightly Batch LIKE or External Text Search Server 1 business day Cloud Database Micro-batch LIKE ~ 20 mins Azure SQL DB Real-time Full Text Search <15 sec Note: The performance increase shown here is not a direct result of utilizing Full Text Search. Though Full Text Search increases the speed of querying such data, the increase in overall timing is a consequence of the system design and usage of multiple complementary SQL features that fit this use-case. Scenario Let's consider a use case where the SQL Database contains a substantially large table named CustomerInfo with billions of rows and terabytes of data. This table includes columns such as Customer Name, Address, State, CustInfo etc. Assume that Custinfo field encompasses free text / notes entered by customer service team for logging. Our use case involves retrieving rows from this Custinfo column based on a partial search criteria provided by the end-user through an application (i.e. web page, mobile app, etc.). For example, the query could be to extract all the rows where Custinfo field has notes stating that ‘mobile’ or ‘address’ is updated. The search functionality should retrieve all corresponding records, whether the match is exact or partial, and sort them according to their proximity to the provided key words. Implementing a standard search using the LIKE operator on such a large dataset would be inefficient, and constructing the logic for specific word searches on a free-text character column is complex to build and maintain. Full-Text Search for this use-case involves applying sound design principles, as well as leveraging SQL Server native features such as partitioning and indexed views, which enhance operational efficiency and manageability. Note: In context of this article, we are using Azure SQL DB Hyperscale, but implementation remains mostly the same for other SQL offerings as well. Technology Overview Table Partitioning involves dividing a large table into smaller, more manageable segments called "partitions". Instead of storing all data in one extensive table, the data is separated into several smaller tables, each containing a portion of the data. Detailed information on Azure SQL Hyperscale table partitioning, along with Best Practices & Recommendations, can be found in a two-part technical blog: Part 1 and Part 2. Indexed Views, also known as Materialized Views, is a SQL Server feature that can improve the performance of queries on large tables. Indexed views store the result set of a query physically on a disk, which allows for faster retrieval. Using Indexed views for Full-Text indexing can enhance the performance and maintainability of large datasets. Details on how to create an indexed view are provided here. Full Text Search is a robust feature that facilitates complex search on text data stored in SQL tables. This feature supports efficient querying in extensive text fields such as documents, articles, and product descriptions through a specialized index known as a Full Text Index. Full-Text Search uses advanced indexing and query functions to perform complex searches, such as phrase matching and linguistic analysis, with high accuracy and speed. It ranks results based on relevance, ensuring the most pertinent results appear at the top. Search results can be refined with logical operators (AND/OR/NOT) and word weights. The system supports rich query syntax for natural language queries and is compatible with multiple languages and character sets, making it suitable for global applications. A comprehensive overview and setup instructions for Full-Text Search can be found in the document here. Key concepts of FULL-TEXT Search are Full Text Catalog and Full-Text Index. Optimizing Full-Text Search in SQL Server This blog post explains how to implement Text Search by using a combination of different but complementary built-in features like Full-Text Search, Partitioning, and Indexed views. In this scenario, we are implementing the following techniques The base table is large and therefore partitioned into smaller chunks of data Each Indexed View will be built aligning to a single partition on the base table. Each Full Text Index will be built aligning to a single Indexed View. Full Text Catalogs will hold the relevant build information for the Full Text Indexes. Key Benefits in this architecture for VLDB: Provides a scalable model for growing data sizes. Partitioning the base table provides better manageability and optimized performance. Indexed views aligned with Partitioning Key allows quicker population of Indexed view. Having Full text catalog aligned with indexed view allows to rebuild / reorganize Full text catalog for specific subset. Full Text Index aligned with Indexed view provides manageable sized indexes and quicker crawling. Ability to rebuild / reorganize specific full-text indexes and improved query performance. Note: Full-Text index population is an asynchronous activity. For a given Index view / table, only one Full-Text index is allowed. With in a full-text index multiple columns can be included. Below diagram provides a representation of how we can implement Full-Text search on very large tables. Steps highlighted: 1 Partitioning base table. 2 Indexed views aligned with partition key. 3 Full-Text Catalogs. 4 Full-Text Indexes. Partitioning Choosing a partition key that evenly distributes data yields optimal results and is the most important parameter for partitioning. Partition switching/splitting divides a large partition into smaller ones when it grows over time. Here, if the partitioning column is STATE, the base table splits data based on STATE. Sample Table definition along with partitioning: --Partition Function Creation CREATE PARTITION FUNCTION [PF_STATE](varchar(2)) AS RANGE FOR VALUES ('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY') --Partition Scheme Creation CREATE PARTITION SCHEME [PS_STATE] AS PARTITION [PF_STATE] ALL TO ([PRIMARY]) --Table DDL with partition Key. CREATE TABLE [dbo].[CUSTOMERINFO]( [CUSTID] [int] NOT NULL, [CUSTNAME] [varchar](40), [ADDRESS] [char](60), [CITY] [char](20), STATE varchar(2), [ZIP] [char](10) , [COUNTRY] [char](15), CUSTINFO nvarchar(max) PRIMARY KEY CLUSTERED ( [CUSTID] ASC, STATE ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ) ON [PS_STATE](STATE); Indexed view To improve efficiency, multiple indexed views are created and aligned with the partition key column. Indexed views are schema-bound and store the results of the view’s query, so the results are precomputed and stored on disk. For large tables, creating an indexed view that aligns with the partitioned column helps to quickly populate the indexed view and aids in updating the views when the base table changes. Multiple indexed views enable the creation of multiple full-text indexes, optimizing the search process. Note: Indexed view should have an unique index so this can be utilized during full-text index creation. Sample Indexed views along with clustered index: -- Create Indexed view for STATE AL and clustered index which is subsequently used for FullText Index. CREATE VIEW [dbo].[v_CUSTOMERINFO_AL] WITH SCHEMABINDING AS select CUSTID ,CUSTNAME,ADDRESS,CITY,STATE,ZIP,COUNTRY,CUSTINFO From dbo.CUSTOMERINFO where STATE = 'AL' ; GO CREATE UNIQUE CLUSTERED INDEX [CX_CUSTOMERINFO_AL] ON [dbo].[v_CUSTOMERINFO_AL] ( CUSTID ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO -- Create Indexed view for STATE AK and clustered index which is subsequently used for FullText Index. CREATE VIEW [dbo].[v_CUSTOMERINFO_AK] WITH SCHEMABINDING AS select CUSTID ,CUSTNAME,ADDRESS,CITY,STATE,ZIP,COUNTRY,CUSTINFO From dbo.CUSTOMERINFO where STATE = 'AK' ; GO CREATE UNIQUE CLUSTERED INDEX [CX_CUSTOMERINFO_AK] ON [dbo].[v_CUSTOMERINFO_AK] ( CUSTID ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO Full-Text Search Full-Text catalog A Full-Text catalog is a logical container for full-text indexes, independent of any table. One catalog can be used for indexes on different tables. For databases with large or growing text data, use multiple catalogs. Rebuild the Full-Text catalog after significant data changes (bulk inserts, large updates) to improve performance and reduce index fragmentation. Include this rebuild in regular database maintenance activities. Sample Full Text Catalog: --FullText Catalog for STATE AL and AK. CREATE FULLTEXT CATALOG CT_CUSTOMERINFO_AL AS DEFAULT; CREATE FULLTEXT CATALOG CT_CUSTOMERINFO_AK AS DEFAULT; To change properties of Full-Text Catalog like REORGANIZE and REBUILD provided here Full-Text Index A table or indexed view can only have one full-text index, which can cover multiple columns. These indexes are linked to a unique index of the base table and attached to a full-text catalog. They can be created on char-based data types and XML. The process of creating and maintaining full-text indexes is called population or crawl, which is asynchronous and updates with changes to the underlying table or view and can be maintained with the "Change_Tracking" property. For large tables, aligning full-text indexes with indexed views allows for multiple manageable indexes, improving performance and speeding up data updates. Ensure high I/O file groups are allocated for full-text indexes in SQL Server installations. Fulltext index comes with an option of adding & removing columns as well as enabling or disabling it as needed. Note: During Bulk data load (or other unique scenarios), FULL-TEXT index population (i.e. Change_Tracking) can be set to Manual / OFF and post load activity, Populate Full-Text Index and reset Change_Tracking to AUTO. Sample Full Text Index: --Full Text Index aligned to Clustered Index on Indexed view and Full-Text Catalog. CREATE FULLTEXT INDEX ON [dbo].[v_CUSTOMERINFO_AL](CUSTINFO) KEY INDEX [CX_CUSTOMERINFO_AL] ON CT_CUSTOMERINFO_AL WITH CHANGE_TRACKING = AUTO; CREATE FULLTEXT INDEX ON [dbo].[v_CUSTOMERINFO_AK](CUSTINFO) KEY INDEX [CX_CUSTOMERINFO_AK] ON CT_CUSTOMERINFO_AK WITH CHANGE_TRACKING = AUTO Full-Text Index properties that can be changed are listed here. Sample queries using Full-Text search Full Text samples with contains and more examples are here Sample 1: Contains with OR --To Identify the CUSTINFO column which has words ‘Mobile’ Or ‘Address’. select * from dbo.[v_CUSTOMERINFO_AL] where contains (CUSTINFO,'Mobile or Address') Sample 2: Contains with AND --To Identify the CUSTINFO column which has words ‘Mobile’ AND ‘Address’. select CUSTID,CUSTNAME,CUSTINFO from dbo.[v_CUSTOMERINFO_AL] where contains (CUSTINFO,'Mobile and Address') Details about Ranking are in Full-Text search with Ranking Note: While SQL optimizer selects best execution plan for a query, for very specific and unique scenarios Hints also can be used to optimize further. In this specific use-case, Indexed View Matching functionality can be implemented using the NOEXPAND Table Hint which drastically improves performance. By adding this hint, the query optimizer uses the index on the view if a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query. Please perform due diligence prior to using Hints. Full-Text Index Maintenance and validation Types of Full-Text Index population. How to Improve the Performance of Full-Text indexes Sample Queries that helps in Full Text Maintenance and validation: --How to check Full Text index population status SELECT object_name(object_id) as tablename ,change_tracking_state_desc, has_crawl_completed,crawl_type_desc,crawl_start_date,crawl_end_date FROM sys.fulltext_indexes where object_name(object_id) like 'v_CUSTOMERINFO%' Additional Information If more advanced AI based search capabilities are needed, Azure provides solutions like Azure AI Search. These are different offerings from Azure SQL and must be deployed separately and integrated appropriately. If the use-case involves Vector search, Azure SQL DB can work with vectors and details are here. As the SQL Server Full Text functionality keeps advancing, there are specific features that get deprecated. These can be found here. When building new applications using Full Text, we recommend that this list is being considered for future proofing your design. Feedback and suggestions If you have feedback or suggestions for improving this data migration asset, please contact the Databases SQL Ninja Engineering Team (datasqlninja@microsoft.com). Thanks for your support! Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.
Seamless Cross-Tenant Migration of Azure SQL Databases without Modifying Connection Strings
Transferring an Azure SQL Database between tenants involves several steps, but it can be done efficiently without changing your connection strings. There are several reasons why an organization might need to migrate Azure Entra tenants. These can include organizational restructuring, mergers and acquisitions, compliance requirements, or the need to consolidate resources for better management and cost efficiency. This guide outlines the process in a clear and concise manner. High-Level Overview The migration process involves the following key steps: Create a Temporary Subscription in the Source Tenant: This is the initial step where you set up a new subscription in the source tenant. Move Resources to the Temporary Subscription: Use Azure Resource Mover to transfer the Azure SQL Server and Database to the new subscription. Transfer the Subscription to the Target Entra Tenant: This involves transferring the billing ownership of the subscription to the target tenant. Accept the Transfer at the Target Tenant: The target tenant accepts the transfer and completes the necessary verification steps. Move Resources to the Target Subscription in the Target Tenant: Finally, move the resources to the desired subscription within the target tenant. Detailed Steps Create a Temporary Subscription in the Source Tenant Navigate to the Azure Portal and create a new subscription (i.e. Pay-As-You-Go) if necessary. Enter your payment information and complete the setup. Move Resources to the Temporary Subscription Open Azure Resource Mover and select "Move across subscription". Create a new Resource Group and add the resources you want to move. Select Add resources and select your SQL Server and SQL Database resources you want to move Verify the resources and initiate the move. Note: This can take a few minutes. Transfer the Subscription to the Target Entra Tenant Select the Pay-As-You-Go subscription and transfer billing ownership to the target tenant's administrator. Enter the recipient's email address Important: Select 'Move subscription tenant' toggle to 'Yes' The source administrator will receive an email to confirm the transfer request. Accept the Transfer at the Target Tenant The target tenant's administrator accepts the transfer and completes the verification process. Once the transfer is complete, the subscription will appear in the target tenant. Move Resources to the Target Subscription in the Target Tenant Use Azure Resource Mover to transfer the Azure SQL Server and Database to the desired subscription within the target tenant. Verify the resources and complete the move. Post-Transfer Tasks After the migration, there are a few additional steps and best practices that should be followed to ensure everything is set up correctly: Take Ownership of the Server in the New Tenant: Set the desired Entra ID for the administrator of the SQL Server. Configure Security and Access: Set up necessary security controls and drop any old Entra IDs. Review Firewall Rules: Ensure that firewall rules and any Azure Key Vault configurations are correct. Test the Database: Perform thorough testing to ensure all data and functionalities are intact. Downtime Planning During the migration process, it's crucial to plan for minimal downtime. In our testing, we used an application that continuously inserted records into the database, and no loss of connection occurred throughout the transfer. Little to no downtime can be achieved in this process but only when using SQL Authentication. Because the Entra tenant is changing, any logins based on that tenant will cease to work once the database is moved. Conclusion Business conditions often change and can require the movement of Azure SQL Databases across Entra tenants. While this process can often seem daunting, following this can be straightforward if you follow the steps outlined in this guide. Remember to thoroughly test the database after the transfer and update all necessary configurations and security rules to maintain seamless operations. Feedback If you have feedback or suggestions for improving this data migration asset, please comment here or contact the Databases SQL Customer Success Engineering Team (datasqlninja@microsoft.com) directly. Thanks for your support! Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide. Happy Migrating!Enhancing SQL Server Transactional Replication Initialization Performance
Initializing SQL Server Transactional Replication with default settings can be significantly slow when working with large table migrations. Increasing MaxBCPThreads allows SQL Server to perform multiple tasks concurrently, optimizing hardware utilization and greatly reducing the time needed to synchronize data between the publisher and subscribers. In some cases, tuning this parameter has cut initialization time by up to 75%.Optimizing Query Store Memory Usage for Ad hoc Workloads
Introduction Query store (QDS) is great at collecting performance tuning telemetry, and to achieve this it uses resources including disk and memory. While the best practices documentation looks at the QDS configuration for adhoc workloads at the general level, one of the key areas for memory usage is the MEMORYCLERK_QUERYDISKSTORE_HASHMAP clerk which can be significant for adhoc workloads, but there are ways to tune this usage. MEMORYCLERK_QUERYDISKSTORE_HASHMAP QDS tracks queries and their plans, and to identify these, it creates a hash for each query, (based on the combination of statement_sql_handle, batch_sql_handle, object_id, query_parameterization_type, and context_settings_id), and keeps these hashes in memory using the QDS HASHMAP clerk, to efficiently work with high volumes of queries. This means that in general, as the number of queries being tracked for a database’s workload increases, the size of this memory clerk goes up. This is exacerbated with multiple databases either on the same SQL instance or elastic pool, since these all share the same QDS hashmap clerk; QDS has memory limits (seen in the readonly_reason in sys.database_query_store_options (Transact-SQL) - SQL Server | Microsoft Learn) both per-database and per instance or elastic pool, but especially in dense elastic pools this can be a significant amount of memory. While the query store documentation https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver16#Parameterize covers the recommendations to avoid adhoc queries and use parameterization, sometimes life isn’t that simple, and you need to optimize this memory usage. Two groups of queries are tracked; Undecided queries Persisted queries Persisted queries These are the queries that have met the thresholds, and QDS is recording the execution and waits for them. Control the overall number of distinct persisted queries using the QDS settings: - Raising the thresholds (execution count, compile CPU and execution CPU) filters out more queries; bear in mind that collecting this telemetry is only useful if you are using it to monitor and tune the workload. - Reducing the stale query threshold (default is 30 days); however, if the workload has the same set of queries running each day or week, then reducing this may not reduce the total number of distinct queries very much. To customize these threshold, use the custom capture policy; the auto policy values are 30 executions, 1 seconds of compile CPU and 0.1 second of execution CPU in a day (https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver16#g-enable-the-query-store-with-custom-capture-policy-options ). Note that if you never use the query store telemetry, you can set the capture mode to None, so that no new queries will be persisted. Undecided queries To be able to work out whether a query has met the CPU and execution thresholds, QDS calculates the hash, puts this in the hashmap clerk, and tracks the CPU and executions (but not the more detailed runtime stats telemetry). Once a query meets one of the thresholds, it starts being tracked, and its hash stays in the hashmap; if it doesn’t meet any of the thresholds in the threshold period, it is taken out of the hashmap, but is put back in the next time it runs, starting its undecided journey again. This means that with the AUTO capture policy, every query that has run in the last day has its hash in the hashmap, so for an adhoc workload with relatively few queries reaching the thresholds, these undecided hashes can make up the majority of the hashmap. To reduce the number of hashes kept, change the STALE_CAPTURE_POLICY_THRESHOLD. The example below shows this. With the default of 24 hours, all of the unique hashes stay in memory, with a final clerk size of 803 MB; changing this to 1 hour, stays at around the 200 MB mark, with QDS evaluating the list of undecided queries every hour, and removing most of these since they haven’t met the thresholds. Changing STALE_CAPTURE_POLICY_THRESHOLD This demonstrates the impact of changing this QDS setting; at 1 hour (the first set of results), the hashmap memory fluctuates, while with the default of 24 hours, it steadily increases. ALTER DATABASE [qds_test] SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = (STALE_CAPTURE_POLICY_THRESHOLD = 1 HOURS, TOTAL_EXECUTION_CPU_TIME_MS = 1000000)) Run details: stale threshold is 1, hour 0, Hashmap MB 131, waiting for 00:36 Run details: stale threshold is 1, hour 1, Hashmap MB 229, waiting for 00:36 Run details: stale threshold is 1, hour 2, Hashmap MB 179, waiting for 00:36 Run details: stale threshold is 1, hour 3, Hashmap MB 276, waiting for 00:36 Run details: stale threshold is 1, hour 4, Hashmap MB 218, waiting for 00:37 Run details: stale threshold is 1, hour 5, Hashmap MB 158, waiting for 00:35 ALTER DATABASE [qds_test] SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = (STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, TOTAL_EXECUTION_CPU_TIME_MS = 1000000)) Run details: stale threshold is 24, hour 0, Hashmap MB 131, waiting for 00:36 Run details: stale threshold is 24, hour 1, Hashmap MB 255, waiting for 00:36 Run details: stale threshold is 24, hour 2, Hashmap MB 405, waiting for 00:34 Run details: stale threshold is 24, hour 3, Hashmap MB 502, waiting for 00:35 Run details: stale threshold is 24, hour 4, Hashmap MB 600, waiting for 00:37 Run details: stale threshold is 24, hour 5, Hashmap MB 803, waiting for 00:38 The code for this example is below; there is an inner loop which runs one hundred thousand adhoc queries once an hour for 6 hours, and this is repeated with the threshold set to 1 and 24 hours. This was run on a single Azure SQL database with the capture CPU threshold set very high so that the only hashmap memory being used is from the undecided queries in this database. set nocount on declare @stale_hours INT = 1 declare @hours_lcv INT declare @tsql nvarchar(300) declare @starttime datetime declare @delay_length varchar(8) declare @hashmap int declare Max_queries INT = 100000 declare Max_hours INT = 6 declare @lcv INT -- loop; 1st iteration setting the stale threshold to one hour, the second to 24 hours while @stale_hours < 25 begin -- clear the results from the previous run, and set the stale threshold ALTER DATABASE [qds_test] SET QUERY_STORE CLEAR set @tsql = 'ALTER DATABASE [qds_test] SET QUERY_STORE (OPERATION_MODE = READ_WRITE,QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = (STALE_CAPTURE_POLICY_THRESHOLD = ' + cast(@stale_hours AS nvarchar(3)) + ' HOURS,TOTAL_EXECUTION_CPU_TIME_MS = 1000000))' select @tsql exec sp_executesql @tsql set @hours_lcv = 0 -- repeat the hashmap bloat for a number of hours; wait after the inner loop until the hour is up while @hours_lcv < Max_hours begin set @lcv = 1 set @starttime = getdate() -- generate adhoc queries to bloat the QDS hash map while (@lcv < Max_queries) begin set @tsql = 'select null as nothinghere into #null from sys.databases where 1=2 and database_id = ' + cast((@hours_lcv * Max_queries) + @lcv as nvarchar(10)) exec sp_executesql @tsql set @lcv += 1 end SELECT @hashmap = SUM(pages_kb) / 1024 FROM sys.dm_os_memory_clerks WHERE [type] = 'MEMORYCLERK_QUERYDISKSTORE_HASHMAP' set @delay_length = '00:' + right('00' + cast(59 - least(59,datediff(minute,@starttime,getdate())) as varchar(2)),2) select 'Run details: stale threshold is ' + cast(@stale_hours AS varchar(2)) + ', hour ' + cast(@hours_lcv as varchar(2)) + ', Hashmap MB ' + cast(@hashmap as varchar(10)) + ', waiting for ' + @delay_length -- wait for the rest of the hour waitfor delay @delay_length set @hours_lcv += 1 end set @stale_hours += 23 end Conclusion For workloads where you are using QDS, but the adhoc nature of the workload isn’t able to be addressed, reducing the stale capture threshold can significantly reduce the memory that QDS uses. Feedback and suggestions If you have feedback or suggestions for improving this data migration asset, please contact the Databases SQL Ninja Engineering Team (datasqlninja@microsoft.com). Thanks for your support! Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.
TempDB Size Considerations to Ensure Successful SQL VM Deployments from Marketplace Images
Deploying a SQL VM using a marketplace image can sometimes lead to deployment failures, particularly when the selected size for the tempdb exceeds the capacity of the Windows OS disk. This issue arises even if the intention is to host the tempdb file on a separate, non-OS drive. Understanding the limitation detail is crucial for a seamless deployment process and preventing avoidable errors. With careful planning and configuration, you can resolve this challenge and ensure your SQL VM deployment completes successfully. Scenario: When deploying a SQL Virtual Machine (VM) using a marketplace image using following configuration. Local Storage Available for this VM Size: 300 GB on the D drive OS Disk Size Selected: 127GB Selected Tempdb Size: 180 GB (8 data files of 20 GB each, totaling 160 GB, plus a 20 GB log file) Validation passes However, the deployment will fail Cause This issue occurs because the overall size of the TempDB files surpasses the maximum allocated size for the OS disk, which is limited to 127 GB. As a result, the deployment cannot proceed as intended, even though the local storage on the D drive appears sufficient. The below error can be seen in the operation details above for the failure: { "status": "Failed", "error": { "code": "Ext_StorageConfigurationSettings_ApplyNewTempDbSettingsError", "message": "Error: 'Status(StatusCode=\"DeadlineExceeded\", Detail=\"Deadline Exceeded\")'" } } Workaround To resolve this issue, deploy a smaller tempdb size initially and then increase it post-deployment: Initial Deployment: Set the initial size to 1 GB each for 8 data files (totaling 8 GB) and a 1 GB log file, making a total of 9 GB. Post-Deployment: Use the following T-SQL commands to increase the tempdb size to the desired size (e.g., 20 GB): USE [master] GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp5', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp6', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp7', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp8', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20971520KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 20971520KB ) GO Conclusion To avoid deployment failures related to exceeding the OS disk size, start by deploying with a smaller TempDB size and increase it after deployment. Always ensure the TempDB size is within the allocated disk limits during the initial deployment to prevent errors. This article uses the Azure portal for the deployment example. If you're using other methods such as Azure CLI, ARM templates, or PowerShell, make sure to follow the appropriate steps for setting the TempDB size. While the specific process may vary, the key idea is to ensure the initial TempDB size stays within the available OS disk space to avoid deployment failures.Oracle to Azure SQL Post Migration Optimization: Handling Oracle Function-Based Indexes in Azure SQL
Why Post-Migration Optimization Matters For customers migrating from Oracle to Azure SQL Databases, understanding Azure SQL optimization techniques can make a significant difference in overall database performance. During the Application Stress Testing phase, it is essential to identify query patterns, evaluate index usage, and refine queries to prevent performance bottlenecks and ensure system efficiency. While Azure SQL databases Intelligent Query Processing (IQP) can significantly improve performance with minimal intervention, certain complex scenarios may still require manual query rewrites to achieve the desired level of performance. Leveraging SARGable Queries in SQL Server One such optimization technique involves ensuring that queries in SQL Server are SARGable (Search ARGument-able). SARGable queries enable the SQL Engine to efficiently leverage indexes, significantly reducing resource consumption and execution time. For instance, small adjustments such as replacing column transformations with range-based conditions, creating computed columns, and adding required indexes on computed columns can have a significant impact on query performance. Metric Non-SARGable Query SARGable Query Execution Plan Index Scan (usually less efficient) Index Seek (usually more efficient) IO Cost High (Reads Many Pages) Low (Reads Fewer Pages) CPU Time Higher (Function Evaluation for Rows) Lower (Direct Use of Index) Logical Reads High (entire index/table scanned) Low (only relevant index pages accessed) Handling Function-Based Indexes: Oracle vs SQL Server Oracle: In Oracle, function-based indexes allow indexing on the result of a function or expression. This enables efficient query execution when functions or expressions are used in WHERE or JOIN conditions. The TRUNC(APPLN_START_DT) function is precomputed and stored in the Index. Queries can get the value of the expression from the index instead of computing it. The following is an example of this in practice: -- Create a function-based index on the TRUNC(APPLN_START_DT) expression CREATE INDEX idx_appln_start_dt ON appln_data ( trunc(appln_start_dt) ); -- Filter rows where the year of APPLN_START_DT matches the current year. SELECT * FROM appln_data WHERE trunc(appln_start_dt, 'YYYY') = trunc(sysdate, 'YYYY'); SQL Server: SQL Server takes a slightly different approach to handling Indexes and Query Optimization, particularly when working with large datasets or tables containing millions of rows. The SQL optimizer excels when queries are SARGable (Search ARGument-able), meaning the predicates are written to allow direct index utilization without applying transformations to columns. Let’s analyze this with a simple and practical example to see how query design impacts performance, resource usage, and execution efficiency. Let's analyze two versions of a query on a table [APPLN_DATA] with 50 million rows to understand how query formulation impacts performance, resource utilization, and execution efficiency. Comparing SARGable and Non-SARGable Queries Non-SARGable Query: -- Filter rows where the year of APPLN_START_DT matches the current year. SELECT * FROM [dbo].[APPLN_DATA] WHERE YEAR(APPLN_START_DT) = YEAR(GETDATE()); Execution Plan: Index Scan. Logical Reads: 928,804 CPU Time: 7,562 ms Physical Reads: 2 Elapsed Time: 63,130 ms Execution Plan: Why It's Inefficient: The YEAR() function transforms the APPLN_START_DT column, which prevents the optimizer from performing an Index Seek and forces an Index Scan. The execution plan confirms this by showing an Index Scan, where SQL Server reads through all rows instead of directly seeking the relevant ones using the index. This behavior increases resource consumption, including logical reads, CPU usage, I/O costs, and overall query execution time. SARGable Query: SELECT * FROM [dbo].[APPLN_DATA] WHERE APPLN_START_DT >= CAST(YEAR(GETDATE()) AS CHAR(4)) + '-01-01' AND APPLN_START_DT < CAST(YEAR(GETDATE()) + 1 AS CHAR(4)) + '-01-01'; Execution Plan: Index Seek. Logical Reads: 173,041 CPU Time: 3,547 ms Elapsed Time: 52,092 ms Execution Plan: Why It's Better: The query uses range-based conditions (>= and <) directly on the APPLN_START_DT column without applying any functions. This makes the query SARGable, allowing SQL Server to efficiently perform an Index Seek, as shown in the execution plan. An Index Seek ensures that SQL Server retrieves only the relevant rows, significantly reducing logical reads, CPU usage, and overall query execution time. Understanding the Key Differences and Impact of SARGable and Non-SARGable Queries Aspect Non-SARGable SARGable Query Example WHERE YEAR(APPLN_START_DT) = YEAR(GETDATE()) WHERE APPLN_START_DT >= '2024-01-01' Index Usage Index Scan (Due to YEAR() function - inefficient) Index Seek (efficient) Logical Reads High (entire index/table scanned) Low (only relevant index pages accessed) CPU Time High Low Elapsed Time Longer Shorter Query Optimization Applies function on the column Uses range conditions without transformations Additional Optimization Techniques: A Computed column with an index was not used here because the query retrieves rows for an entire year, making range-based conditions more efficient. Computed indexes are more suitable for scenarios with specific transformations on smaller subsets of data, such as Calculations and Filtering for a particular month or day. In our example, we use SELECT * for simplicity, but in real-world workloads, queries typically retrieve specific columns based on application requirements. Selecting only the required columns reduces I/O and improves query performance. Additionally, Covering Indexes (indexes that include all queried columns) should be considered to eliminate Key Lookups, further enhancing performance for frequently accessed queries. If your table has billions of rows, even efficient Index Seeks may take considerable time. Filtered Indexes, Indexed Views and Partitioning the table based on a logical key (e.g., date ranges) can help improve query performance further. Key Takeaways for a successful Heterogenous Migration: Oracle's function-based indexes can be adapted in SQL Server using Range-based Conditions, Computed Columns with Indexes, Indexed Views, or Application-level Optimizations. Aim to write SARGable queries to leverage SQL Server's indexing capabilities effectively. Avoid using Functions in Query Predicates. Evaluate and consider the use of Indexed Views and Filtered Indexes. It is crucial to iteratively analyze and Update Index Statistics, Evaluate Index Access patterns, Review Histograms, monitor Query Plans and Fragmentation, Rebuilding Indexes during the App Stress Testing phase to achieve optimal performance. Collaborate with application developers to refactor query logic when needed. References: Intelligent query processing details - SQL Server | Microsoft Learn Create indexed views - SQL Server | Microsoft Learn Create filtered indexes - SQL Server | Microsoft Learn Migrating Oracle Databases to SQL Server (OracleToSQL) - SQL Server | Microsoft Learn Automatic tuning - SQL Server | Microsoft Learn Feedback and suggestions If you have feedback or suggestions for improving this data migration asset, please contact the Databases SQL Ninja Engineering Team (datasqlninja@microsoft.com). Thanks for your support! Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.Azure SQL Managed Instance – Sync Agent Jobs and Logins in Failover Groups
In an Azure SQL Managed Instance setup with Failover Group, the schema, data, and database-level users will always be synced between primary and secondary instances. As of October 2021, there is no sync mechanism for SQL Server Agent Jobs or Server Logins/Roles because the functionality of replicating system databases does not exist. This document provides guidance and scripts to implement a workaround solution to have the Agent Jobs and Server Logins/Roles synced between primary and secondary instances.
Enhancing SQL PaaS Security: Automating Azure Blob SAS Key Rotation Using Azure Runbooks
In today’s cloud environment, security and efficient management of storage resources are paramount for organizations. Azure Blob Storage, known for its scalability and robust security, is a popular solution for handling large volumes of unstructured data. However, ensuring secure access to this data requires careful management, particularly when it comes to Shared Access Signatures (SAS) keys, which provide time-limited access to storage resources. To mitigate the risk of unauthorized access, it’s essential to rotate SAS keys regularly. Automating this process not only enhances security by ensuring the timely retirement of outdated keys but also guarantees the secure creation and updating of new keys. In this guide, we’ll show you how to use Azure Automation PowerShell Runbooks to automate the rotation of Azure Blob Storage SAS keys. This solution also covers updating external user credentials used in SQL to access the blob storage, simplifying access management while reducing manual intervention and the potential for human error. By adopting this automated approach, organizations can strengthen their data security practices, align with key management best practices, and ensure uninterrupted, secure access for users—all while minimizing administrative effort. This article will walk you through the process of automating the rotation of Azure Blob Storage SAS keys using Azure Automation PowerShell Runbooks. You’ll learn how to configure the necessary resources and set up permissions to ensure smooth execution. Note: This article uses SQL Managed Instance as an example to outline the steps in detail, which can also be applied to Azure SQL Database for implementation. Prerequisites Before proceeding with the implementation, ensure you meet the following prerequisites: An active Azure subscription. Azure Automation Account with sufficient privileges to perform administrative tasks. Access to an Azure Storage Account, Azure Key Vault, and Azure SQL Managed Instances that will be involved in the automation process. Summary Configure Azure Automation Account Set up an Automation Account in Azure and import required modules for integration. Grant Required Permissions Assign the necessary roles to the Automation Account to enable access to Azure Key Vault, Storage Accounts, and SQL Managed Instances. Create Stored Procedure in SQL Managed Instance Implement a stored procedure to facilitate database credential updates. Create Login and Grant Permissions Create a login for the Automation Account and grant the required permissions to execute operations in the SQL Managed Instance. Setup Documentation Configure Azure Automation Account To start, configure an Azure Automation Account to run the automation scripts and manage resources. Create an Azure Automation Account: Go to the Azure portal and create a new Azure Automation Account. This account will host the PowerShell Runbooks that will automate the SAS key rotation. Import the SQL Server Module: Once the Automation Account is created, import the SQLServer module into the Automation Account. This module allows the Runbook to interact with SQL Managed Instances and perform necessary database operations. a) Go to the Azure Portal and navigate to your Automation Account. b) Select "Modules" under the "Shared Resources" section in the Automation Account. c) Click on "Browse Gallery", and search for "SQLServer" in the gallery. d) Select the "SQLServer" module and click Import. e) Wait for the import to complete and verify the module appears under Installed Modules. Runbook Setup Create a new Runbook, select PowerShell as the type, choose the recommended runtime version, and enter the script code shared (SASKeyRotationAutomationScript.ps1) below as the content. Grant Required Permissions Ensure that the Automation Account has the appropriate permissions to interact with dependent resources such as Azure Key Vault, Storage Account, and SQL Managed Instances. Key Vault: Assign the "Key Vault Secrets Officer" role to the Automation Account on the Key Vault. This role allows the account to manage secrets in the Key Vault, which will be necessary for storing SAS keys. Storage Account: Assign the "Storage Account Key Operator Service Role" to the Automation Account for the relevant Storage Account. This role enables the Automation Account to manage keys for the Storage Account, including generating new SAS tokens. SQL Managed Instances: Assign the "Reader" role to the Automation Account on each SQL Managed Instance. This role allows the Automation Account to read database configurations and settings required for the SAS key update process. Subscription Level: Assign the "Reader" role at the subscription level to ensure that the Automation Account can access all the resources it needs across the subscription. Create Stored Procedure in SQL Managed Instance Next, create a stored procedure in the SQL Managed Instance that will handle updates to the database credentials, as part of the SAS key rotation process. Create the Stored Procedure: Implement the stored procedure UpdateDatabaseScopedCredential.sql within your SQL Managed Instance to manage database credential updates. This procedure will be invoked by the Automation Account during the execution of the Runbook. Create Login and Grant Permissions Create a login for the Automation Account within the SQL Managed Instance and assign the necessary permissions to execute the stored procedure and other required tasks. Execute the Following SQL Commands: USE [master] GO CREATE LOGIN [AutomationAccount] FROM EXTERNAL PROVIDER; GO USE [DBA] GO CREATE USER [AutomationAccount] FOR LOGIN [AutomationAccount]; GRANT EXECUTE ON dbo.UpdateDatabaseScopedCredential TO [AutomationAccount]; GRANT CREATE TABLE TO [AutomationAccount]; Explanation: The above SQL commands will create a login for the Automation Account from an external provider (likely Azure AD), map it to a user, and grant the required permissions to execute the stored procedure and create tables, if necessary, during the process. With these steps completed, your Azure environment will be ready to automate the SAS key rotation and securely manage credentials through Azure Automation. Next, manually run the Runbook to test its functionality, ensuring that it performs as expected. After confirming the successful execution, schedule the Runbook according to your requirements. PowerShell Script File for SAS Key Rotation SASKeyRotationAutomationScript.ps1 #________________________________ #Input parameters #________________________________ $subscriptionId =xxxxx-459a-409f-8339-xxxxxxxx $resourceGroupName = "ProdRG" $storageAccountName = "Prodstorageaccount" $keyVaultName = "Prodkeyvault" $secretName = "SASForHRTK" $SASexpiryDate =90 # days $automationAccountName = "AutomationAccount" $global:NewSASKey=""; $database ="master" Connect-AzAccount -Identity Set-AzContext -subscriptionId $subscriptionId | Out-Null *>$null # Retrieve SQL Managed Instances and construct DNS names $sqlInstances = Get-AzSqlInstance | Select-Object ManagedInstanceName, DnsZone $SQLMIList = $sqlInstances | ForEach-Object { $dnsName = "$($_.ManagedInstanceName).public.$($_.DnsZone).database.windows.net,3342" [PSCustomObject]@{ DNSName = $dnsName } } #_______________________________________________________________ # Variables used #_______________________________________________________________ Write-Output "[$(Get-Date)]: __________________ Beginning of SAS Key Rotation Script __________________" Write-Output "[$(Get-Date)]: Input Parameters for Execution:" # Add input parameters Write-Output "`n[$(Get-Date)]: Subscription ID : $subscriptionId" Write-Output "[$(Get-Date)]: Resource Group Name : $resourceGroupName" Write-Output "[$(Get-Date)]: Storage Account Name : $storageAccountName" Write-Output "[$(Get-Date)]: Key Vault Name : $keyVaultName" Write-Output "[$(Get-Date)]: Secret Name : $secretName" Write-Output "[$(Get-Date)]: SAS Expiry Date : $SASexpiryDate" Write-Output "[$(Get-Date)]: User Principal Name : $UserPrincipalName" Write-Output "[$(Get-Date)]: Connected to subscription: [$( (Get-AzSubscription -subscriptionId $subscriptionId).Name )]`n" Write-Output "[$(Get-Date)]: SQL Managed Instances identified in the current subscription `n" Write-Output ("_" * 60 + "`n"); $SQLMIList.DNSName; Write-Output ("`n" + ("_" * 60)) #_______________________________________________________________ # Generate a new SAS token and update Keyvault #_______________________________________________________________ function GenerateSASToken_And_UpdateKeyVault { $context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey ((Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName -WarningAction SilentlyContinue)[0].Value ) -WarningAction SilentlyContinue $expiryTime = (Get-Date).Adddays($SASexpiryDate).ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ") try { $sasToken = New-AzStorageAccountSASToken -Service Blob -ResourceType Container,Object -Permission "rwdlact" -ExpiryTime $expiryTime -Context $context Write-Output "[$(Get-Date)]: SAS Token generated successfully" $global:NewSASKey=$sasToken } catch { $ErrorOnSASkeyGeneration=$True Write-Output "[$(Get-Date)]: Failed to generate SAS Token. Error: $_" return ("ErrorOnSASkeyGeneration") } $securesasToken = $sasToken | ConvertTo-SecureString -AsPlainText -Force if ($ErrorOnSASkeyGeneration -ne $True) { # Check if the secret exists in Key Vault $secret = Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $secretName -ErrorAction SilentlyContinue try { if ($secret) { # Update the existing secret with the new SAS token $res=Set-AzKeyVaultSecret -VaultName $keyVaultName -Name $secretName -SecretValue $securesasToken -Expires $expiryTime -ContentType ("SASToken_Created_"+(Get-Date -Format "yyyy-MM-dd_HH:mm:ss")) Write-Output "[$(Get-Date)]: Secret updated in Key Vault - $keyVaultName with key - $secretName." } else { # Create a new secret in Key Vault $res=Set-AzKeyVaultSecret -VaultName $keyVaultName -Name $secretName -SecretValue $securesasToken -Expires $expiryTime -ContentType ("SASToken_Created_"+(Get-Date -Format "yyyy-MM-dd_HH:mm:ss")) Write-Output "[$(Get-Date)]: New Secret created in Key Vault - $keyVaultName with key - $secretName." } } catch { # Handle the error Write-Output "[$(Get-Date)]: An error occurred while updating or creating the secret in Key Vault." Write-Output "Error Message: $_" $exception=$True } } } #_______________________________________________________________ # Update Database scope credentials on SQL Managed instance #_______________________________________________________________ function UpdateDatabaseScopeCredential { param ([string]$sqlmi) $myCred = Get-AutomationPSCredential -Name "CredentialforMI" Write-Output "[$(Get-Date)]:Connecting to the Server : $sqlmi" try { #Using managed identity $secureToken = Get-AzAccessToken -ResourceUrl "https://database.windows.net/" -AsSecureString $token = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secureToken.Token)) $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=$sqlmi;Database=$database;Encrypt=True;Connection Timeout=30;") $Conn.AccessToken = $token $Conn.Open() $Command = $Conn.CreateCommand() $Command.CommandText = "EXEC $database.[dbo].[UpdateDatabaseScopedCredential] '$storageAccountName', '$NewSASKey'" $rowsAffected = $Command.ExecuteNonQuery() if ($rowsAffected -eq -1) { Write-Output "[$(Get-Date)]: The command executed successfully." } else { Write-Output "[$(Get-Date)]: $rowsAffected rows were affected." } Write-Output "[$(Get-Date)]: Updated the SASKey on $sqlmi" } catch { Write-Output "[$(Get-Date)]:An error occurred: $_" } finally { if ($Conn.State -eq 'Open') { $Conn.Close() } } } Write-Output "[$(Get-Date)]: Attempting to generate a new SASToken for Storage account :[$storageAccountName] and update Keyvault :[$keyVaultName]" GenerateSASToken_And_UpdateKeyVault Write-Output "[$(Get-Date)]: Attempting to update the database scope credential for SQL Managed instances" Write-Output "$SQLMIList`n" foreach ($SQLMI in $SQLMIList) { UpdateDatabaseScopeCredential -sqlmi $SQLMI.DNSName } Write-Output "[$(Get-Date)]: __________________ End of SAS Key Rotation Script __________________" SQL Script to Update a Database Scoped Credential UpdateDatabaseScopedCredential.sql Use [DBA] go CREATE OR ALTER PROCEDURE UpdateDatabaseScopedCredential @Storactaccount NVARCHAR(128), @NewKey NVARCHAR(MAX) WITH ENCRYPTION AS BEGIN set nocount on -- Declare variables for dynamic SQL DECLARE @sql NVARCHAR(MAX); DECLARE @dbName NVARCHAR(128); DECLARE @credentialName NVARCHAR(128); IF OBJECT_ID('dbo.SASKeyRotationLogging', 'U') IS NULL BEGIN CREATE TABLE SASKeyRotationLogging (Timestamp DATETIME DEFAULT GETUTCDATE(), Details NVARCHAR(MAX)); END PRINT REPLICATE('_', 100)+ CHAR(13) + CHAR(10)+ '[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Starting the script to update the credentials on Server [' + @@SERVERNAME + '].'; INSERT INTO master..SASKeyRotationLogging (Details) VALUES ('[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Starting the script to update the credentials on Server [' + @@SERVERNAME + '].'); INSERT INTO master..SASKeyRotationLogging (Details) SELECT '[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Database name: ' + name FROM sys.databases WHERE name LIKE '%_ems'; -- Cursor to iterate over databases DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE name LIKE '%_ems'; OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @dbName; WHILE @@FETCH_STATUS = 0 BEGIN -- Construct the dynamic SQL to be executed in each database SET @sql = ' USE [' + @dbName + ']; DECLARE @Result TABLE (CredentialName NVARCHAR(128)); INSERT INTO @Result (CredentialName) SELECT c.name AS CredentialName FROM sys.database_scoped_credentials AS c INNER JOIN sys.external_data_sources AS ds ON ds.credential_id = c.credential_id WHERE c.credential_identity = ''SHARED ACCESS SIGNATURE'' AND ds.type = 5 AND ds.location LIKE ''%' + @Storactaccount + '%''; -- Corrected LIKE syntax -- Update the credentials DECLARE @credName NVARCHAR(128); DECLARE cred_cursor CURSOR FOR SELECT CredentialName FROM @Result; OPEN cred_cursor; FETCH NEXT FROM cred_cursor INTO @credName; WHILE @@FETCH_STATUS = 0 BEGIN -- Construct the ALTER DATABASE SCOPED CREDENTIAL command DECLARE @alterSql NVARCHAR(MAX); SET @alterSql = ''ALTER DATABASE SCOPED CREDENTIAL ['' + @credName + ''] WITH IDENTITY = ''''SHARED ACCESS SIGNATURE'''', SECRET = ''''' + @NewKey + ''''';'' EXEC sp_executesql @alterSql; PRINT ''['' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + ''] [Database: ' + @dbName + '] Updated the key for credential ['' + @credName + ''].'' INSERT INTO master..SASKeyRotationLogging (Details) VALUES (''['' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + ''] [Database: ' + @dbName + '] Updated the key for credential ['' + @credName + ''].''); FETCH NEXT FROM cred_cursor INTO @credName; END '; -- Execute the dynamic SQL EXEC sp_executesql @sql, N'@Storactaccount NVARCHAR(128)', @Storactaccount; FETCH NEXT FROM db_cursor INTO @dbName; END; CLOSE db_cursor; DEALLOCATE db_cursor; PRINT '[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] End of script to update the credentials on Server [' + @@SERVERNAME + '].'+ CHAR(13) + CHAR(10)+REPLICATE('_', 100); INSERT INTO master..SASKeyRotationLogging (Details) VALUES ('[' + CONVERT(NVARCHAR(30), GETUTCDATE(), 126) + '] Ending the script to update the credentials on Server [' + @@SERVERNAME + '].'); ENDMonitoring CDC using Extended Events in SQL Server 2022
What do the new SQL 2022 CDC extended events track? CDC jobs include: Capture Job - Captures changes into capture table. Clean-up Job - Removes older data from capture table. Each event will cover the different CDC job activities. repl_logscan_session reports on capture job progress. cdc_cleanup_job_status reports on Clean-up job progress
