Setup SQL Server on Linux VM

### 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 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  

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 …

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 …