Programming4us
         
 
 
SQL Server

Microsoft SQL Server 2008 R2 : Scripting Replication

2/9/2012 9:12:45 AM
Earlier, it was strongly suggested that you generate SQL scripts for all that you do because going through wizards every time you have to configure replication is a difficult way to run a production environment. In the example in the preceding section, you always chose to generate these scripts as you built up the replication configuration. This was only half the scripts needed, however. You must also generate the breakdown scripts (that is, those that drop and remove replication components) to remove each component of the replication topology in case you need to start from scratch or, as an example, rebuild a subscriber that is completely nonfunctional. As you can see in Figure 1, SQL Server Management Studio has a great feature that allows the complete generation of all aspects of replication topology (including disabling and removing replication).
Figure 1. A script-generation feature for all replication topology components.

Note

Remember that working from scripts minimizes the errors you make while supporting your data replication environments (especially at 3:00 a.m.).


The following example shows the SQL scripts needed to generate the part of the data replication configuration you just built with the wizard:

------------------------------
-- From distribution Server --
------------------------------
/****** Scripting replication configuration. Script Date: 6/30/2009 10:49:03 AM
******/
/****** Please Note: For security reasons, all password parameters were scripted
with either NULL or an empty string. ******/

/****** Begin: Script to be run at Distributor ******/

/****** Installing the server as a Distributor. Script Date: 6/30/2009 10:49:03 AM
******/
use master
exec sp_adddistributor @distributor = N'DBARCH-LT2\SQL08DE02', @password = N''
GO

-- Adding the agent profiles
-- Updating the agent profile defaults
exec sp_MSupdate_agenttype_default @profile_id = 1
GO
exec sp_MSupdate_agenttype_default @profile_id = 2
GO
exec sp_MSupdate_agenttype_default @profile_id = 4
GO
exec sp_MSupdate_agenttype_default @profile_id = 6
GO
exec sp_MSupdate_agenttype_default @profile_id = 11
GO

-- Adding the distribution databases
use master
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\
Program Files\Microsoft SQL Server\MSSQL10.SQL08DE02\MSSQL\Data', @data_file =
N'distribution.MDF', @data_file_size = 6, @log_folder = N'C:\Program Files\
Microsoft SQL Server\MSSQL10.SQL08DE02\MSSQL\Data', @log_file =
N'distribution.LDF', @log_file_size = 3, @min_distretention = 0,
@max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

-- Adding the distribution publishers
exec sp_adddistpublisher @publisher = N'DBARCH-LT2\SQL08DE01', @distribution_db =
N'distribution', @security_mode = 1, @working_directory = N'C:\Program
Files\Microsoft SQL Server\MSSQL10.SQL08DE02\MSSQL\ReplData', @trusted = N'false',
@thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

/****** End: Script to be run at Distributor ******/
use master
GO
Other -----------------
- Processing and Storing Data in SQL Server 2005 : Data Migration from One Data Store to Another Data Store
- Processing and Storing Data in SQL Server 2005 : Implementing the Record Failure Code
- Processing and Storing Data in SQL Server 2005 : Data Tracking Validation
- Processing and Storing Data in SQL Server 2005 : Updating the FileWorker Class
- Microsoft SQL Server 2008 R2 : Setting Up Replication (part 4) - Creating Subscriptions
- Microsoft SQL Server 2008 R2 : Setting Up Replication (part 3) - Horizontal and Vertical Filtering
- Microsoft SQL Server 2008 R2 : Setting Up Replication (part 2) - Creating a Publication
- Microsoft SQL Server 2008 R2 : Setting Up Replication (part 1) - Creating a Distributor and Enabling Publishing
- SQL Server 2008 R2 : Basing the Replication Design on User Requirements
- SQL Server 2008 R2 : Planning for SQL Server Data Replication & SQL Server Replication Types
- SQL Server 2008 R2 : Replication Agents
- SQL Server 2008 : Replication - Subscriptions
- SQL Server 2008 : Replication Scenarios
- Protecting SQL Server Data : CELL-LEVEL ENCRYPTION - Special Considerations
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Harnessing Linked Servers
- Monitoring SQL Server 2005 Performance : Using Windows System Monitor & Using SQL Server Profiler
- Monitoring SQL Server 2005 Performance : Monitoring and Recording Performance
- SQL Server 2008 R2 : Replication - The Publisher, Distributor, and Subscriber Magazine Metaphor
- SQL Server 2008 R2 : Replication - What Is Replication?
- SQL Server 2008 High Availability : Other HA Techniques That Yield Great Results & High Availability from the Windows Server Family Side
 
 
Most View
- CSS for Mobile Browsers : Common Patterns (part 4)
- SharePoint 2010 : Use Wiki Syntax to Link to Existing Content and Create Pages
- BizTalk Server 2009 : The core principles of a service-oriented architecture (part 4)
- SharePoint 2007 : Create an Alert on a File or List Item
- Installing Exchange Server 2003 in a Clustered Environment
- BizTalk Server 2009 : Consuming WCF services from orchestrations
- First Stages of SEO : Benchmarking Current Rankings
- Programming Windows Phone 7: An Introduction to Touch - The Manipulation Events
- Programming Windows Phone 7 : Color Themes
- Parallel Programming with Microsoft .Net : Parallel Tasks - Design Notes
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS