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
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8