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
Author: Nisarg Upadhyay
Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
How to create Transactional Replication
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.
Creating Database Maintenance Plans
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.
- Shrink a database
- Backup a database
- Operator notification
- Update database statistics
- Perform database integrity check
- Execute SQL Server Agent jobs
- Execute T-SQL script
- 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
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
Backup and Restore FILESTREAM-Enabled Database
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
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
Storing Files in SQL Database Using FILESTREAM – Part 1
FILESTREAM has been introduced by Microsoft in 2008. The purpose was to store and manage unstructured files more effectively. Before FILESTREAM was introduced, the following approaches were used to store the data in SQL server:
- Unstructured files can be stored in the VARBINARY or IMAGE column of a SQL Server table. This approach is effective to maintain transactional consistency and reduces the file management complexity, but when the client application reads data from the SQL table, it uses SQL memory which leads to poor performance.
- Instead of storing the entire file in the SQL table, store the physical location of the unstructured file in SQL Table. This approach gives huge performance improvement, but it does not ensure the transactional consistency moreover file management was difficult too.
Moving Existing Table From Primary Filegroup to Different Filegroup
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
Run Excel Macro using SSIS Script Task
When we export data from SQL server table to excel file by using SQL Server integration service package, data in excel file column exports into text format. Even though the column values are Integer or Decimal, it is stored in text format. For example, I am retrieving the values of “CreditLimit” column from the product table. Data format changes automatically and stores in text format in excel file.
Conditional Split Transformation: Export Data from SQL Server into Oracle and MySQL Databases
In this article, I have explained how we can divide and export data of SQL Server database table into Oracle and MySQL databases. That process is based on the condition defined in SSIS as “conditional split transformation”. Conditional split transformation is like case statement of any programming language. Using conditional, we can redirect the output of a component in multiple destinations that is based on a condition defined in the conditional split component. Read More