### Troubleshooting References #### connection Troubleshootinghttps://learn.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide?view=sql-server-linux-ver15#connection #### Restore Database https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add – sudo add-apt-repository “$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)” sudo apt-get update sudo apt-get install -y mssql-server sudo /opt/mssql/bin/mssql-conf setup ### Command line Tools sudo apt install curl curl https://packages.microsoft.com/keys/microsoft.asc | …
Import BACPAC File Created from Azure SQL Database
Import DACPAC file to SQL Server
Import DACPAC File https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-import-from-bacpac-powershell # Login-AzureRmAccount # Set the resource group name and location for your server $resourcegroupname = “myResourceGroup-$(Get-Random)” $location = “westeurope” # Set an admin login and password for your server $adminlogin = “ServerAdmin” $password = “ChangeYourAdminPassword1” # Set server name – the …
SQL Server Performance Queries
SQL Server Performance Tuning — Top 10 sessions caused blocking SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.blocking_session_id = 0 AND …
SQL Server IO Stall Statistics
— Read IO Stalls Statistics SELECT @@VERSION GO SELECT type, SUM(io_stall_read_ms) AS io_stall_read, SUM(io_stall_write_ms) AS io_stall_write, SUM(num_of_reads) AS num_of_reads, SUM(num_of_writes) AS num_of_writes FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs JOIN sys.database_files df ON fs.file_id = df.file_id WHERE database_id = DB_ID() GROUP BY df.type
Change Database Recover Model
Change Database Recover Model USE master DECLARE @isql VARCHAR(2000), @dbname VARCHAR(64), @logfile VARCHAR(128) DECLARE c1 CURSOR FOR SELECT d.name, mf.name AS logfile –, physical_name AS current_file_location, size FROM sys.master_files mf INNER JOIN sys.databases d ON mf.database_id = d.database_id WHERE recovery_model_desc <> ‘SIMPLE’ AND d.name NOT …
Azure Useful Queries
Log Analytics // Percentiles Calculation search * | where Category == ‘SQLSecurityAuditEvents’ | order by event_time_t , server_principal_name_s ,duration_milliseconds_d | where database_name_s == “UserDB” | where server_principal_name_s == “databaselogin” | summarize percentiles(duration_milliseconds_d, 25, 50, 75, 90,95,99) by substring(statement_s, 0, 40) , server_principal_name_s ,application_name_s | where …
Deadlocks in Azure SQL Database
Deadlocks in Azure SQL Database : Recently we were working with Azure Logic Apps to invoke Azure Functions. By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values. So Logic App invoked several concurrent threads which …
Azure SQL Database Connectivity Architecture
Azure SQL Database Connectivity Architecture Connection policy Azure SQL Database supports the following three options for the connection policy setting of a SQL Database server: Redirect (recommended): Clients establish connections directly to the node hosting the database. To enable connectivity, the clients must allow outbound firewall …
Azure Database Firewall Queries
— Server Level Firewall Rules SELECT * FROM sys.firewall_rules — Database Level Firewall Rules SELECT * FROM sys.database_firewall_rules — — Remove server level firewall setting EXECUTE sp_delete_firewall_rule N’AllowAllWindowsAzureIps’; — Remove database-level firewall setting EXECUTE sp_delete_database_firewall_rule N’Allow Azure’; — Add database-level firewall setting EXECUTE sp_set_database_firewall_rule N’Allow …