{Li C Zhao}

In the past few months, I’ve been working more closely with Database because the departure of our DBA. I have been doing deployments that are involved in adding and altering schema changes. Whenever a new table is added to SQL replication you need to generate replication store procedures at the publisher and the take the result and put it at the subscribers. In the past, I would need to run ’sp_scriptpublicationcustomprocs ‘, which generate store procedures for every published tables, and I would search for store procedures for the new table. Next, I would need to find the article id for the new table and run ’sp_scriptdynamicupdproc ‘, which will generate smarter update store procedure.

I’ve create a store procedure that will only generate store procedures that for specified table name. If no table name is supplied, it will generate store procedures for every published articles.

-- Author: Li C Zhao
-- Version: 1.0
-- Date: 2008-02-29
-- Usage: exec sp_GenerateReplProcedures [@table = 'tablename']
-- Description: Generate store procedures for replication for the
--    subscribers. If no parameter is pass, it will generate all
--    published store procedures. If table name is passed as the
--    parameter, it will generate store procedures (INSERT, UPDATE,
--    and DELETE) for the specified article (if found).
--
CREATE PROCEDURE [sp_GenerateReplProcedures]
    @table VARCHAR(255) = NULL
AS

SET NOCOUNT ON
CREATE TABLE #TmpSpText ( spText NVARCHAR(2048) )

IF @table IS NOT NULL
    DECLARE test CURSOR FOR SELECT artid FROM [sysarticles]
        WHERE dest_table = @table
ELSE
    DECLARE test CURSOR FOR SELECT artid FROM [sysarticles]
OPEN test

DECLARE @id INT
FETCH NEXT FROM test INTO @id

WHILE @@fetch_status<>-1
BEGIN
    -- Generating Insert Store Procedure
    INSERT INTO #TmpSpText (spText)
        EXEC sp_scriptinsproc @id

    SELECT spText AS '---- INSERT STORE PROCEDURE' FROM [#TmpSpText]
    PRINT 'GO'
    DELETE FROM #TmpSpText

    -- Generating Delete Store Procedure
    INSERT INTO #TmpSpText (spText)
        EXEC sp_scriptdelproc @id
    SELECT spText AS '---- DELETE STORE PROCEDURE' FROM [#TmpSpText]
    PRINT 'GO'
    DELETE FROM #TmpSpText

    -- Generating Dynamic Update Procedure
    INSERT INTO #TmpSpText (spText)
        EXEC sp_scriptdynamicupdproc  @id

    SELECT spText AS '---- UPDATE STORE PROCEDURE' FROM #TmpSpText
    PRINT 'GO'
    DELETE FROM #TmpSpText

FETCH NEXT FROM test INTO @id
END
CLOSE test
DEALLOCATE test
DROP TABLE #TmpSpText

GO

Links:
sp_scriptdynamicupdproc
sp_scriptpublicationcustomprocs

Introduction:

If your company is like mine, you will have multiple VPN profiles on your Cisco VPN client. The issue is that you are only allow to connect one VPN at a time. If you need to get to resources on multiple locations you will need to keep disconnecting and reconnecting between different VPN tunnels. Thanks to my co-worker Alex, who show me that it’s possible to connect to multiple with vpnc on Linux, I got it to work on windows. This has been tested on Windows XP SP2 only.

Requirements:

Cygwin
OpenVPN
vpnc
Steps:

1. Download Cygwin setup and run the setup file. Set up cygwin and select the following packages during installation
   • Devel -> gcc
   • Devel -> make
   • Libs -> libgcrypt (and the source code as well)
   • Libs -> libgpg (and the source code as well)
   • Perl -> perl
2. Download OpenVPN and install it. You can uncheck most of the options if you like, make sure “TAP-Win32 Virtual Ethernet Adapter” and “Add Shortcuts to Start Menu” (so you can add more virtual interface from start menu) are selected.
3. Download vpnc and copy it to c:\cygwin\tmp

   Launch cygwin bash shell and run the following commands in the shell

cd /tmp

tar xvfz vpnc-<version>.tar.gz

Note: <version> is the version that you have downloaded. As of writing the version is 0.5.1. Hence, the command would be “tar xvfz vpnc-0.5.1.tar.gz”

cd vpnc-<version>

Example: cd vpnc-0.5.1

make

make PREFIX=/usr install

Note: PREFIX is optional. By default it will install the executables to /usr/local. By putting PREFIX=/usr it will install the executables to /usr/bin an /usr/sbin.

mkdir /var/run/vpnc

Multiple Tunnels:

For each VPN tunnel you will need 1 TAP virtual adapter. If you are planning to use multiple tunnels at the same time, you will need to OpenVPN and run “Add a new TAP-Win32 virtual ethernet adapter”.

Note:

There’s a bug in “vpnc-script-win.js” that came with the 0.5.1. You will need to rename the TAP virtual Adapter from “Local Area Connection X” to TAPX. The included js file doesn’t support space in the virtual adapter. The file doesn’t add route correctly as well. To fix this issue, you will need to use the “vpnc-script-win.js” that was modified to get it to work. Copy the “vpnc-script-win.js” file and replace the existing one located in c:\cygwin\etc\vpnc (or /etc/vpnc if you are in cygwin shell).

Setting up Profiles:

You can use pcf2vpnc command to convert the existing cisco PCF to out what needs to create profiles for VPNC. The profiles need to be stored at c:\cygwin\etc\vpnc (in <profile>.conf format). The installation comes with a default.conf that you can refrence to. Alternatively, you can run /usr/sbin/vpnc –long-help to get more help and what you can put in the file. You can also connect by passing all the switches via the command line. Below is an example that will work

IPSec gateway <IPSEC GATEWAY>

IPSec ID <Group ID>

IPSec secret <Group Password>

IKE Authmode psk

Xauth username <Your Username>

Interface mode tap

Pidfile /var/run/vpnc/<profile>.pid

Local Port <510>

NAT Traversal Mode force-natt

No Detach

## Enable Single DES

When you run pcf2vpnc it will give you IPSec gateway, IPSec ID, IPSec secret (decrypted), Xauth username, IKE Authmode.

Important:

The “No Detach” option is required, as if you don’t specify it the VPN will connect, but you will not be able to access the VPN even thought it’s connected. “Local Port” needs to be different for each profile – so that there’s collision. Each profile needs to have its own pid file.

Depends on your network administrator’s setting you might need to enable single des. If your network administrator enables weak encryption, you will need to use “Enable Single

At my work, we have MSSQL with replication. The problem is we have 5 SQL subscribers. Our R&D department keep coming out with Store Procedures. In order to deploy these new store procedures to production, we need to run the script on every servers. This is not only time consuming, but bounded to human error.

In SQL, you can replicate store procedures with ’sp_addarticle’ and ’sp_addsubscription’. However, they don’t seem to work in our environment.

exec sp_addarticle  @publication =   '<publicationName>'
   ,  @article =  '<nameofthesp>'
   ,  @source_object =  '<nameofthesp>'
   ,  @type = 'proc exec'

exec sp_addsubscription  @publication =  '<publicationName>'
   ,  @article =  '<nameOfTheSP>'
   ,  @subscriber =  '<nameOfTheSubscriber>'
   ,  @sync_type = '(automatic|none)'

I came across this built in store procedure and it’s doing what we needed to do. Save the SQL script to some where accessible over the network and use ’sp_addscriptexec’ to run the script on all the subscribers. You will still need to run the script on the publisher.

WARNING: Be careful on the script you are running. Any data manipulation in the script will cause your replication to be out of sync. This should be only used for adding and altering store procedures.

exec sp_addscriptexec @publication = '<publicationName>'
   , @scriptfile = '\\<publicationServerNameOrIP>\d$\<SQLScript>.sql'

Tags: Database, Replication, SQL