Navigating SQL Server Error Logs

Navigating SQL Server Error Logs
3.9 (77.14%) 7 votes

Introduction

One of the key skills you need as a database administrator or an IT person is generally the ability to monitor systems very carefully. Lack of this key skill can lead to misdiagnosis when troubleshooting issues. SQL Server exposes a number of tools that can aid the DBA in troubleshooting problems that occur in production. The SQL Server Error Log and the SQL Server Agent Log are two of the most important facilities for troubleshooting SQL Server. In this article, we shall explore the ways we can manipulate the Server and Agent logs.

Read More

Monitoring Backups Across Instances

Monitoring Backups Across Instances
3.8 (76%) 10 votes

Introduction

In the last two or three months, I have been asked twice for a solution native to SQL Server that consolidates a backup report for several SQL Server instances across an enterprise. This question came from friends that did not necessarily want to spend money buying a tool but were more inclined to leverage the capabilities of SQL Server. I have thought about two possible ways to achieve this:

  1. Using Linked Servers, catalog views, SQL Agent Jobs and Database Mail
  2. Using Central Management Server

In this article, I will demonstrate the first and hope we shall have a second part of the article sometime later. Read More

Tracking Database Changes Using Working Folder Source Control

Tracking Database Changes Using Working Folder Source Control
4.6 (92.5%) 8 votes

This article talks about a new method to version control a database using a working folder so that historical changes made to the database can be traced back.

Overview

Since this article is based on the new approach to source control a database by overcoming the working folder limitation, it is better to get some basic understanding of the working folder and related things.

Read More

Move Datafiles in SQL Server – Part 2

Move Datafiles in SQL Server – Part 2
3.7 (73.33%) 9 votes

Introduction

In the first part of the two-part series, we explored migrating databases by first updating the master database system catalogs which contain records of the physical location of data files. In the current article, we shall look at two other methods of migrating databases in SQL Server which essentially have the same effect through the approach is different. Read More

How to create Snapshot Replication

How to create Snapshot Replication
3.4 (67.5%) 8 votes

The concept of snapshot replication is simple. It generates and distributes the snapshot of schema and data of articles, appeared at a specific time and updates the changes on subscriptions. When we create snapshot replication, SQL Server creates a Snapshot Agent Job which generates a snapshot of publication database objects. Snapshot is stored on either network location or on the hard drive. You can define the interval to generate the snapshot using the SQL Job schedule. Read More

How to create Transactional Replication

How to create Transactional Replication
3.6 (72.5%) 8 votes

In this article, I am going to demonstrate how to create transactional replication.

Transactional replication generated a snapshot of publication DB objects and data within it. After the snapshot is generated, all the data changes and schema changes occurred on the publisher database are delivered to subscriber databases. In transactional replication, data and schema changes are almost real-time, hence transactional replication can be used to offloading reports and sometimes can be used as DR Site.

Read More

Importance of transaction log in SQL Server

Importance of transaction log in SQL Server
4 (80%) 6 votes

Transaction logs are a vital and important component of database architecture. In this article, we’ll discuss SQL Server transaction logs, importance, and their role in the database migration.

Introduction

Let’s talk about different options for taking SQL Server backups. SQL Server supports three different types of Backups.
1. Full
2. Differential
3. Transaction-log

Before jumping into transaction-log concepts, let’s discuss other basic backup types in SQL Server. Read More

Creating Database Maintenance Plans

Creating Database Maintenance Plans
4 (80%) 6 votes

Using database maintenance plans, we can automate basic database administration activities. Maintenance plans are SQL Server integration service packages which are executed by SQL Server Agent job on configured schedule. Using maintenance plans, we can automate the following tasks.

  1. Shrink a database
  2. Backup a database
  3. Operator notification
  4. Update database statistics
  5. Perform database integrity check
  6. Execute SQL Server Agent jobs
  7. Execute T-SQL script
  8. Reorganize or rebuild indexes

Moreover, we can set up an alert to notify a DBA about the status of maintenance plan execution by enabling the Database Mail.

In this article, I am going to explain how to create a basic maintenance plan to perform database consistency check on a user database. Read More

Using Working Folder to Source Control Database

Using Working Folder to Source Control Database
4.2 (83.33%) 6 votes

This article is a walkthrough of how to use the working folder option of source control for managing SQL Server databases.

In this article, I am also underlining some of the benefits and limitations of using a working folder as compared to other available options to use with source control.

Let us discuss some key concepts before delving into the technical details of this article. Read More

Generating Test Data in SQL Server

Generating Test Data in SQL Server
4 (80%) 9 votes

When testing the functionality of your application or the performance of a specific stored procedure or an ad-hoc query in the development environment, you need to have data stored in your development databases typical or similar to the data stored in the production databases. This is because the performance of a query that is processing 50 records will be different from the performance of the same query that is processing 50M rows. Restoring a copy of the production database to the development database server for testing purposes is not always a valid option, due to the critical data that is stored in these databases and should not be open for all employees to see, unless you are developing a new application and there is no production database yet.

The best and most secure alternative is to fill the development database tables with testing data. Test data generation is useful for testing the performance of the application or a new functionality without changing the production data. There is no single straight-forward way to generate test data that will fit all scenarios, especially when you need to generate large amount of data to test the performance of complex queries and transactions in which you should cover all possible combinations of testing cases. Read More