Tag: sql server

How to create Transactional Replication

Nisarg Upadhyay
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

Importance of transaction log in SQL Server

Prashanth Jayaram
Importance of transaction log in SQL Server
5 (100%) 2 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

Nisarg Upadhyay
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

Using Working Folder to Source Control Database

Haroon Ashraf
Using Working Folder to Source Control Database
3.8 (75%) 4 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

Ahmad Yaseen
Generating Test Data in SQL Server
4.7 (93.33%) 3 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

Configuring Database Mail in SQL Server

Nisarg Upadhyay
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

Passing Data table as Parameter to Stored Procedures

Ben Richardson
Passing Data table as Parameter to Stored Procedures
3.6 (72%) 5 votes

Real-world database applications need to make multiple requests from the front end to the database in order to carry out all sorts of functions.

If an application is data-intensive, such as the ones used in banks or airports etc, the number of data trips can be huge. Each request to a database utilizes bandwidth and requires time to execute. Without the table-valued parameters, a front application needs to make multiple data trips in order to manipulate multiple rows of data. However, with table-valued parameters, multiple rows can be inserted, updated and deleted from a database using a single parameterized command that takes a table-valued parameter. Read More

Unit Testing Report Procedures – Jump to Start TDDD Part-4

Haroon Ashraf
Unit Testing Report Procedures – Jump to Start TDDD Part-4
3.9 (77.5%) 8 votes

This article is a walk-through of creating a stored procedure through test-driven database development (TDDD) in order to meet a reporting requirement that cannot be fulfilled by using a database view.

This article also provides some useful hints about cases of preferring stored procedure over database view as a potential object that is going to fulfill the business requirement(s). Read More

Backup and Restore FILESTREAM-Enabled Database

Nisarg Upadhyay
Backup and Restore FILESTREAM-Enabled Database
4.1 (82.86%) 7 votes

In my previous articles, I explained how to create and configure the FILESTREAM feature in SQL server instance. Moreover, I demonstrated how to create a table that has a FILESTREAM column and hot to insert and delete the data from it.

In this article, I am going to explain how to backup and restore the FILESTREAM-enabled dataase. Moreover, I am going to demonstrate how to restore FILESTREAM filegroup without making database offline. Read More

Storing Files in SQL Database Using FILESTREAM – Part 2

Nisarg Upadhyay
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