How to create Transactional Replication

Nisarg Upadhyay 56 views
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 134 views
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 530 views
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 273 views
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

Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure

Haroon Ashraf 778 views
Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure
3.3 (66.67%) 3 votes

This article provides a walkthrough of database unit testing a stored procedure which contains a utility procedure within it.

In this article, I am going to discuss a database unit testing scenario when a main stored procedure depends on a utility procedure and the main procedure needs to be unit tested in order to make sure that the requirements are met. The key is to ensure that a unit test can only be written for a single unit of code which means we need one unit test for the main procedure and another unit test for the utility procedure. Read More

Generating Test Data in SQL Server

Ahmad Yaseen 362 views
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 466 views
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 1,661 views
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

How to Test Azure Web Application Performance through Microsoft Azure and Visual Studio

Amarendra Babu 459 views
How to Test Azure Web Application Performance through Microsoft Azure and Visual Studio
4.1 (82.22%) 9 votes

Before deploying your application into production, doing a performance load test is imperative for measuring future performance and ensure that your application is production-ready.

Testing is essential for every application to make sure that any application works and performs according to the desired requirements. During the application testing process, we can attempt and find out if any imperfections are remaining in the application. There are numerous sorts of testing like functional testing, unit testing, acceptance testing, and integration testing. We compose Functional and UI tests to see whether the application is working as per the requirements. Read More

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

Haroon Ashraf 530 views
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