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 Update Statistics
Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query's performance will suffer. It is important to update the statistics to make sure query optimizer always generates the best and...
T-SQL Script to Attach and Detach Databases
DBAs and Developers at times may need to detach and attach the databases for various reasons including the following: 1. Moving files to other drives 2. Server migration 2. Upgrades, 3. system maintenance, etc. This script takes away the headache of remembering the...
T-SQL Script to Check Database Compatibility
Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I've seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the...
T-SQL Script to Obtain Database Growth Information
DBA's and Developers often have a need to query the current state of database file size and growth information. This script allows you to obtain file-growth information from single or all databases. It provides the following information: 1. Local Server Name 2....
T-SQL Query to find CPU and NUMA Nodes
I'm often asked on how to identify the number of CPU's (physical) and Numa Nodes (logical CPU's) from SQL Server. Here's a quick and easy way to obtain such information. [crayon-5f9aa5a0e31dd063169299/]