Generating Merge Dynamic Snapshot Jobs
This sample generates multiple merge dynamic snapshot jobs for a set of Windows Group users. After you create a merge publication with a dynamic filter and generate a standard snapshot, you can run the extended stored procedure and stored procedures included with this sample to generate dynamic snapshot jobs.
The sample extended stored procedure enumerates a Windows group and identifies the members for which dynamic snapshot jobs will be created. After the members are identified, you can execute the sample stored procedure that will create the actual dynamic snapshot jobs. After the dynamic snapshot jobs are created, you will need to start them manually or ensure they will start according to a schedule.
If you have a dynamically filtered merge publication with multiple subscriptions, this sample shows how you can create multiple dynamic snapshot jobs for the users of those subscriptions. Dynamic snapshots provide the performance advantage of using SQL bulk copy program (bcp) files to apply data to a specific Subscriber when applying the initial snapshot. For more information, see Dynamic Snapshots.
The sample code is located in the \Microsoft SQL Server\80\Tools\Devtools\Samples\sqlrepl\dynsnapjob directory.
To run the sample programs
- In the \Microsoft SQL Server\80\Tools\Devtools\Samples\sqlrepl\dynsnapjob\xp directory, copy the compiled xp_enumntusers.dll into your Windows system32 folder.
- Register the name of the extended stored procedure to Microsoft SQL Server™ by running the following Transact-SQL command in SQL Query Analyzer:
use master go exec sp_addextendedproc 'xp_enumntusers', 'xp_enumntusers.dll' go - To create the stored procedure, open SQL Query Analyzer, and then run spdynsnapsample.sql, which is located in the \Samples\sqlrepl\dynsnapjob\sp directory.
- Create a merge publication with dynamic filtering enabled (use a system function such as SUSER_SNAME() as the criteria for the filter), and then generate a standard snapshot for the publication.
- Allow a Windows Local (or Global) group access to the SQL Server instance that contains the merge publication in Step 4. This group needs to have public access only to the database on which you created the publication. This step can be performed using SQL Server Enterprise Manager.
- Add the Windows Group to the Publication Access List (PAL), which is found in the merge publication properties.
You can now run the extended stored procedure and stored procedure to generate dynamic snapshot jobs for the Windows Group users. For more information about how to execute each procedure, see Using xp_enumntusers and Using sp_addntgroupmergedynsnapshotjob.
Using xp_enumntusers
This extended stored procedure enumerates through Microsoft® Windows® Local and Global group users.
xp_enumntusers [ @servername = ] 'domain_server_name'
, [ @groupname = ] 'group_name'
Arguments
[@servername = ] 'domain_server_name'
The domain server to retrieve the group from. Use '.' or '' for local server.
[@groupname = ] 'group_name'
The name of the group from which to enumerate users.
Both @servername and @groupname must be server strings.
Using sp_addntgroupmergedynsnapshotjob
This stored procedure generates a dynamic snapshot job for each user found when xp_enumntusers was run. The stored procedure accepts parameters that allow you to specify the following:
- Generate a single dynamic snapshot job for all users or generate a separate dynamic snapshot job for each user.
- Filter the enumerated user using the LIKE operand.
- Specify schedule information.
sp_addntgroupmergedynsnapshotjob [ @publication = ] 'publication'
, [ @ntserver_name = ] 'ntserver_name'
, [ @group_name = ] 'group_name'
, [ @destination = ] 'destination'
, [ @job_name = ] 'job_name'
, [ @like_string = ] 'like_string'
, [ @as_one_job = ] as_one_job
, [ @frequency_type = ] frequency_type
, [ @frequency_interval = ] frequency_interval
, [ @frequency_subday = ] frequency_subday
, [ @frequency_subday_interval = ] frequency_subday_interval
, [ @frequency_relative_interval = ] frequency_relative_interval
, [ @frequency_recurrence_factor = ] frequency_recurrence_factor
, [ @active_start_date = ] active_start_date
, [ @active_end_date = ] active_end_date
, [ @active_start_time_of_day = ] active_start_time_of_day
, [ @active_end_time_of_day = ] active_end_time_of_day
Arguments
[@publication = ] 'publication'
The name of the publication. publication is sysname, with no default.
[@ntserver_name = ] 'ntserver_name'
The Windows server or domain name on which to enumerate users. Use '.' for local server. ntserver_name is nvarchar(100) with no default.
[@group_name = ] 'group_name'
The name of the group on the Windows server or domain. group_name is nvarchar(256) with no default.
[@destination = ] 'destination'
The destination directory path. destination is nvarchar(3500) with no default.
[@job_name = ] 'job_name'
The job name. job_name is sysname with a default of NULL. If NULL, a default job name is used. This parameter is ignored when @as_one_job=0
[@like_string = ] 'like_string'
The string to be used in WHERE clause used when creating jobs for user names. For example, the WHERE clause is constructed of "WHERE user name LIKE @like_string". like_string can contain any of the valid wildcard characters such as the percent sign ('%'). like_string is nvarchar(1000) with a default of NULL.
[@as_one_job = ] as_one_job
Specifies how many dynamic snapshot jobs to create. as_one_job is a bit with a default of 1. 1 specifies that one dynamic snapshot job will be created for all users. 0 specifies that an individual dynamic snapshot job will be created for each user.
[@frequency_type = ] frequency_type
@frequency_type int = 4,
Is a value indicating when the job is to be executed. freq_type is int with a default of 4, and can be one of these values.
| Value | Description |
|---|---|
| 1 | Once |
| 4 | Daily |
| 8 | Weekly |
| 16 | Monthly |
| 32 | Monthly, relative to frequency_interval |
| 64 | Run when SQL Server Agent service starts |
| 128 | Run when computer is idle |
[@frequency_interval = ] frequency_interval
Is the days that the job is executed. freq_interval is int with a default of 1, and the value used is dependent on the value of freq_type.
| Value of frequency_type | Effect on frequency_interval |
|---|---|
| 1 (Once) | frequency_interval is ignored. |
| 4 (Daily) | Every frequency_internval days. |
| 8 (Weekly) | frequency_interval is one or more of the following (combined with an OR logical operator):
1 = Sunday |
| 16 (Monthly) | On the frequency_interval day of the month. |
| 32 (Monthly relative) | frequency_interval is one of the following:
1 = Sunday |
| 64 (When SQL Server Agent service starts) | frequency_interval is unused. |
| 128 (When computer is idle) | frequency_interval is unused. |
[@frequency_subday = ] frequency_subday
Specifies the units for frequency_subday_interval. frequency_subday is int with a default of 8, and can be one of these values.
| Value | Description of Units |
|---|---|
| 0x1 | At the specified time. |
| 0x4 | Minutes. |
| 0x8 | Hours. |
[@frequency_subday_interval = ] frequency_subday_interval
Is the number of frequency_subday_type periods to occur between each execution of the job. frequency_subday_interval is int, with a default of 6.
[@frequency_relative_interval = ] frequency_relative_interval
Is the scheduled job's occurrence of frequency_interval in each month, if frequency_interval is 32 (monthly relative). frequency_relative_interval is int with a default of 1, and can be one of these values.
| Value | Description of Units |
|---|---|
| 1 | First |
| 2 | Second |
| 4 | Third |
| 8 | Fourth |
| 16 | Last |
[@frequency_recurrence_factor = ] frequency_recurrence_factor
Is the number of weeks or months between the scheduled execution of the job. frequency_recurrence_factor is used only if frequency_type is 8, 16, or 32. frequency_recurrence_factor is int with a default of 0.
[@active_start_date = ] active_start_date
Is the date on which execution of the job can begin. active_start_date is int with a default of 0, which indicates today's date. The date is formatted as YYYYMMDD. If active_start_date is not NULL, the date must be greater than or equal to 19900101.
[@active_end_date = ] active_end_date
Is the date on which execution of the job can stop. active_end_date is int with a default of 99991231, which indicates December 31, 9999. Formatted as YYYYMMDD.
[@active_start_time_of_day = ] active_start_time_of_day
Is the time on any day between active_start_date and active_end_date to begin execution of the job. active_start_time is int, with a default of 0, which indicates 12:00:00 A.M. on a 24-hour clock. The value for this parameter must be entered using the form HHMMSS.
[@active_end_time_of_day = ] active_end_time_of_day
Is the time on any day between active_start_date and active_end_date to end execution of the job. active_end_time is int with a default of 235959, which indicates 11:59:59 P.M. on a 24-hour clock. The value for this parameter must be entered using the form HHMMSS.