Home » Computers » Data Recovery

SQL Server Replication for Data Storage for Your Business

Aug 18, 2007
Database management systems are among the most important software systems for businesses in the information age. SQL Server is an enterprise level RDBMS provided by Microsoft and is widely used in the business world. SQL Server provides the technology that allows you to replicate your data to different servers thus allowing your company data to be stored in more than one location - this process is called replication.

Replication is the process of sharing data between databases in different locations. Using replication, you create copies of the database and share the copy with different users. This allows them to make changes to their local copy of the database and later synchronize the changes to the source database.

Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.

You can automatically backup a database by keeping a replica on a different computer. Replication allows you to continue making changes online.

You can replicate a database on additional network servers and reassign users to balance the loads across those servers.

Microsoft SQL server uses publishing industry model to represent the components and processes in replication architecture.

Based on the publication model we can identify the following entities for the SQL Server replication model: Publisher, Distributor, Agent, Subscriber, Articles, Publications, and Subscriptions.


Publisher is a server that makes the data available for subscription to other servers. In addition to making data available for replication, a publisher also identifies what data has changed at the subscriber during the synchronizing process. Depending on the type of replication, changed data is identified at different instances. We will learn more about Replication types in the Replication Types section.


Distributor maintains the Distribution Database. The role of the distributor varies depending on the type of replication. Two types of Distributors are identified: Remote distributor and Local distributor. Remote distributor is separate from publisher and is configured as a distributor for replication. Local distributor is a server that is configured as a publisher and a distributor.


Agents are the processes that are responsible for copying and distributing data between Publisher and subscriber.


Subscriber is a server that receives and maintains the published data.


An article can be any database object, viz. Tables, Views, Indexed views, Stored Procedures, User defined functions.


Publication is a collection of articles.


Subscription is a request for copy of data or database objects to be replicated.

Subscription Types

Changes to the subscriptions at the publisher can be replicated to subscribers via PUSH subscription or PULL subscription. With Push subscription the publisher is responsible for synchronizing all the changes to the subscriber without the subscriber asking for those changes.

With Pull subscription the subscriber initiates the replication instead of the publisher.

Replication Types

Microsoft SQL Server supports the following types of replication: Snapshot Replication, Transactional Replication, and Merge Replication.

Snapshot Replication

Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.

Transactional Replication

In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.

Merge Replication

Merge replication provides advantages of both Snapshot replication and Transactional replication. The initial snapshot applied to the subscribers and then SQL server tracks changes to the data at publisher and subscriber levels. The data is synchronized on a scheduled basis or on demand.

Replicating to Third Party Hosts

SQL Server can easily be configured to replicate data to a third party supplier over the internet. Thus allowing the third party supplier to house an up to date copy of your most important company databases. To this you will need to work closely with the third party supplier, but you have to ensure that your publications allow pull and anonymous subscriptions.

1. configure the publishers or distributor to listen on TCP/IP
2. configure the publication to use FTP
3. configure a subscription to use FTP
About the Author
SQL Server Replication is the process of replicating data from your company database to a database stored on another SQL Server. The second server can be hosted by a third party in Global Data Vault. Protect your company data at http://www.globaldatavault.com.
Please Rate:
(Average: Not rated)
Views: 232
Print Email Report Share
Article Categories
    • Artists
    • Gambling
    • Humanities
    • Humor
    • Movies
    • Music
    • Photography
    • Tattoos
    • Television
    • Classic Cars
    • Motorcycles
    • Recreational Vehicles
    • SUVs
    • Trucks
    • Vans
    • Branding
    • Business Opportunities
    • Careers and Jobs
    • Corporate
    • Customer Service
    • Direct Mail
    • Entrepreneurship
    • Ethics
    • Financing
    • Franchising
    • Home-Based Business
    • Human Resources
    • Import and Export
    • Leadership
    • Management
    • Market Research
    • Marketing and Advertising
    • Negotiation
    • Network Marketing
    • Networking
    • Organizational
    • Presentation
    • Project Management
    • Public Relations
    • Small Business
    • Strategic Planning
    • Team Building
    • Telemarketing
    • Training
    • Ask an Expert
    • College and University
    • Home Schooling
    • K-12
    • Languages
    • Online Education
    • Psychology
    • Accounting
    • Credit
    • Currency Trading
    • Debt Consolidation
    • Insurance
    • Investing
    • Leasing
    • Loans
    • Mortgage
    • Mutual Funds
    • Personal Finance
    • Stock Market
    • Structured Settlements
    • Taxes
    • Wealth Building
    • Coffee
    • Cooking
    • Gourmet
    • Recipes
    • Wine and Spirits
    • Acne
    • Aerobics
    • Alternative Medicine
    • Beauty
    • Cancer
    • Cosmetics
    • Depression
    • Diabetes
    • Diseases and Conditions
    • Fitness Equipment
    • Fitness
    • Hair Loss
    • Heart Disease
    • Medicine
    • Men's Health
    • Muscle Building
    • Nutrition
    • Skin Care
    • Supplements and Vitamins
    • Weight Loss
    • Women's Health
    • Yoga
    • Arts and Crafts
    • Babies
    • Collecting
    • Elderly Care
    • Genealogy
    • Hobbies
    • Parenting
    • Pets
    • Pregnancy
    • Woodworking
    • Feng Shui
    • Gardening
    • Home Appliances
    • Home Security
    • Interior Design
    • Landscaping
    • Affiliate Programs
    • Article Marketing
    • Auctions
    • Audio
    • Banner Advertising
    • Blogging
    • Broadband
    • Domain Names
    • E-Books
    • E-Commerce
    • Email Marketing
    • Ezines and Newsletters
    • Forums
    • Internet Marketing
    • Link Popularity
    • Pay-Per-Click
    • Podcasting
    • RSS
    • Search Engine Marketing
    • Search Engine Optimization
    • Security
    • Social Media
    • Spam
    • Video
    • Viral Marketing
    • Web Design
    • Web Development
    • Web Hosting
    • Copyright
    • Cyber Law
    • Intellectual Property
    • National, State, Local
    • Patents
    • Regulatory Compliance
    • Trademarks
    • Buying
    • Selling
    • Baseball
    • Basketball
    • Boating
    • Cycling
    • Extreme Sports
    • Fishing
    • Football
    • Golf
    • Hockey
    • Hunting
    • Martial Arts
    • Running
    • Scuba Diving
    • Soccer
    • Swimming
    • Tennis
    • Dating
    • Divorce
    • Marriage
    • Weddings
    • Astrology
    • Buddhism
    • Christianity
    • Faith
    • Hinduism
    • Islam
    • Judaism
    • Meditation
    • Metaphysical
    • New Age
    • Cable and Satellite TV
    • Cell Phones
    • Communication
    • Gadgets and Gizmos
    • GPS
    • Satellite Radio
    • Video Conferencing
    • VoIP
    • Addictions
    • Coaching
    • Goal Setting
    • Motivational
    • Stress Management
    • Time Management
    • Clothing
    • Electronics
    • Fashion
    • Gifts
    • Jewelry
    • Causes and Organizations
    • Environment
    • History
    • Holidays
    • Men's Issues
    • Nature
    • Philosophy
    • Politics
    • Women's Issues
    • World Affairs
    • Air Travel
    • Camping
    • Cruises
    • Destinations
    • Outdoors
    • Article Writing
    • Book Reviews
    • Copywriting
    • Fiction
    • Non-Fiction
    • Poetry
    • Quotes
    • Screenplay
    • Tools and Resources