How to create Transactional Replication

How to create Transactional Replication
4 (80%) 1 vote

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

Creating Database Maintenance Plans

Creating Database Maintenance Plans
3.7 (73.33%) 3 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

Configuring Database Mail in SQL Server

Configuring Database Mail in SQL Server
3.3 (65%) 4 votes

SQL Server Database Mail has been introduced in SQL Server 2005. Database Mail is a component that can send emails using SQL Server Engine. Using Database Mail, an administrator or a developer can send query output to an end user. DBAs can configure it to get email alerts and notifications. Database Mail uses SMTP (Simple Mail Transfer Protocol) to deliver emails to recipients.

In this article, I am going to demonstrate how to configure SQL Server Database Mail. Read More

Storing Files in SQL Database Using FILESTREAM – Part 2

Storing Files in SQL Database Using FILESTREAM – Part 2
3.7 (73.33%) 6 votes

In my previous article, I described how to configure FILESTREAM in SQL Server, create FILESTREAM-enabled database and tables. Moreover, I demonstrated how to insert and delete data from the FILESTREAM table.

In this article, I am going to demonstrate how to insert multiple files in a FILESTREAM table using T-SQL. Read More

SQL Database Administrator Interview Tips

SQL Database Administrator Interview Tips
3.7 (74.29%) 7 votes

Interview is the most significant step of SQL database administrator recruitment. During the interview, a technical person or interviewer asks questions about the technical experience of a candidate. The purpose of these questions is to understand candidate’s experience about SQL Server. The answers to the questions will measure your knowledge and experience. If you cannot prove your experience in this interview, most probably the decision on your employment will be negative. First of all, you should be calm during the interview. This advice is the most significant key to success. The first question of an interview is about your past experience and skills. Being relevant to this question, you should describe your old and current job responsibilities and skills. You must be sure that you demonstrate all of your knowledge and experience to the interviewer. Because this introduction makes the first impact on your potential employer. Read More

Moving Existing Table From Primary Filegroup to Different Filegroup

Moving Existing Table From Primary Filegroup to Different Filegroup
4.5 (90%) 4 votes

In this article, I am going to explain how to move a table from the Primary filegroup to the Secondary filegroup. First, let’s understand what are datafile, filegroup, and type of filegroups.

Database Files and Filegroups

When SQL Server is installed on any server, it creates a Primary data file and Log file to store data. The Primary data file stores data and database objects like tables, index, stored procedures, etc. Log files store information required to recover transactions. Data files can be clubbed together in filegroups. Read More

Move Datafiles in SQL Server – Part 1

Move Datafiles in SQL Server – Part 1
3.6 (72.5%) 8 votes

Introduction

There are a number of situations which would warrant the movement of database files or transaction log files from one volume to another on the same server. These may include:

  1. The need to format the volume assuming it was not formatted properly when SQL Server was installed. Recall that when installing SQL Server, it is recommended that 64K allocation unit size is used to format the volumes. If this is not done at the point of installation and needs to be done later, it will obviously require preserving a backup of the database fist or creating a new, properly formatted volume and moving the database to this new volume.
  2. The need to use a new volume assuming the limits have been reached for the underlying storage. A good example would be the 2TB limit of a VMware Data Store. This is the case as of VSphere 5.0. Higher versions of VSphere have much higher limits.
  3. The need to improve performance by managing IO. One more reason you may want to move datafiles is performance. There are cases where a database is created with multiple datafiles all sitting on one disk until it becomes obvious, as the database grows, that the you have created a “hot region” in the storage layer. One solution would be creating new data files and rebuilding clustered indexes, another would be moving data files.

Read More

Basics of sys.dm_exec_requests

Basics of sys.dm_exec_requests
4.4 (87.27%) 11 votes

Performance monitoring and troubleshooting in SQL Server is a vast topic. In SQL Server 2005, dynamic management views, also known as DMV’s, have been introduced and became an essential helping tool for diagnosing SQL Server performance problems. At the same time, we can use dynamic management views for Azure SQL Database. Some of them can differ from SQL Server on-premise database but the logic of work is still the same. Microsoft has very good documentation about dynamic management views. The only thing,  you need to be careful about the version and product validation of dynamic management views. Read More

Main Usage of sys.dm_os_wait_stats

Main Usage of sys.dm_os_wait_stats
4.5 (90%) 6 votes

As you know, the main responsibility of the database administrator lies in the monitoring of the SQL Server performance and intervening in determined time. You can find several SQL Server performance monitoring tools in the market but sometimes we need additional information about SQL Server performance to diagnosis and troubleshoot the performance issues. So we must have enough information about SQL Server Dynamic Management Views to handle issues about SQL Server.

Dynamic Management View (DMV) is a concept that helps us to discover SQL Server Engine performance metrics. DMV was first announced in SQL Server 2005 version and it continued in all versions of SQL Server afterward. In this post, we will talk about particular DMV whose database administrator must have enough information. This is sys.dm_os_wait_stats.

Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Fixing Data Loss Using Log Shipping with Delayed Recovery
4 (80%) 5 votes

Introduction

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More