Free E-Book – Nuts & Bolts of Database Mirroring

With the introduction of SP1 in SQL Server 2005, Microsoft introduced a new high availability technology called Database Mirroring. It works by transferring transaction log records for a database from the primary server to the secondary server, thereby maintaining a hot standby server. As with normal SQL Server operation, with Database Mirroring data changes are recorded in the transaction log before any changes to actual data pages are made. The log records are first placed in the principal database‘s log buffer in memory and then hardened to disk. Those transaction log records are copied to and replayed on the mirror server‘s
database. Because the mirror database replays the principal’s transaction log records, it duplicates the principal‘s database changes on the mirror‘s database.

Check if PowerShell is Installed on SQL Server

Many database related tasks can be automated using PowerShell. This T-SQL script can be used to check the following:
Is PowerShell installed on your local SQL Server?
Is it enabled for script execution?
What is the version of PowerShell installed on your machine?
You can obtain all the above information using the below T-SQL script. Since we have to obtain information from the server registry and from the output of PowerShell.exe file, we will be using both sys,xp_regread and xp_cmdshell extended stored procedures in this example:

Server side lightweight SQL Server tracing

There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by creating a server side trace.

T-SQL Script to find High CPU Queries

There are times when you would want to know what query in SQL Server may be causing CPU spikes. There are many reason why a query may hit the CPU and the best way to troubleshoot it to: Identify the query that is causing high CPU Identify the total time taken by query...

Why do Queries behave differently in production

You may have experienced something similar where the query or a stored procedure performs bad when deployed in the production and the same query / stored procedure works perfectly well in the test or development environment. Behind the scenes (Query Internals) It is...

Improve Performance of SQL Server File Operations

Ever wondered why SQL Server takes long time to perform below operations? Create a database Expand data files for additional growth Automatic data file growth due to auto-grow settings Restoring a database, etc. Today we will be looking in to a powerful and yet not...

Why to Optimize Ad-Hoc Workload in SQL Server

By default, when a query is executed in SQL Server, it compiles and generates an execution plan for that query and stores that plan in the plan cache. Now if this happens to be a one-off/ad-hoc query that will never be run again, then the newly generated plan is...

Automatic database backups in Azure Cloud

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didnt have proper backups in place. Wouldnt it be great if there was a way that DBAs and engineers didnt have to worry about...