SQL
160 TopicsSQL Cluster Connecting to Linked Server over a firewall.
Hi, Scenario: SQL FCI Cluster --> firewall (stateful) --> SQL Linked Server SQL cluster needs to connect to linked server at the other side of a firewall. Question: What needs to be opened on the firewall? SQL VIP --> SQL VIP or are the cluster node IP's required too? SQL VIP --> SQL VIP Node1 --> SQL VIP Node2 --> SQL VIP Many thanksSolved33Views0likes2CommentsDeadlocks on High Frequency Updates
Using SQL Server 2022, I'm stress testing an UPDATE statement. I'm using a python script to send parallel requests to the database. The problem is that, as soon as the number of parallel requests exceed max_workers_count, 576 in my case, I get multiple errors of the form: ('40001', '[40001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 448) was deadlocked on lock | thread resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') I wasn't able to reproduce the error with less requests than max_workers_count. The UPDATE request is the following: UPDATE dbo.UsersAnswer SET UsersSelectionType = ? WHERE For_Question = ? AND For_Quiz = ? AND FK_Answer = ?; Note that, I've tried with and without (UPDLOCK, ROWLOCK) and (UPDLOCK), but it doesn't change the outcome. Also, the updates are done for the same primary key. Finally, the UsersAnswer table is created as follows: CREATE TABLE [dbo].[UsersAnswer]( [For_Question] [smallint] NOT NULL, [For_Quiz] [uniqueidentifier] NOT NULL, [FK_Answer] [int] NOT NULL, [UsersSelectionType] [tinyint] NOT NULL, CONSTRAINT [PK_UsersAnswer] PRIMARY KEY CLUSTERED ( [For_Question] ASC, [For_Quiz] ASC, [FK_Answer] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UsersAnswer] WITH CHECK ADD CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] FOREIGN KEY([FK_Answer]) REFERENCES [dbo].[Answer] ([PK_Answer]) GO ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] GO ALTER TABLE [dbo].[UsersAnswer] WITH CHECK ADD CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] FOREIGN KEY([For_Question], [For_Quiz]) REFERENCES [dbo].[QQ] ([FK_Question], [FK_Quiz]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] GO Do you have any idea on what could cause the deadlock? The deadlock graph is huge, you can find it here. Thanks for your insights on this.Solved166Views0likes8CommentsSetting up SQL Managed Instance Link to an Availability Group
On a recent case, a customer was trying to set up SQL Managed Instance Link to partner with an on premise Always On Availability Group (AG). Using the public documentation will work, but this will only be active against the primary node and a failover will cause the database on the managed instance side to stop syncing. This post will be using steps from the following documents. Prepare your environment for a link - Azure SQL Managed Instance Configure link with scripts - Azure SQL Managed Instance The steps to set this up are below Create a database master key on your AG nodes Enabling trace flags on your AG nodes (Optional) Testing network connectivity between your SQL Managed Instance and the AG Create certificates on your AG nodes Import AG certificate public keys to your SQL Managed Instance Import the certificate public key of your SQL Managed Instance to your AG nodes Import Azure-trusted root certificate authority keys to your AG nodes Alter the mirroring endpoint on your AG nodes Create a distributed availability group on your AG Set up the managed Instance Link Create a database master key You first need to create a master encryption key on all nodes of your AG if it does not already exist. You can check if it exists by running this query. -- Run on SQL Server USE master; GO SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%'; If no results are returned, run this query, keep note of the passwords in a confidential and secure place. -- Run on SQL Server -- Create a master key USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong_password>'; Enabling trace flags on your AG nodes (Optional) To optimise the performance of your link, we recommend enabling the trace flags T1800 and T9567 on all of your nodes. Instructions on this are here and will require a restart of the service. Testing network connectivity between your SQL Managed Instance and the AG Instructions on how to run the connectivity tests can be found here, and can be done by SSMS or T-SQL. For these tests, your SQL Managed Instance will need to be able to connect to your Mirror endpoint using the IP of your availability group. All nodes of your AG will need to be able to connect to ports 5022 and 11000-11999 on the subnet of your SQL Managed Instance. Create certificates on your AG nodes On each node, run the query below to create a certificate. You can alter your @cert_expiry_date variable to a date that suits you. -- Create the SQL Server certificate for the instance link USE MASTER -- Customize SQL Server certificate expiration date by adjusting the date below DECLARE @cert_expiry_date AS varchar(max)='03/30/2025' -- Build the query to generate the certificate DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername + N'_endpoint' DECLARE @sqlserver_certificate_subject NVARCHAR(MAX) = N'Certificate for ' + @sqlserver_certificate_name DECLARE @create_sqlserver_certificate_command NVARCHAR(MAX) = N'CREATE CERTIFICATE [' + @sqlserver_certificate_name + '] ' + char (13) + ' WITH SUBJECT = ''' + @sqlserver_certificate_subject + ''',' + char (13) + ' EXPIRY_DATE = '''+ @cert_expiry_date + ''''+ char (13) IF NOT EXISTS (SELECT name from sys.certificates WHERE name = @sqlserver_certificate_name) BEGIN PRINT (@create_sqlserver_certificate_command) -- Execute the query to create SQL Server certificate for the instance link EXEC sp_executesql @stmt = @create_sqlserver_certificate_command END ELSE PRINT 'Certificate ' + @sqlserver_certificate_name + ' already exists.' GO Import AG certificate public keys to your SQL Managed Instance The public keys of your AG certificates need to be imported into your SQL Managed Instance. Run this query on each node to get the data required -- Run on SQL Server -- Show the name and the public key of generated SQL Server certificate USE MASTER GO DECLARE @sqlserver_certificate_name NVARCHAR(MAX) = N'Cert_' + @@servername + N'_endpoint' DECLARE @PUBLICKEYENC VARBINARY(MAX) = CERTENCODED(CERT_ID(@sqlserver_certificate_name)); SELECT @sqlserver_certificate_name as 'SQLServerCertName' SELECT @PUBLICKEYENC AS SQLServerPublicKey; You will get a result similar to below You can now import these into your SQL Managed Instance using Azure Cloud Shell. Log in using this script, replacing . # Run in Azure Cloud Shell (select PowerShell console) # Enter your Azure subscription ID $SubscriptionID = "<SubscriptionID>" # Login to Azure and select subscription ID if ((Get-AzContext ) -eq $null) { echo "Logging to Azure subscription" Login-AzAccount } Select-AzSubscription -SubscriptionName $SubscriptionID Then run this script, replacing the values of $CertificateName, $PublicKeyEncoded and $ManagedInstanceName. # Run in Azure Cloud Shell (select PowerShell console) # =============================================================================== # POWERSHELL SCRIPT TO IMPORT SQL SERVER PUBLIC CERTIFICATE TO SQL MANAGED INSTANCE # ===== Enter user variables here ==== # Enter the name for the server SQLServerCertName certificate – for example, "Cert_sqlserver1_endpoint" $CertificateName = "<SQLServerCertName>" # Insert the certificate public key blob that you got from SQL Server – for example, "0x1234567..." $PublicKeyEncoded = "<SQLServerPublicKey>" # Enter your managed instance short name – for example, "sqlmi" $ManagedInstanceName = "<ManagedInstanceName>" # ==== Do not customize the below cmdlets==== # Find out the resource group name $ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName # Upload the public key of the authentication certificate from SQL Server to Azure. New-AzSqlInstanceServerTrustCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -Name $CertificateName -PublicKey $PublicKeyEncoded Repeat this step for all AG nodes. Import the certificate public key of your SQL Managed Instance to your AG nodes Using the same Azure Cloud Shell, run this script to get the public key of your SQL Managed Instance certificate, replacing the value of the variable $ManagedInstanceName. # Run in Azure Cloud Shell (select PowerShell console) # =============================================================================== # POWERSHELL SCRIPT TO EXPORT MANAGED INSTANCE PUBLIC CERTIFICATE # ===== Enter user variables here ==== # Enter your managed instance short name – for example, "sqlmi" $ManagedInstanceName = "<ManagedInstanceName>" # ==== Do not customize the following cmdlet ==== # Find out the resource group name $ResourceGroup = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName # Fetch the public key of the authentication certificate from Managed Instance. Outputs a binary key in the property PublicKey. Get-AzSqlInstanceEndpointCertificate -ResourceGroupName $ResourceGroup -InstanceName $ManagedInstanceName -EndpointType "DATABASE_MIRRORING" | out-string On all nodes of your AG you can then run this query, replacing with the value of the PublicKey output in the previous step. -- Run on SQL Server USE MASTER CREATE CERTIFICATE [<ManagedInstanceFQDN>] FROM BINARY = <PublicKey> Import Azure-trusted root certificate authority keys to your AG nodes Use the steps here to import the Microsoft PKI root-authority certificate and DigiCert PKI root-authority certificate onto all nodes of your AG. Alter the mirroring endpoint on your AG nodes The mirroring endpoint on your AG nodes will also need to be updated to allow your Managed Instance to authenticate with your AG using the newly created certificate. This can be done by navigating to Server Objects > Endpoints > Database Mirroring. Right click on the endpoint and select Script Endpoint as > CREATE To > New Query Editor Window. You will need to add this command under AUTHENTICATION, changing the cert_name value and change the CREATE ENDPOINT to ALTER ENDPOINT. CERTIFICATE [cert_name] Once updated, execute the query. Create a distributed availability group on your AG Use this script to set up the distributed availability group, updating the values - Your choice of name - AG Name already created : - The IP of your AG listener and the endpoint listener port - Your choice of name - FQDN of your instance - Just the instance name -- Run on SQL Server -- Create a distributed availability group for the availability group and database -- ManagedInstanceName example: 'sqlmi1' -- ManagedInstanceFQDN example: 'sqlmi1.73d19f36a420a.database.windows.net' USE MASTER CREATE AVAILABILITY GROUP [<DAGName>] WITH (DISTRIBUTED) AVAILABILITY GROUP ON N'<AGNameOnSQLServer>' WITH ( LISTENER_URL = 'TCP://<SQLServerIP>:<EndpointPort>', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC, SESSION_TIMEOUT = 20 ), N'<AGNameOnSQLMI>' WITH ( LISTENER_URL = 'tcp://<ManagedInstanceFQDN>:5022;Server=[<ManagedInstanceName>]', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC ); GO Set up the managed Instance Link The final step is to set up the link, you can do this by running the script here. Please ensure that the $SQLServerIP is the IP of your AG listener and not the IP of the primary node. The in the link includes updated parameters from Az.Sql 6.0.0. This version is not currently available in Azure Cloud Shell. If you are using Azure Cloud Shell, replace the final section of the command... ...with the below New-AzSqlInstanceLink -InstanceName $ManagedInstanceName -Name $DAGName -PrimaryAvailabilityGroupName $AGNameOnSQLServer -ResourceGroupName $ResourceGroup -SecondaryAvailabilityGroupName $AGNameOnSQLMI -SourceEndpoint $SourceIP -TargetDatabase $DatabaseName Once this is executed, your MI Link should be up and running and failing over to another node on your AG will not stop your MI Link from syncing. Disclaimer Please note that products and options presented in this article are subject to change. This article reflects MI Link and the documentation in January 2025. I hope this article was helpful for you, please feel free to share your feedback in the comments section.486Views0likes0CommentsLeveraging GitHub Copilot for T-SQL Code Conversion: A Deep Dive into Code Explainability
Converting database code between different relational database management systems (RDBMS) is an essential part of database migration , especially when moving from Oracle’s PL/SQL to SQL Server’s T-SQL. Both Oracle and SQL Server have unique syntax, functions, and programming conventions that require significant adjustments when converting complex SQL queries, stored procedures, or functions. While manual conversion can be a painstaking process, GitHub Copilot, an AI-powered code assistant, can ease this burden by offering real-time suggestions and automating many aspects of the conversion. In the first part of this two-part blog, we explore how GitHub Copilot can be a powerful tool for code conversion, helping developers quickly adapt code from one language or framework to another. By providing context-aware suggestions and completions, Copilot simplifies the process of rewriting and refactoring code. In the second part, we dive deeper into Copilot’s explainability feature, showcasing how it enhances the code conversion process from Oracle to Azure SQL. Code explainability in GitHub Copilot refers to the tool’s ability to offer suggestions and comments that help developers understand what a given piece of code is doing. When dealing with database migrations, such as converting Oracle SQL code to Azure SQL (SQL Server), GitHub Copilot’s ability to explain, suggest, and refactor code can ease the transition. It can assist in making the conversion process smoother, more efficient, and less error-prone by explaining the logic behind Oracle-specific queries and suggesting corresponding changes in Azure SQL’s syntax We'll go through multiple examples, analyze the differences between the two languages, and show how GitHub Copilot handles these challenges. Understanding the Key Differences: PL/SQL vs T-SQL Before jumping into examples, it's important to understand few of the fundamental differences between PL/SQL (Oracle) and T-SQL (SQL Server): Syntax: While both are procedural SQL dialects, there are key differences in the way they handle variables, control structures, and flow control. Functions: Each platform has its own set of built-in functions (e.g., string manipulation, date handling, etc.), and these need to be mapped correctly during conversion. Error Handling: Error handling and exception management differ between the two, with PL/SQL using EXCEPTION blocks and T-SQL using TRY...CATCH. Cursor Handling: While both support cursors for iterating through results, their syntax differs. Leveraging GitHub Copilot for Oracle PL/SQL to SQL Server T-SQL Code Conversion: A Deep Dive into Complex Examples with Explainability Converting database code between different relational database management systems (RDBMS) is an essential part of database migration , especially when moving from Oracle’s PL/SQL to SQL Server’s T-SQL. Both Oracle and SQL Server have unique syntax, functions, and programming conventions that require significant adjustments when converting complex SQL queries, stored procedures, or functions. While manual conversion can be a painstaking process, GitHub Copilot, an AI-powered code assistant, can ease this burden by offering real-time suggestions and automating many aspects of the conversion. In the first part of this two-part blog, we explore how GitHub Copilot can be a powerful tool for code conversion, helping developers quickly adapt code from one language or framework to another. By providing context-aware suggestions and completions, Copilot simplifies the process of rewriting and refactoring code. In the second part, we dive deeper into Copilot’s explainability feature, showcasing how it enhances the code conversion process from Oracle to Azure SQL. Code explainability in GitHub Copilot refers to the tool’s ability to offer suggestions and comments that help developers understand what a given piece of code is doing. When dealing with database migrations, such as converting Oracle SQL code to Azure SQL (SQL Server), GitHub Copilot’s ability to explain, suggest, and refactor code can ease the transition. It can assist in making the conversion process smoother, more efficient, and less error-prone by explaining the logic behind Oracle-specific queries and suggesting corresponding changes in Azure SQL’s syntax We'll go through multiple examples, analyse the differences between the two languages, and show how GitHub Copilot handles these challenges. Understanding the Key Differences: PL/SQL vs T-SQL Before jumping into examples, it's important to understand few of the fundamental differences between PL/SQL (Oracle) and T-SQL (SQL Server): Syntax: While both are procedural SQL dialects, there are key differences in the way they handle variables, control structures, and flow control. Functions: Each platform has its own set of built-in functions (e.g., string manipulation, date handling, etc.), and these need to be mapped correctly during conversion. Error Handling: Error handling and exception management differ between the two, with PL/SQL using EXCEPTION blocks and T-SQL using TRY...CATCH. Cursor Handling: While both support cursors for iterating through results, their syntax differs. Example 1: Complex Stored Procedure Conversion Let’s start with a complex example that involves handling parameters, cursors, and error handling. We'll look at a PL/SQL stored procedure in Oracle that processes employee records and outputs their details. CREATE OR REPLACE PROCEDURE GetEmployeeDetails (emp_id IN NUMBER) IS CURSOR emp_cursor IS SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE employee_id = emp_id; emp_record emp_cursor%ROWTYPE; emp_full_name VARCHAR2(100); BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; -- Concatenate first and last name emp_full_name := emp_record.first_name || ' ' || emp_record.last_name; -- Output employee details DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id); DBMS_OUTPUT.PUT_LINE('Full Name: ' || emp_full_name); DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(emp_record.hire_date, 'YYYY-MM-DD')); END LOOP; CLOSE emp_cursor; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred.'); END; Oracle PL/SQL Example In Oracle, we might have a procedure that uses a cursor to fetch employee records and print them. The procedure also includes error handling for potential issues during execution. Key Points in Oracle PL/SQL: Cursor: PL/SQL uses CURSOR for iterating through rows. Concatenation: Oracle uses || for string concatenation. Error Handling: PL/SQL uses EXCEPTION blocks for handling errors such as NO_DATA_FOUND. Converting to SQL Server T-SQL with GitHub Copilot When you type the PL/SQL code into an IDE with GitHub Copilot enabled (e.g., Visual Studio Code), Copilot will start suggesting the equivalent SQL Server T-SQL code. Here’s how Copilot converts the procedure. Copilot’s Suggested SQL Server T-SQL Code CREATE PROCEDURE GetEmployeeDetails (@emp_id INT) AS BEGIN DECLARE @emp_id INT, @first_name NVARCHAR(50), @last_name NVARCHAR(50); DECLARE @emp_full_name NVARCHAR(100); DECLARE emp_cursor CURSOR FOR SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE employee_id = @emp_id; OPEN emp_cursor; FETCH NEXT FROM emp_cursor INTO @emp_id, @first_name, @last_name, @hire_date; WHILE @@FETCH_STATUS = 0 BEGIN -- Concatenate first and last name SET @emp_full_name = @first_name + ' ' + @last_name; -- Output employee details PRINT 'Employee ID: ' + CAST(@emp_id AS NVARCHAR); PRINT 'Full Name: ' + @emp_full_name; PRINT 'Hire Date: ' + CONVERT(NVARCHAR, @hire_date, 23); FETCH NEXT FROM emp_cursor INTO @emp_id, @first_name, @last_name, @hire_date; END CLOSE emp_cursor; DEALLOCATE emp_cursor; BEGIN TRY -- Error handling code (example) END TRY BEGIN CATCH PRINT 'An error occurred.'; END CATCH END; Key Points in T-SQL: Cursor: SQL Server uses DECLARE CURSOR to declare a cursor, similar to PL/SQL but with different syntax for opening and fetching. Concatenation: SQL Server uses + for string concatenation. Error Handling: SQL Server uses TRY...CATCH for error handling, unlike PL/SQL’s EXCEPTION block. GitHub Copilot provides a T-SQL equivalent for an Oracle PL/SQL stored procedure. Explanation of the Conversion Process Cursor Declaration and Fetching: PL/SQL uses CURSOR with OPEN, FETCH, and CLOSE to process rows. T-SQL uses DECLARE CURSOR to define the cursor and fetches rows with FETCH NEXT. Both approaches are similar, but T-SQL requires explicit DEALLOCATE to release the cursor. Error Handling: PL/SQL uses EXCEPTION blocks to handle errors like NO_DATA_FOUND. T-SQL uses TRY...CATCH blocks to capture and handle errors, a major difference in error handling between the two systems. String Concatenation: PL/SQL uses || for concatenating strings, while T-SQL uses +. This is a simple yet crucial change when converting between the two dialects. Example 2: Complex Query with Date Handling Let’s now look at a query that involves date handling and conditional logic. These are often tricky when converting between PL/SQL and T-SQL due to differences in how each platform handles dates. Oracle PL/SQL Query Example SELECT employee_id, first_name, hire_date, CASE WHEN hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD') THEN 'Veteran' ELSE 'New Hire' END AS employee_status FROM employees WHERE hire_date BETWEEN TO_DATE('1990-01-01', 'YYYY-MM-DD') AND SYSDATE; Key Points in PL/SQL: TO_DATE: Used to convert strings into dates. SYSDATE: Returns the current system date. CASE: Used for conditional logic to categorize employees as either "Veteran" or "New Hire". Copilot’s Suggested SQL Server T-SQL Code SELECT employee_id, first_name, hire_date, CASE WHEN hire_date < CONVERT(DATE, '2000-01-01', 23) THEN 'Veteran' ELSE 'New Hire' END AS employee_status FROM employees WHERE hire_date BETWEEN CONVERT(DATE, '1990-01-01', 23) AND GETDATE(); Key Points in T-SQL: CONVERT: SQL Server uses CONVERT for date formatting, similar to Oracle’s TO_DATE, but with different syntax and style codes. GETDATE(): Equivalent to Oracle’s TO_DATE In conclusion, GitHub Copilot streamlines code conversion by offering both quick suggestions and detailed explanations. The first part highlighted its ability to assist with code transitions, while the second part focused on how its explainability feature enhances the Oracle to Azure SQL migration, providing guided, efficient, and error-free conversion. This combination accelerates development and reduces potential issues during migration.573Views2likes0CommentsCapturing Latest Effective Date of Current Instance of Position
I am having issues trying to formulate a JOIN statement within my query that will provide the values that I am needing for all employees. Specifically, situations where an employee held the position multiple times. Here my current statement segment: JOIN (SELECT R_POSITION, WORK_ASSIGNMNT, EMPLOYEE, MIN(EFFECT_DATE) AS EFFECT_DATE FROM HR_EMP_POSITIONS GROUP BY R_POSITION, WORK_ASSIGNMNT, EMPLOYEE) AS EP ON EP.R_POSITION = W.POSITION AND EP.EMPLOYEE = W.EMPLOYEE AND EP.WORK_ASSIGNMNT = W.WORK_ASSIGNMNT For my statement, I need to retrieve the latest EFFECT_DATE of the last time the employee held a specific position that matches their current position. Please find my sample document with the EP and W tables. My current statement works for employee A; however, for employee B it is providing the 10/16/2023 date when I need it to populate the 8/26/2024 date. Any ideas on what I can do to get the data that I need? I don't have any other fields that I can use to help refine the criteria.61Views0likes3CommentsA quick way to generate Azure SQL migration assessments
Overview Azure Migrate doesn’t allow users to import the SQL Server Inventory and discovery information from sources other than Azure Migrate appliance as of today. As a result, users who cannot deploy Azure Migrate appliance to discover their SQL Server estate for technical, business or compliance reasons or have already discovered their SQL Server estate using tools like rvtools and want a quick and approximate way to check readiness and total cost savings by moving to Azure, cannot leverage the rich capabilities SQL assessment and business case capabilities that Azure Migrate offers. To help customers quickly get migration readiness and cost savings by migrating to Azure, we are introducing “Import SQL Server Discovery data as CSV” feature in Azure Migrate that allows users to import the SQL Server inventory as a CSV file and use the discovery data to generate assessments and business cases. This feature is launched in private preview. In this blog post, we will do a deep dive on how to use this feature to generate quick assessments for your off-Azure SQL Server estate. Here are the high-level steps for importing your off-Azure SQL Server inventory as a CSV: 1) Step-1: Identify the SQL Server Instances and extract the discovery data into a CSV file. 2) Step-2: Validate and upload the inventory data to Azure Migrate 3) Step-3: Generate and view Assessments Pre-requisites: Import SQL Discovery data as a CSV feature is built on top of the new Azure Migrate experience which is also in Private Preview phase. To use Import SQL Discovery data as CSV feature, you must onboard to the private preview of new Azure Migrate experience and here is the form to sign up for Private preview: Azure Migrate Private Preview Sign-Up Survey. We will share the instructions to onboard via the contact details you provided. Make sure you have the credentials to connect to the SQL Server Instances of interest Make sure you have the requisite roles and privileges on each SQL Server Instance to access and extract the required metadata from Dynamic Management Views. To generate a login with required privileges, please run the scripts listed here: Configure custom login for SQL Server Discovery An Azure Subscription. Please note you don't need to deploy an Azure Migrate appliance to use the SQL import CSV feature. Step-1: Identify and extract the discovery data of SQL Server Instances you intend to migrate to Azure SQL into a CSV file Identify the list of all off-Azure SQL Server Instances you intend to migrate to Azure SQL from either on-premises infrastructure or other cloud platforms like Amazon Web Services (AWS), Google Cloud Platform (GCP). To extract the discovery data, please run the SQL Script below against the SQL Server Instance which you would like to import into Azure Migrate via CSV and store the output into a CSV file. /* PLEASE ENTER THE FULLY QUALIFIED DOMAIN NAME OF THE HOST IN THE LINE BELOW BEFORE RUNNING THE QUERY */ Declare @FullyQualifiedDomainName varchar(200) = ''; With ServerCores AS( SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], sqlserver_start_time FROM sys.dm_os_sys_info ), Config AS( SELECT value_in_use AS [Max Server Memory(MB) In Use] FROM sys.configurations WHERE name = 'max server memory (MB)'), UserCount AS( SELECT count(database_id) AS [Number User Databases] FROM sys.databases WHERE database_id > 4 ), DbSize AS( SELECT SUM(CAST(Size AS bigint))*8/1024 AS [Max server memory (in MB)] FROM sys.master_files WHERE database_id > 4 ), TempDbSize AS ( SELECT SUM(CAST(Size AS bigint))*8/1024 AS [TempDb Size (In MB)] FROM sys.master_files WHERE db_name(database_id) = 'tempdb' ), Logins AS (Select count(name) AS [NumOfLogins] from sys.syslogins), Properties AS ( select db.name as database_name, db.database_id as database_id, cast(db.compatibility_level as int) as database_compatibility_level, db.collation_name as database_collation, db.is_broker_enabled as is_service_broker_enabled, '0' as [Number of Nics], SERVERPROPERTY ('servername') as instance_name, SERVERPROPERTY ('productversion') as instance_version, SERVERPROPERTY ('edition') as instance_edition, SERVERPROPERTY ('productlevel') as instance_level, SERVERPROPERTY ('IsHadrEnabled') as is_hadr_enabled, SERVERPROPERTY ('collation') as server_collation, CASE WHEN SERVERPROPERTY ('IsClustered') = 1 THEN 'TRUE' ELSE 'FALSE' END as is_failover_clustered, COALESCE(SERVERPROPERTY ('IsIntegratedSecurityOnly'), 0) as is_integrated_security_only, COALESCE(SERVERPROPERTY ('IsPolyBaseInstalled'), 0) as is_polybase_installed, CASE WHEN 'A' = 'a' THEN 0 ELSE 1 END as is_server_case_sensitive, /* '' + @host_platform + '' as host_platform,*/ db.state as database_state, /* db.is_auto_close_on as is_auto_close_on,'+ CASE when @check_cdc = 1 THEN 'db.is_cdc_enabled' else '0' END + ' as is_cdc_enabled,*/ CASE when db.user_access = 0 THEN 1 ELSE 0 END as is_multi_user, CASE when db.recovery_model = 0 THEN 0 ELSE 1 END as is_full_recovery_model, db.is_read_only as is_read_only, case when source_database_id IS NULL THEN 0 ELSE 1 end as is_snapshot, case when is_published = 1 OR is_subscribed = 1 OR is_merge_published = 1 or is_distributor = 1 THEN 1 ELSE 0 end as is_replication_enabled, db.is_encrypted from sys.databases db where db.name not in ('master', 'tempdb', 'model', 'msdb') and is_distributor <> 1), EachDbSize AS( SELECT database_name = DB_NAME(database_id), CAST(SUM(CAST(size AS bigint)) * 8. / 1024 AS DECIMAL(12,2)) AS [Database size (in MB)], convert(varchar(12),CAST((sum(case when physical_name like '%ldf'then 0 else size end)*8.0)/1024.0 AS numeric(8,2))) AS [Data file size (in MB)], convert(varchar(12),CAST((sum(case when physical_name like '%ldf'then size else 0 end)*8.0)/1024.0 AS numeric(8,2))) AS [Log file size (in MB)] FROM sys.master_files WHERE database_id > 4 GROUP BY database_id) select @FullyQualifiedDomainName as [Fully qualified domain name], instance_name as [Instance Name], p.database_name as [Database Name], database_state as [Database Status], instance_edition [SQL Edition], instance_version as [SQL Version], [Logical CPU Count], [Hyperthread Ratio], [Physical CPU Count], [TempDb size (in MB)], is_failover_clustered as [Is FCI Enabled], '' as [Failover cluster name], '' as [Failover cluster instance role], '' as [Failover cluster shared disk count], '' as [Is failover cluster multi subnet], [Max server memory (in MB)], [Number of Nics], instance_level as [Service pack], '' as [ESU status], 'FALSE' as [IS AG enabled], instance_level as [Build version], 'Online' as [Service status], 'FALSE' as [Is database highly available], 'FALSE' as [Is part of distributed availability group], [Database size (in MB)], [Data file size (in MB)], [Log file size (in MB)], database_compatibility_level as [Compatibility level], '' as [Availability group ID], '' as [Availability group name], '' as [Availability group type], '' as [Availability replica ID], '' as [Availability replica name], '' as [Commit mode], '' as [Replica type], '' as [Replica state], 'FALSE' as [Is AG multi subnet], '' as [AG replica sync status], '' as [AG replica seed mode], '' as [AG replica read mode] from ServerCores, Config, UserCount, DbSize, TempDbSize,Logins, Properties p LEFT JOIN EachDbSize s on p.database_name = s.database_name order by p.database_name Collate the output of all SQL Server Instances of interest into a single file. Step-2: Validate and upload the CSV file To validate and upload the generated CSV file with SQL Server discovery data: 1) Go to the Azure Migrate experience link you would have received from Microsoft after you signed up for the private preview of this feature. Click on “Create Project” to create a new Project (if not done already): 2) Fill in the required fields in create project wizard 3) Click on the created project and select “Using custom Import” option under “Start Discovery” button. 4) In the Discover page, select “File Type” as “SQL Server inventory (CSV)” 5) (Optional) Click on “Download” button under Step 1: Download template to view and understand the template expected by Azure Migrate. Optionally, you can skip this step as the CSV file generated by SQL script shared above confirms to the template. 6) Browse and select the inventory file in Step-2 and click on Validate to validate the uploaded SQL Inventory data. 7) If the uploaded doesn’t contain any warnings or error messages, it will show validation completed successfully with 0 errors and warnings. If there are any errors or warnings Azure Migrate will show a count of warning and error messages. To view and resolve the warnings or errors (if any) with the imported file, click on Download ErroDetails.csv, which displays errors/warning messages corresponding to each database along with remedial action. Please note warnings are non-blocking messages, i.e., user can still go ahead and complete the Import operation. Whereas errors are blocking in nature i.e. users will not be allowed to import unless the error messages are resolved. 8) Once the validation phase is completed, click on Import button to complete the import operation. Step-3: Create Assessments using imported SQL Discovery Data SQL Server Instances and the databases uploaded via CSV import route are treated at par with the SQL Server instances that are discovered by Azure Migrate appliance. You can view the SQL Server Instance properties like Version details, Number of databases, Support status and all other properties uploaded via CSV file. The quality of property details and the assessments generated is directly dependent on the quality of data uploaded via CSV. To create SQL assessments for the uploaded SQL Server Instances, go to the Azure Migrate project's resource page and click on Create Assessments button: Give a name for the assessment and click on Add workloads to add the imported and appliance discovered workloads: In the following page, select the SQL Server instances that have value “Import” for Discovery Source column. You can also create assessments on a combination of workloads that are imported and appliance discovered. Click Add button to add the workloads and click on Review + Create Assessments to create assessment. For imported SQL Server instances, the quality of assessments is only as good as the data uploaded via CSV, please ensure that the data uploaded via CSV accurately depicts the metadata of the SQL Server instance. Like regular SQL assessments, you will have all the assessment configuration options like choosing Sizing criteria (performance-based vs as on-premises), migration strategy (optimizing cost vs migration to PaaS), comfort factor etc. By default, Azure Migrate will try to create a performance-based incase the performance-metrics are not available, it defaults to As-on-premises assessment. Assessment created on the imported SQL Servers will help you know which SQL Server Instances are ready for migration to Azure SQL, the recommended Azure SQL Target type. Linking and Rediscovery Linking If the host where the imported SQL Instance currently hosted is discovered via import of VM Servers or via appliance, the SQL Server Instance will be automatically associated with that host. SQL Server Instance will be rolled up under that VM Server in the discovery view. The SQL Server instance will be uniquely identified based on the FQDN and the SQL Server Instance name. The combination of attributes is used for conflict resolution as well. Rediscovery and precedence rules 1) If the SQL Server instance is originally imported via CSV and is reuploaded again via CSV, upload operation will rewrite all the properties. 2) If the SQL server instance is first imported via CSV and then discovered via Azure Migrate appliance and the user has provided credentials to do deep discovery via Azure Migrate appliance, then the appliance discovered data will override the data imported via CSV. 3) If the SQL server instance is first imported via CSV and then discovered via Azure Migrate appliance and the user has not provided SQL Server credentials to do deep discovery, then the appliance discovered data will only update the additional data that appliance discovered. 4) If the SQL Server Instance is first discovered via the Azure Migrate appliance and is deep discovered (correct SQL Server credentials are provided), and if the SQL Server is imported via CSV, data imported via CSV will be discarded. 5) If the SQL Server Instance is first discovered via the Azure Migrate appliance and is only inventoried (correct SQL Server credentials are not provided), and if the SQL Server is later imported via CSV, upload operation will completely overwrite the appliance discovered details. How to onboard to Private Preview To onboard Preview of SQL Inventory import as CSV, please fill the Signup form for Private preview What’s Next 1) In the next release will ship out capabilities to create Business case for SQL Servers imported via CSV. 2) We will also build support for Availability Groups and Failover cluster Instances for the SQL Server instances imported via CSV.MGDC for SharePoint FAQ: How to flatten datasets for SQL or Fabric
When you get your data from Microsoft Graph Data Connect (MGDC), you will typically get that data as a collection of JSON objects in an Azure Data Lake Storage (ADLS) Gen2 storage account. For those handling large datasets, it might be useful to move the data to a SQL Server or to OneLake (lakehouse). In those cases, you might need to flatten the datasets. This post describes how to do that. If you’re not familiar with MGDC for SharePoint, start with https://aka.ms/SharePointData. 1. Flattening Most of the MGDC for SharePoint datasets come with nested objects. That means that a certain object has other objects inside it. For instance, if you have a SharePoint Groups object, it might have multiple Group Members inside. If you have a SharePoint Permissions object, you could have many Permissions Recipients (also known as Sharees). For each SharePoint File object, you will have a single Author object inside. When you convert the datasets from JSON to other formats, it is possible that these other formats require (or perform better) if you don’t have any objects inside objects. To overcome that, you can turn those child objects into properties of the parent object. For instance, instead of having the File object with an Author object inside, you can have multiple author-related columns. For instance, you could have Author.Name and Author.Email as properties of the flattened File object. 2. Nested Objects You can get the full list of SharePoint datasets in MGDC at https://aka.ms/SharePointDatasets. Here is a table with a list of objects and their nested objects: Object How many? Primary Key Nested Object How many? Add to Primary Key Sites 1 per Site Id RootWeb 1 per Site Sites 1 per Site Id StorageMetrics 1 per Site Sites 1 per Site Id SensitivityLabelInfo 1 per Site Sites 1 per Site Id Owner 1 per Site Sites 1 per Site Id SecondaryContact 1 per Site Groups 1 per Group SiteId + GroupId Owner 1 per Group Groups 1 per Group SiteId + GroupId Members 1 per Member COALESCE(AADObjectId, Email, Name) Permissions 1 per Permission SiteId + ScopeId + RoleDefintion + LinkId SharedWithCount 1 per Recipient Type Type Permissions 1 per Permission SiteId + ScopeId + RoleDefintion + LinkId SharedWith 1 per Recipient or Sharee COALESCE(AADObjectId, Email, Name) Files 1 per File SiteId + WebId + ListId + ItemId Author 1 per File Files 1 per File SiteId + WebId + ListId + ItemId ModifiedBy 1 per File When you flatten a dataset and there is an object with multiple objects inside (like Group Members or Permission Recipients), the number of rows will increase. You also need to add to primary key to keep it unique. Also note that the File Actions, Sync Health and Sync Errors datasets do not have any nested objects. 3. One Object per Parent When the nested object has only one instance, things are simple. As we described for the Author nested object inside the File object, you promote the properties of the nested object to be properties of the parent object. This is because the Author is defined as the user that initially created the file. There is always one and only one Author. This can happen even happen multiple times for the same object. The File also has a ModifiedBy property. That is the single user that last changed the file. In that case, there is also only one ModifiedBy per File. The Site object also includes several properties in this style, like RootWeb, StorageMetrics, SensitivityLabelInfo, Owner and SecondaryContact. Note that, in the context of the Site object, there is only one owner. Actually two, but that second one is tracked in a separate object called SecondaryContact which is effectively the secondary owner. 4. Multiple Objects per Parent The SharePoint Permissions dataset has a special condition that might create trouble for flattening. There are two sets of nested objects with multiple objects each: SharedWith and SharedWithCount. SharedWith has the list of Recipients and SharedWithCount has a list of Recipient Types. If you just let the tools flatten it, you will end up a cross join of the two. As an example, if you have 4 recipients in an object and 2 types of recipients (internal users and external users, for instance) you will end up with 20 objects in the flattened dataset instead of the expected 10 objects (one per recipient). To avoid this, in this specific condition, I would recommend just excluding the SharedWithCount column from the object before flattening. 5. Conclusion I hope this clarifies how you can flatten the MGDC for SharePoint datasets, particularly SharePoint Permissions dataset. For further details about the MGDC for SharePoint, https://aka.ms/SharePointData.