Category: SQL Server

Import BACPAC File Created from Azure SQL Database

Import BACPAC File to On-Premise SQL Server : C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin> SqlPackage.exe /a:import /sf:\\Userdb0.bacpac /tsn:SERVER-SQL\DEV2016 /tdn:Azure_Test /p:CommandTimeout=2400   Error : When you are try to import BACPAC File created from Azure Environment, you might encounter the following error

Tagged with: ,

Import DACPAC file to SQL Server

Import DACPAC File # 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” #

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

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

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)

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

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

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

Stored Procedure Error Logging

Stored Procedure Error Logging Create table to capture Errors Create Stored Procedure to Log Error Invoke Stored Procedure in TRY/CATCH Block USE TEMPDB; GO IF OBJECT_ID(‘APM.ErrorLog’) IS NOT NULL DROP TABLE APM.ErrorLog; GO CREATE TABLE APM.ErrorLog ( ErrorLogID INT IDENTITY(1,

Memory-Optimized Tables

Monitor Memory Optimized Table Space Usage : ; WITH system_allocated_memory ( system_allocated_memory_in_mb ) AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb) + SUM(TMS.memory_allocated_for_indexes_kb) ) / 1024.00) FROM [sys].[dm_db_xtp_table_memory_stats] TMS WHERE TMS.object_id <= 0 ), 0.00) ), table_index_memory ( table_used_memory_in_mb,

Brute force attack on SQL Server

Brute force attack on SQL Server  If your business needs the SQL Server to be accessible on public network, you may be very vulnerable for brute force attacks. Following query will help you identify the failed login attempts and you

When Does SQL Server Trial Edition Expire?

When Does SQL Server Trial Edition Expire? I installed SQL Server Docker Container on Linux Machine running on EC2. I took the AMI Image and launched the existing container and wanted to know when will the SQL Server licence expires

SQL Injection attack on Website hosted on EC2 Machine

SQL Injection attack on Website hosted on EC2 Machine: I setup a website with publicly available dataset. As soon as I launched website, numerous trolls and automated bots sending traffic to identify the vulnerabilities. Safe-Guards I have taken so far :

Install SQL Server Docker Container on Linux

Install SQL Server Docker Container on Linux and Connect through Client Tools I was surprised by ability to run setup and run SQL Server Docker container. It only takes about 2 commands and 2 Minutes. $docker pull microsoft/mssql-server-linux $docker run -e

SQL Server Optimizations for High Concurrency

SQL Server Optimizations for High Concurrency Our business needs very robust, low latency, highly available and durable online transactional system which supports high concurrency for about four weeks in a year. It’s almost like Thanksgiving sale where you mark down very popular item

ScriptOut Change Tracking Tables

ScriptOut Change Tracking Tables SELECT AS Schema_name , AS Table_name , ‘ALTER TABLE ‘ + QUOTENAME( + ‘.’ + QUOTENAME( + ‘ ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) ‘ + CHAR(13) AS SQLScript FROM sys.change_tracking_tables JOIN sys.tables ON

How to become DBA favorite Developer

Application Name : When a connection is made to SQL Server there are by default no way for SQL Server to know what software is making the connection. If several apps are using a shared SQL Server and there are

AWS RDS First Touch Penalty

AWS RDS First Touch Penalty According to AWS Documentation The first time a DB instance is started and accesses an area of disk for the first time, the process can take longer than all subsequent accesses to the same disk

Useful Free Tools and Books

Useful Free Tools Download Links Wonderful Microsoft utilities PerfMon , Profiler ,  Dynamic Management Views and System Tables should provide lot of insights and help in diagnosing and troubleshooting issues. These free tools are nice add-ons and improves the productivity.  Download