SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Archives: August 2012

SQL Server Guest User – Still a Serious Security Threat

One of the security recommendation inside SQL Server Security Best Practice white paper for guest user is that, to disable guest access in every database (expect “master”, “msdb” and “tempdb”) on SQL Server instance, and it should not be used in any circumstances. By default, guest user exists in all… Read more

4 comments, 5,254 reads

Posted in Basit's SQL Server Tips on 28 August 2012

Transparent Data Encryption (TDE) with Database Mirroring

Database security is essential for the organisations working in healthcare and financial sector because the databases in such organisations contain sensitive and confidential information about their customers. The organisation working in such sectors also falls under the boundaries of data regulatory and compliance requirements (such as HIPAA, PCI-DSS, SSAE16 etc.)… Read more

2 comments, 1,717 reads

Posted in Basit's SQL Server Tips on 23 August 2012

Tips For Minimizing Deadlocks in SQL Server

Database servers are configured in such a way that they can service multiple requests from multiple users. Obviously this increases the likelihood of conflicts because multiple processes can request access to same resources at the same time. This type of conflict is also known as block or blocking. Blocking usually… Read more

0 comments, 2,449 reads

Posted in Basit's SQL Server Tips on 23 August 2012

Different ways to audit SQL Server security

Today, my first article (ie. Different ways to audit SQL Server security) published on SearchSQLServer.com. In this article, I discussed about the various options available in SQL Server for auditing security-related events, which helps companies to meet various regulatory compliance requirements.

Read more

0 comments, 1,075 reads

Posted in Basit's SQL Server Tips on 17 August 2012

Introduction to Windows Azure SQL Database (Part 1)

Windows Azure SQL Database also known as SQL Server Data Services or simply SQL Azure is a Microsoft’s cloud services that offers a capabilities on Microsoft SQL Server 2012 (such as Amazon S3 and Amazon Relational Database Services), as part of the Azure Services Platform. Windows Azure SQL Database… Read more

0 comments, 1,903 reads

Posted in Basit's SQL Server Tips on 16 August 2012

Find failed SQL Agent jobs using Transact-SQL script

Today, I’m sharing the following T-SQL script which you can use to find which SQL Server Agent Jobs failed yesterday. I use this script as part of my daily server monitoring SSIS package, which executes this script on all production SQL server and then sends email to our group:


Read more

1 comments, 2,272 reads

Posted in Basit's SQL Server Tips on 15 August 2012

How to list all CLR objects inside your database?

I wrote the following query that returns the list of all CLR functions/procedures/objects exists inside your database:

SELECT o.object_id AS [Object_ID]
      ,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] 
      ,o.type_desc AS [CLRType] 
      ,o.create_date AS [Created] 
      ,o.modify_date AS [Modified] 
      ,a.permission_set_desc AS [CLRPermission] 
FROM sys.objects o 
INNER JOIN sys.module_assembly_usages ma
    ON o.object_id =…

Read more

0 comments, 1,225 reads

Posted in Basit's SQL Server Tips on 12 August 2012

How to find SQL Agent Jobs without Notification Operator Configured?

We can either use SQL Server Management Studio or use Transact-SQL query to find any SQL Agent jobs that have been setup without notification operator.

The following are the steps to find SQL Agent jobs without notification operator via SQL Server Management Studio:

  1. In Object Explorer, expand SQL Agent, then…

Read more

2 comments, 2,367 reads

Posted in Basit's SQL Server Tips on 12 August 2012

Using a Dedicated Administrator Connection (DAC) on SQL Server

Dedicated Administrator Connection (DAC) allows administrators emergency access to SQL Server via SQL Server Management Studio (SSMS) or via command prompt (SQLCMD utility) when SQL Server is not responding to normal connections, so that they can execute the diagnostic functions (typical set DBCC commands, DMVs and system stored procedures) to… Read more

0 comments, 1,468 reads

Posted in Basit's SQL Server Tips on 9 August 2012

Recovering from out-of-disk space conditions for tempdb

Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

  • Tracking versions for row-versioning concurrency.
  • Performing bulk load operations on tables with triggers enabled.
  • Running…

Read more

1 comments, 3,134 reads

Posted in Basit's SQL Server Tips on 6 August 2012

Creating and Managing User-Defined Roles in SQL Server 2012

SQL Server 2012 introduces user-defined server roles. These user-defined server roles are similar to fixed server roles with only difference is that they are created by SQL Server Administrator. SQL Administrators can create, delete or manage these user-defined server roles using T-SQL or SQL Server Management Studio. User-defined roles can… Read more

0 comments, 2,812 reads

Posted in Basit's SQL Server Tips on 2 August 2012

Performing Unattended Installs of SQL Server 2012

In my post here, I’ve discussed the procedure to perform attended installation of SQL Server 2012 on a Windows Server 2008.

Now imagine, you have multiple SQL Server 2012 instances to install with the same configuration on the multiple servers and you want this task to be done as…

Read more

3 comments, 9,576 reads

Posted in Basit's SQL Server Tips on 2 August 2012