Refresh database from SQL 2008 to SQL 2005 by using import/Export

Today I got request refresh database from Production to QA; my production server is SQL 2008 R2 and QA environment is SQL 2005. Well is it possible to downgrade database version or try to make SQL fool by detaching and attaching data and log files from higher version to lower. Database on SQL 2008 has bit different file structure and system tables and SQL 2005 does not understand the SQL 2008 structure hence cannot restore or attach/detach. Continue reading

Advertisements

SQL Server – Lock Pages in Memory

This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM). Continue reading

Start SQL Server in Single User Mode

Today I received a mail from one of my Blog reader. He wanted to know how to start SQL Server in Single User mode?

 Continue reading 

SQL Server : How to move or add Tempdb data and log files

Problem:

– TempDB has grown big and the current disk drive does not have enough space.
– Adding TempDB data file to another physical drive helps to improve performance, as they can be read simultaneously.

Solution : Here I have tried to explain how can we move tempdb data files from one place to another. Continue reading

SQL Server 2008 R2 : LOG SHIPPING – A VERY LARGE DATABASE

Scenario:  A couple of days back, when we were setting up a DR plan for one of our most mission critical DB server which was hosting around 10 databases having size of 10,23,5,7.4,1.8,6.2,7.9,5.53,8.7 and 125 GB respectively. I decided to implement Log Shipping as a part of the DR strategy because the DR server (Secondary Server) was never going to be used for Reporting purpose; therefore Log Shipping was the best choice. Implementing Log Shipping for small databases is relatively Simple as one can easily do it using the GUI but for large database GUI is not a good option as the database on the destination server will be always out of sync. So a question arises on “How do we accomplish this task?”. In this article, I am going to explain you’ll the detailed steps which we followed to implement the same. Continue reading

Transactional Replication – A Much Better Approach For Alert Messages

As  DBAs, we are often required to set up alert notification emails for various tasks. For example, there might be a requirement to set up notification alerts if a particular backup job fails. A while back, I  came across the situation where I was asked to write a script which sent an email alert to a particular group of users if there was an issue with the Transactional Replication. The users were not happy with the alert message generated by the pre-defined alerts introduced by Microsoft in SQL Server Management Studio since they were not able to understand the description of the message – since the body as well as the heading of the email were not easily interpreted. After gathering the requirements, I decided to implement a more functional email alerts system. Continue reading

MOVING SYSTEM DATABASE TO A DIFFERENT LOCATION IN SQL SERVER 2008 R2

Scenario:

A couple of days back, I was examining the configuration of one of our production DB servers. This server was involved in Merge Replication. All the database files on this server were dumped on one SAN drive of a subsequently small size. The replication cleanup job for some reasons was not able to delete the historical records from the system tables present in the distribution database due to which all of a sudden the drive which contained the data files as well as the log files got filled up hardly leaving any space for the SQL Server to start. After a detailed examination of the system, I came up with the following recommendations: Continue reading