SQL Server Performance

Function Replication? - has anyone heard of this?

Discussion in 'SQL Server 2005 Replication' started by sql_jr, Jan 25, 2008.

  1. sql_jr New Member

    Gurus out there, please vet and review the below methodology. The client wants to set up its own custom type replication to call remote sps/functions through a firewall to the "publisher", and all transactions will be pushed at the publisher, whether its internal clients directly at the publisher, or external via a firewall that calls the replication to fire ALWAYS at the publisher. They don't want two-way replication set up. I believe such method is used in SYBASE, but can this apply to MS SQL.The function replication technique In MS SQL Server environment, in particular, all setup and configuration (and replication related ones are no exception) are typically done thru specifically dedicated GUI Wizards. Of course, the same can be accomplished via a command-line interface, however, Wizards create/perform all low-level command functionality behind the scenes and the implementer only needs to stay on a conceptual level of “what needs to be implemented” without the need to know “how it is actually done and by which replication commands”. Thus, it will be sufficient to perform the steps below at the Wizard level to accomplish the desired configuration and functionality. On a Wizard level:
    1. On Internal Database Server (Publisher): create publications & define articles
    2. On External Database Server (Subscriber): create updatable subscriptions & choose update model as depicted below:

    Updatable SubscriptionRequirements
    Immediate UpdatingPublisher and Subscriber must be connected to update data at the Subscriber.
    Queued UpdatingPublisher and Subscriber do not have to be connected to update data at the Subscriber. Updates can be made while offline.
    Immediate Updating with Queued Updating as a FailoverPublisher and Subscriber are connected most of the time, but you may occasionally need to make updates offline. Internally, the function replication setup steps are:
    § Create a function-string class.
    create function string class function_class [set parent to parent_class]Example: Create a derived function-string class named sqlserver_derived_class that will inherit function strings from the system-provided class rs_default_function_class:
    create function string class
    sqlserver_derived_class set parent to rs_default_function_class§ Add a function string to a function-string class. Replication Server uses function strings to generate instructions for data servers.
    create function string
    for function_class [with overwrite]
    [scan 'input_template']
    [output {language 'lang_output_template' |
    rpc 'execute procedure
    [@param_name=]{constant |?variable!mod?} ...' |
    writetext [use primary log | with log | no log] | none}]Example: create a user-defined function update_some_tbl and a corresponding function string for the sqlserver_derived_class. The function string executes a stored procedure named update_some_tbl:
    create function string titles_rep.update_some_tbl
    for sqlserver_derived_class
    output rpc
    'execute update_some_tbl
    @col1 = ?col1!param?,
    @col2 = ?col2!param?,
    @col3 = ?col3!param?'§ Create a function replication definition and user-defined function for a stored procedure that is to be replicated.
    create function replication definition
    with primary at data_server.database
    [deliver as 'proc_name']
    ([@param_name datatype [, @param_name datatype]...])
    [searchable parameters (@param_name [, @param_name]...)]
    [send standby {all | replication definition} parameters]Example: Create a function replication definition named some_tbl_upd for a function and stored procedure of the same name. The stored procedure to be invoked in the destination database is named update_some_tbl. Use a function replication definition like this for a request function:
    create function replication definition some_tbl_upd
    with primary at LDS.some_db
    deliver as 'update_some_tbl'
    (@col1 datatype1, @col2 datatype2, @col3 datatype3)
    searchable parameters (@col1)§ Create and initialize a subscription that materializes subscription data. The subscription may be for a database replication definition, table replication definition, function replication definition, or publication.
    create subscription sub_name
    for { table_repdef | func_repdef | { publication pub |
    database replication definition db_repdef }
    with primary at server_name.db }
    with replicate at data_server.database
    [where {column_name | @param_name} {< | > | >= | <= | = | &} value
    [and {column_name | @param_name} {< | > | >= | <= | = | &} value]...]
    [without holdlock | incrementally | without materialization]
    [subscribe to truncate table][for new articles]Example: Create a subscription named some_tbl_upd_sub for the function replication definition some_tbl_upd. To use this command to create a subscription for a function replication definition, data must already exist at the replicate database, and must use the without materialization clause:
    create subscription some_tbl_upd_sub
    for some_tbl_upd
    with replicate at LDS.some_db
    without materialization
  2. sql_jr New Member

  3. satya Moderator

    Its hard to comment whether it will set your requirement fit or not unless it is testing, as its a specific process where you have huge involvement of firewalls and opening ports for SQL server, by default the SQL replication uses with 1433 that is listen-on with SQL server services and I don't see any additonal requirement to open up.

Share This Page