SQL Server General DBA

Using Temp Tables in SSIS

Temporary tables are created in the TempDB database, which persists for a particular session. The objective is to maintain that session, until the temp table information is used and dump the data into a physical table. To maintain a session in SSIS, there is a ‘Retain Same Connection’ property of the Connection Manager. If we […]

SQL Server Disaster Recovery Test

This article describes a simple procedure we implemented to ensure that the data on our the database on the Disaster Recovery server is consistent with the production server. Our DR Server is a separate server which is located in a separate data centre. The DR server receives data from the production database server via Transactional […]


Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT. Let’s create a table named Student using […]

Accessing SSRS Reports Using Report Viewer

Introduction Reports developed in the report designer need to be published to the report server in order to make them available to the end users. Once the reports have been deployed on the report server they can be managed through the report manager web application but that is more relevant to a site administrator as […]

Automating SQL Server Transactional Log Shipping Alerts

DBAs often follow a practice of using the Transactional Log Shipping Monitor in SSMS to ensure that the Log Shipping is running without any issues. However, it is not always possible to do 24/7 monitoring using the Log Shipping monitor. The alert messages generated by third party monitoring systems can be overkill and generate messages […]

Create Alert Messages When SQL Server Transactional Replication Is Blocked

Recently we had an issue in our Production environment, on the Reporting Database Server the Transactional Replication procedure was being blocked due to execution of some heavy T-SQL queries and as a result business users were unable to view the latest data. During our investigation, I had a looked at the Synchronisation status of the […]

Understanding SQL Server Fixed Server Roles – Part 1

When considering the security and protection of the database engine, one of the first considerations that comes to mind is SQL Server Fixed Server Roles. The Server Roles are named Fixed Server Roles as the user cannot alter these under any conditions. Assigning the appropriate roles to the user helps keep the SQL Server Environment […]

Agile Database Development – The Sprint

In Moving to Agile Database Development I discussed the reasons that led our organisation to the decision to incorporate Agile methods into our software development. That part ended with a lead into our first Sprint. For readers who are already familiar with development in sprint cycles will the following explanations not be completely new, but […]

Find Orphaned Users In SQL Server

Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. The reason for this is that whenever a user is created, a login name and SID will be created. Each database user is mapped with an SID. This SID is different from one SQL Server instance to […]

Using Entity Framework With An Existing SQL Server Database (Code Second)

Entity Framework originally shipped with two modes – Model First which allowed for the entity model to be created using the designer UI and Database First which created the EF model from a database. EF 4.1 introduced Code-First development, enabling developers to simply generate the EF model and database directly from code. This method rapidly […]