SQL Server Database Replication

SQL Server Database Replication
3.4 (68%) 5 vote[s]

Database replication is the technology to distribute data from the primary server to secondary servers. Replication works on Master-slave concept where Master database distributes data to one or multiple slave servers. Replication can be set up between multiple SQL Server instance on the same server, OR it can be set up between multiple database servers within same or geographically separated data centers. Read More

Overview of Data Compression in SQL Server

Overview of Data Compression in SQL Server
4.5 (90%) 4 vote[s]

The database is a critical and vital part of any business or organization.  The growing trends predict that 82% of enterprises expect the number of databases to increase over the next 12 months. A major challenge of every DBA is to discover how to tackle massive data growth, and this is going to be a most important goal. How can you increase database performance, lower costs, and eliminate downtime to give your users the best experience possible? Is data compression is an option? Let’s get started and see how some of the existing features can be useful to handle such situations.

In this article, we are going to learn how the data compression solution can help us optimize the data management solution. In this guide, we’ll cover the following topics:

  • An overview of compression
  • Benefits of compression
  • An outline about data is compression techniques
  • Discussion of various types of data compression
  • Facts about data compression
  • Implementation considerations
  • and more…

Read More

Navigating SQL Server Error Logs

Navigating SQL Server Error Logs
3.9 (77.14%) 7 vote[s]

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

Move Datafiles in SQL Server – Part 2

Move Datafiles in SQL Server – Part 2
3.8 (76%) 10 vote[s]

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.3 (66.67%) 9 vote[s]

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 vote[s]

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
4.1 (82.86%) 7 vote[s]

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.8 (76.67%) 6 vote[s]

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 vote[s]

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