Home > SQL Server Tips > Database Management and Administration > 15 SQL Server replication tips in 15 minutes
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

DATABASE MANAGEMENT AND ADMINISTRATION

15 SQL Server replication tips in 15 minutes


Hilary Cotter, Contributor
07.26.2005
Rating: -4.75- (out of 5)


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


The following list of tips and tricks will help you enhance replication performance and monitoring.

Keep in mind that transactional replication offers the best performance of all the replication types. Far too often, clients use snapshot or merge replication when transactional replication is the best choice. For instance, you should use snapshot if your data changes infrequently, if a large portion of data changes or if clients don't require an up-to-date data set.

In all other cases, consider transactional replication as only the changes that will go across the wire to your clients. Snapshot will send the entire data set (potentially in gigabytes) each time, whereas transactional could be sending megabytes. The difference will be especially acute if you are replicating over low-bandwidth lines.

Replication tips: Performance

    1. Replicate tables without primary keys
    2. Achieve bi-directional replication
    3. Replicate stored procedure execution
    4. Create lightweight tables on the subscriber
    5. Put subscription database in Bulk Logged recovery model
    6. Use the independent agent option
    7. Change your polling interval
    8. Maintain your log
    9. Set performance profiles

Replication ...


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   



RELATED CONTENT
SQL Server Replication
SQL Server Mailbag: Migrating down to Standard Edition
Push vs. pull: Configuring SQL Server replication
Top load balancing methods for SQL Server
New replication features in SQL Server 2008 and what they mean to you
SQL Server database replication tutorial
Licensing a standby server for SQL Server replication
Upgrade live applications to SQL Server 2005 for high availability
Tool to synchronize two SQL Server databases
Simplify SQL Server replication
Replication techniques in SQL Server

Microsoft SQL Server Performance Monitoring and Tuning
Using traces in SQL Server Profiler
SQL Server Mailbag: CALs, witnesses and unwanted changes
SQL Server Mailbag: Data restoration and DB property management
Working with IntelliSense in SQL Server 2008 Management Studio
SQL Server Mailbag: Stored procedures, triggers and SSRS reports
Troubleshooting Distributed Transaction Coordinator errors in SQL Server
Clearing the Windows page file and its effect on server performance
Optimizing SQL Server indexes –- even when they're not your indexes
Performance implications of transaction log autogrowth in SQL Server
The short course on how SQL Server really works

Database Management and Administration
Using traces in SQL Server Profiler
Meet compliance requirements with improved database security practices
Hardening the network and OS for SQL Server security
Securing the server and database in SQL Server
How SQL Server 2008 components impact SharePoint implementations
Troubleshooting Distributed Transaction Coordinator errors in SQL Server
Achieving high availability and disaster recovery with SharePoint databases
Clearing the Windows page file and its effect on server performance
Deploying a SQL Server virtual appliance for Microsoft Hyper-V
How to create SQL Server virtual appliances for Hyper-V

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
replication  (SearchSQLServer.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


tips: Monitoring

    1. Check subscriber status with Replication Monitor Group
    2. Expire subscribers

Replication tips: Miscellaneous

    1. Replicate to objects with different schemas
    2. Reveal replication commands
    3. Check number of commands to be replicated
    4. Prevent updates, inserts and deletes from being replicated

About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

More information from SearchSQLServer.com

  • Book Excerpt: Understanding replication methods: Snapshot, transaction and merge
  • Tip: Heterogenous data replication using SQL Server
  • Tip: SQL Server version control


    • Rate this Tip
    To rate tips, you must be a member of SearchSQLServer.com.
    Register now to start rating these tips. Log in if you are already a member.




    DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.



    SQL Server Development - .NET, C#, T-SQL, Visual Basic
    HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
    About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
    SEARCH 
    TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

    TechTarget Corporate Web Site  |  Media Kits  |  Site Map




    All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy     		  TechTarget - The IT Media ROI Experts