Sybase DC38133-01-0902-01 specification

Introduction

This chapter presents a tutorial to lead you through setting up a SQL Remote installation. The installation replicates data between an Adaptive Server Enterprise database (the consolidated database) and an Adaptive Server Anywhere database (the remote database).

Goals

In the tutorial you act as the system administrator of a consolidated Adaptive Server Enterprise database, and set up a simple replication system. The replication system consists of a simple sales database, with two tables.

The consolidated database holds all of the database, while the remote database has all of one table, but only some of the rows in the other table.

The tutorial takes you through the following steps:

♦ Creating a consolidated database on your Adaptive Server Enterprise server.

♦ Creating a ﬁle-sharing replication system with a single Adaptive Server Anywhere remote database.

♦ Replicating data between the two databases.

The database

	The tutorial uses a simple two-table database. One table holds information
	about sales representatives, and the other about customers. The tables are
	much simpler than you would use in a real database; this allows us to focus
	just on those issues important for replication.
Database schema	The database schema for the tutorial is illustrated in the ﬁgure.

Customer

cust_key char(10)

name char(40)

rep_key char(5)

rep_key =

rep_key

SalesRep

rep_key char(5)

name char(40)

Features to note include the following:

♦Each sales representative is represented by one row in the SalesRep table.

♦Each customer is represented by one row in the customer table.

♦Each customer is assigned to a single Sales representative, and this assignment is built in to the database as a foreign key from the Customer

54

Image 72

Sybase DC38133-01-0902-01 manual Database schema for the tutorial is illustrated in the ﬁgure

Contents

SQL Remote User’s Guide Part number DC38133-01-0902-01 Last modiﬁed OctoberPage Contents SQL Remote Administration 199 Principles of SQL Remote DesignSQL Remote Design for Adaptive Server Anywhere SQL Remote Design for Adaptive Server Enterprise 141 Administering SQL Remote for Adaptive Server Anywhere241 Using SQL Remote with Replication Server 277System Objects for Adaptive Server Anywhere 325 Utilities and Options Reference 291 Command Reference for Adaptive Server Anywhere 353 Command Reference for Adaptive Server Enterprise 379 Server Anywhere Differences 437 SQL Remote for Adaptive Server Enterprise and AdaptiveSupported Platforms and Message Links 445 Index 449 Viii About This Manual Mail or ﬁle transferSystems SQL Anywhere Studio documentation Adaptive Server Anywhere Database Administration Guide ThisPage Page Documentation conventions Release Savepoint savepoint-name Following icons are used in this documentation Client applicationDatabase server, such as Sybase Adaptive Server Anywhere Xiv Adaptive Server Anywhere sample database Asademo.db Finding out more and providing feedback Page Xviii Introduction to SQL Remote Page Welcome to SQL Remote This chapter introduces SQL Remote and the documentation SQL Remote for Adaptive Server Anywhere Enables replication About SQL Remote Product installation To install the SQL Remote software WindowsTo install the SQL Remote software Unix About this manualPage SQL Remote Concepts SQL Remote SQL Remote components Data server Client applicationsMessage Agent Message system client Publications and subscriptions Two-table synchronization definition Replication At the remote database SQL Remote features Page Some sample installations Server-to-laptop replication for mobile workforces Server-to-server replication among ofﬁces More Page Setting Up SQL Remote Setup overview Each user in the installationSybase System Administrator should perform all SQL Remote You must take some steps to Installing the SQL Remote system objects To install the SQL Remote system objectsPreparing your Adaptive Server Enterprise server Ensuring Tempdb is large enough SQL Remote system objects Command-line installation of the stable queue To install the stable queuePage To upgrade SQL Remote Upgrading SQL Remote for Adaptive Server Enterprise Uninstalling SQL Remote To uninstall the SQL Remote objects from a databaseTo uninstall the stable queue from a database Tutorials for Adaptive Server Anywhere Users Just on those issues important for replication IntroductionGoals Database Tutorials for Adaptive Server Anywhere Users Tables are described in more detail as follows Replication goals RepkeyName Custkey Sybase Central or command-line utilities Preparing for the Sybase Central replication tutorial To prepare for the tutorial To add tables to the consolidated database Setting up a consolidated database Add a SQL Remote message type To add an address to a message type Add the publisher and remote user to the database To create a new user as the publisher To add a remote user Add publications and subscriptionsTo add a publication Set up the remote database in Sybase Central Enter the expression repkeyClick Finish to create the publication Publication for the remote database user ﬁelduser To extract a database Preparing for the replication tutorial To create the databases and directories for the tutorial Set up the consolidated database To add the tables to the consolidated databaseCreate a SQL Remote message type To create the message type Grant Publish and Remote at the consolidated databaseTo create a publisher for the database Create publications and subscriptions SQL, to create a publicationTable SalesRep, and some of the rows of the table Customer Set up the remote database To create the publicationTo create the subscription Extract the remote database information Load the remote database informationTo load the database information Select * from SalesRep Select * from Customer Start replicating data Enter data at the consolidated databaseSend data from the consolidated database First, enter some data into the consolidated database Receive data at the remote database To send the data to the remote databaseTo receive data at the remote database To verify that the data has arrived Commit the insertion by executing the following statement Custkey Name Repkey Sample publication To add the publication to the sample databasePage Tutorial for Adaptive Server Enterprise Users Database schema for the tutorial is illustrated in the ﬁgure Tutorial for Adaptive Server Enterprise Users SalesRep table is many-to-onePage Tutorial Adaptive Server Enterprise replication First steps To install SQL Remote into the hq database Setting up the consolidated database Create the SalesRep table with the following statementCreate the Customer table with the following statement Create the message links and addresses Create the necessary users and permissions To create the publisher To create the subscriber Create the publication and subscription Create an empty publication To create a subscription Create a subscription to SalesRepData for ﬁelduser , with aSubscription value of rep1 Extract the remote database To load the data into the database using Interactive SQL Utility onTo create a database ﬁle named ﬁeld.db Connect to the server using the Interactive SQL utility To load the data into the database as a batch processRun the script from Interactive SQL To enter data at the Adaptive Server Enterprise database Connect to the Adaptive Server Enterprise server from isql To replicate the data from Adaptive Server Enterprise To receive the data at Adaptive Server Anywhere Commit the row Custkey Name Repkey Page Replication Design for SQL Remote Page Principles of SQL Remote Design Adaptive Server Enterprise System Administrator or database Design overviewEnsuring compatible databases SQL Remote replicates data Using compatible sort orders and character sets SQL Remote Message Agent does not perform any character setConversions Between Ssremote and Adaptive Server Enterprise whenever 819LATIN1 Eucjapan Replication of updates How statements are replicatedReplication of inserts and deletes Clause of the Update statement User, the row to be updated is not foundStatement Representatives Replication of procedures This case, the Update does not update anythingProcedure call is not replicated Replication of triggers Message causing the conﬂict Body of your triggersActions carried out by conﬂict Some Before triggers can produce Replication of data deﬁnition statements How data types are replicated Replication of blobs Replication of dates and times Select Who gets what? Principles of SQL Remote Design Replication errors and conﬂicts Replication errorsReplication conﬂicts Tracking SQL errors Issue the following statementVARCHAR, or Long VARCHAR. The procedure is called once with DetectedPage SQL Remote Design for Adaptive Server Anywhere Publications determine what information Is shared among which databasesSubscriptions determine what Information each user receives Publishing data Publishing whole tablesTo publish one or more entire tables Sybase Central To publish one or more entire tables SQL Publishing only some columns in a table For more information, see the Create Publication statementTo publish only some columns in a table Sybase Central Publishing only some rows in a table To publish only some columns in a table SQL Publishing only some rows using a Where clause To create a publication using a Where clause Sybase CentralTo create a publication using a Where clause SQL Publishing only some rows using a subscription expression To create an article using a subscription expression SQL Column Altering existing publications To add articles Sybase Central Dropping publications To remove articles Sybase CentralTo modify an existing publication SQL To delete a publication Sybase Central To delete a publication SQLSee also the Drop Publication statement ASA SQL Reference, Design issues overview Publication design for Adaptive Server Anywhere Conditions for valid articles Design tips for performance Pay attention to Update Publication Triggers In particular Here is a simple database that illustrates the problem Contact exampleThree tables are described in more detail as follows Following information Those customers assigned to them, from the Customer table106 Partitioning the Customer table in the Contact example Partitioning the Contact table in the Contact exampleTerritory realignment in the Contact example Reps Properly transferred to the new sales representativeAlong with the Customer For which there is no longer a Customer Special Update statement for publications Understanding this helps in designing efﬁcient publications Assume the following data∙ SalesRep table ∙ Customer table SalesRepData Publication Name rep1 Before list Sharing rows among several subscriptions Policy exampleMany-to-many relationship in the database With the following information Entire SalesRep tableInvolving the sales rep subscribed to the data 113 Publication Territory realignment with a many-to-many relationship Contact example on Remote databases contain the proper data 116 Customer being transferred Where custkey = OldRow.custkey That this subquery can be multi-valuedCan be multi-valued Dealing with the customer, taken from the Policy table Customer SQL Remote Design for Adaptive Server Anywhere Managing conﬂicts ID Rep Dept Ann Marc Must be excluded from the installation by proper design SQL Remote installation at the consolidated databaseHow SQL Remote handles conﬂicts Updating Implementing conﬂict resolution Using conﬂict resolution triggers Create Trigger trigger-name Referencing OLD AS oldval Conﬂict resolution examples Resolving date conﬂicts Resolving inventory conﬂicts Increments from the two updates. For example Reporting conﬂicts Designing to avoid referential integrity errors Designing triggers to avoid errorsIncludes the salesorder table Page Using global autoincrement default column values Ensuring unique primary keys Declaring default global autoincrement Setting the Globaldatabaseid value Primary key values for Global Default Autoincrement values To set the global database identiﬁcation numberDatabase will have Globaldatabaseid set to How default values are chosen Using primary key pools Primary key pool table Replicating the primary key poolTo replicate the primary key pool SQL Filling and replenishing the key pool 136 Cursor for Adding new customers Else Primary key pool summary Insert Creating subscriptions To create and manage subscriptions in Sybase Central For more information, see Create Subscription statement on 140 SQL Remote Design for Adaptive Server Enterprise 141 Design overview Creating publications Creating whole-table articlesCreating articles containing some of the columns in a table Creating articles containing some of the rows in a table Creating an article using a Where clause To create an article using a Where clauseCreating an article using a subscription column To create an article using a subscription column Spaddremotetable tablenameSpaddarticle publicationname, tablename NULL, columnname Publication design for Adaptive Server Enterprise Insert Succeeds To handle this case, using an example Contain the subscription columnTwo tables described earlier in this chapter 149 150 Are realigned Adding a subscription-list column to the Contact table Exec spcreatepublication ’SalesRepData’ go Log entries are values, not subscribers Update on the Contact table Update on the Customer tableMaintaining the subscription-list column For the following operations Tuning extraction performance SQL Remote Design for Adaptive Server Enterprise To create a subscription view Create a view that contains this subquery. For example156 Solving the problem 158 Foreign KEY Subscriptions to this publication take the following form These objects159 Triggers 161 For Insert AS Begin Tuning extraction performance for shared rows Ensure the quotedidentiﬁer option is set to on 163 Customer Managing conﬂicts Consolidated database, no row will be updated Any conﬂict resolution procedures are calledDatabase objects to handle the resolution When the conﬂicting message arrived Names of the conﬂict resolution objects Remoterowtable. These three are discussed in turnTable only ever contains a single row This table must have the same column names ﬁrst conﬂict resolution example Two-table database is as follows168 Conﬂict-causing Update After the procedure is run, the rows in the OldCustomerFollows @lostname value is the value that was overridden by Second conﬂict resolution example To test the exampleConﬂictLog table has a single row, showing the conﬂict It when the procedure is executed Lost and won namesUser ID of the remote user. The table is as follows Replaces it with the value that was previously present Where conﬂict resolution is carried out by Before triggers There are several points of note hereUser ID of the remote user is stored by the Message Agent Currentremoteuser column of the temporary table #remote 173 Page Primary key pool Columns of this table have the following meanings 175 To replicate the primary key pool 176 177 Adding new customers Testing the key pool To test the primary key pool179 Primary key pool summary Spsubscription create, puborders SamS ’856’ Page SQL Remote Page SQL Remote replication installation Deploying and Synchronizing Databases185 Deployment overview Resolve update conﬂicts are all easy to do Test before deploymentChanges to avoid on a running system Conﬂicts as they occurPage Synchronizing databases Mixed operating systems and database extraction Example Using the extraction utility Where path is the path of the reload command ﬁle 191Creating a database from the reload ﬁles To create a remote database from the reload ﬁle Using the extraction utility from Sybase Central Before extracting a database Designing an efﬁcient extraction procedure To extract a database for a remote user Sybase Central An efﬁcient approach to extracting many databases Limits to using the extraction utility Extracting groups Using the extraction utility for Adaptive Server Enterprise This setup is illustrated in the following diagram Customizing the system tables Synchronizing data over a message system SQL Remote Administration 199 Management overview Managing SQL Remote permissions Granting and revoking Publish permissions To create a new user as the publisher Sybase Central To make an existing user the publisher Sybase CentralTo revoke Publish permissions Sybase Central 202 Exec sppublisher ’SBeaulieu’ go Granting and revoking Remote and Consolidate permissions Granting Remote permissions To make an existing user remote Sybase Central Following optionsUse an Smtp e-mail system Send messages to e-mail address sbeaulieu@acme.com Selecting a send frequency Permissions to user hquser, using the ﬁle message link Granting Consolidate permissionsStatement is executed automatically at the remote database Agent is run Assigning permissions in multi-tier installations To revoke Remote permissions Sybase CentralPermission from user SBeaulieu Publish Using message types Working with message types Using Sybase Central to work with message types To add a message type Sybase CentralTo alter a message type Sybase Central Using commands to work with message types To drop a message type Sybase CentralTo create a message type SQL Create Remote Message Type type-nameADDRESS address-string To alter a message type SQL To drop a message type SQLCreate Remote Message Type statement on 213 Setting message type control parameters To set a message control parameter Adaptive Server Anywhere By using the ﬁle message system ﬁle message system215 Ftp message system Troubleshooting ftp problems Smtp message system Mote messages218 SQL Remote Administration Mapi message system Sharing SMTP/POP addressesSupported operating systems on VIM message system VIM message control parameters Running the Message Agent Message Agent batch and continuous modesNames are ssremote and dbremote, respectively Enterprise works on Replication system recovery procedures To run the Message Agent in continuous modeTo run the Message Agent in batch mode Connections used by the Message Agent Ensuring consistent Message Agent settings See the sections Transaction log and backup management onAdaptive Server Enterprise transaction log and backup Management on Troubleshooting errors at remote sites Message Agent and replication security Execute the following command against the remote database To conﬁgure a consolidated site to receive log informationUse either the -roor the -rtMessage Agent option For more information, see The Message Agent on Tuning Message Agent performance Tuning throughput by controlling Message Agent threading Tuning throughput by caching messages Tuning incoming message polling Polling interval Requesting resends Example231 Tuning the message sending process To those when tuning the incoming-message polling frequency Your choices dictate how often updates are sent toWhen a remote user requests that a message be Resending messages Dbremote -ru 1h Encoding and compressing messages Encoding scheme Creating custom encoding schemes Message tracking system Status information in the remoteuser tableTracking messages by transaction log offsets Agent -loption to change this setting Are offsets in the local database transaction log,Cannot be a later offset than logsent Transaction log Handling of lost or corrupt messages Its resendcountPage Administering SQL Remote for Adaptive Server Anywhere 241 Running the Message Agent as a service Starting the Message Agent Full DBA permissions from the Message Agent When connecting Full DBA permissions on the databaseSuggested practice is to grant Remote DBA authority at DBA authority grants them no extra permissionsPage Error reporting and handling Default error handlingIgnoring errors Agent output Implementing error handling procedures Example e-mailing notiﬁcation of errorsFollowing stored procedure implements this notiﬁcation 246 Columns have the following meaning 247 Commit Work Here is a sample insert into the table from the above error 248 Transaction log and backup management Setting the transaction log directory Backup utility options Using the live directory as the transaction log directorySuch a directory is kept in proper shape Following command line Using the backup directory as the transaction log directory 252 Managing old transaction logs Recovery with a single transaction log To recover the database254 To recover from media failure on the C drive Backup the mirrored transaction log d\mirdir\consol.mlgStart the database using the following command line Recovery with multiple transaction logs Backups daily using the following command Command line would beBackup the mirrored transaction log d\mirdir\cons.mlg Make a copy of the database and log ﬁle Backup procedures at remote databases Apply the renamed transaction logs in order, as followsRestart your system as normal Upgrading consolidated databases 258 To unload and reload a consolidated database manual Using passthrough mode Uses and limitations of passthrough mode Operations not replicated in passthrough mode On a running SQL Remote setupProcedure on the replicate side has the correct effect However, the following dynamic SQL statement is replicated Administering SQL Remote for Adaptive Server Enterprise 263 How the Message Agent for Adaptive Server Enterprise works Scanning the transaction logMessage Agent on Administering SQL Remote for Adaptive Server Enterprise Stable queue Message Agent operation phases 266 TransactionStable Logqueue Message Agent 267 268 For setup instructions, see Setting Up SQL Remote on Running multiple Message Agents To execute Agent output Protecting against media failure on the transaction log Stable queue recovery issues Transaction log management With the following command Truncation point can be reset with the following commandFreed by Dump Transaction 274 Making schema changes Schema modiﬁcations Using SQL Remote with Replication Server 277 When you need to use the SQL Remote Open Server Architecture for Replication Server/SQL Remote installations How the pieces ﬁt together Message system Stable Queue Agent Information to retrieve it when it starts Remote Open Server onSQL Remote Open Server is the following executable On Windows operating systems, the SQL Remote Open Server is Setting up SQL Remote Open Server Same regardless of previous installationsAdd the SQL Remote Open Server to the setup This stage is To set up the SQL Remote Open Server Page Conﬁguring Replication Server Set the dsixactgroupsize parameterSet the dsinumthreads parameter Create replication deﬁnitions for SQL Remote data Suspend and restart the connection Other issues 288 This part presents reference material for SQL Remote Page Utilities and Options Reference 291 Message Agent Tracking system to ensure message deliveryDbremote ssremote options directory Utilities and Options Reference Listing, see Connections used by the Message Agent on Replication security on 295 Languagename,charsetname,sortorder For more information, see Running multiple Message Agents on Page Dbremote -rd 30s Page Registry, at the following location You should create a ﬁle named dbremote.iniWindows NetWare Database Extraction utility Extracting a remote database in Sybase Central Extraction utility 303 304 Remote Adaptive Server Anywhere database Extraction utility options Utilities and Options Reference Optional Database name. If this parameter is not sup 309 Page Input Server Enterprise and Adaptive Server Anywhere databases SQL Remote Open Server Default value for the open server name is SSQueue Gain access to the SQL Remote system tablesValues default to the -cvalues Name of the executable is as followsPage SQL Remote options Page Yy yyyy mm mmm dd 318 Hh nn mm Ss.s Relationships on SQL Remote event-hook procedures Sphookdbremotebegin and sphookssrmtbeginSphookdbremoteend and sphookssrmtend Sphookdbremoteshutdown and sphookssrmtshutdown Sphookdbremotereceivebegin and sphookssrmtreceivebeginSphookdbremotereceiveend and sphookssrmtreceiveend Sphookdbremotesendbegin and sphookssrmtsendbegin Sphookdbremotesendend and sphookssrmtsendendSphookdbremotemessagesent and sphookssrmtmessagesent Sphookdbremotemessagemissing and sphookssrmtmessagemissing Has applied a set of messages from a user 323Page System Objects for Adaptive Server Anywhere 325 SQL Remote system tables Sysarticle tableEach row describes an article in a SQL Remote publication Sysarticlecol table It is in, and the publication it is part327 Syspublication table Sysremoteoption tableSysremoteoptiontype table Each row describes a SQL Remote publication Sysremotetype table Sysremoteuser tablePublisher address 329 330 Consolidate Syssubscription table Remote permissions to one publication331 332 SQL Remote system views Sysarticles viewSysarticlecols view Syspublications view Sysremoteoptions view Sysremoteusers view Syssubscriptions view Each row lists information about a subscription335 336 System Objects for Adaptive Server Enterprise 337 #remote table Srarticle tableManage SQL Remote information Table has a single row Srarticlecol table Srmarker tableDatabases in the same session 339 Srobject table Sroption tableSrpassthrough table Each row describes a replication option used by SQL Remote Srpublication table Srpublisher tableSrremoteoption table Srremoteoptiontype table Srremotetable tableSrremotetype table 342 Srremoteuser table 343 344 Received Srsubscription table 345 Srarticles view Srarticlecols viewSrpublications view Srremoteoptions view Srremotetables viewSrremotetypes view Srremoteusers view 348 Srsubscriptions view 349 Stable Queue tables Srqueuestate tableStable queue 350 Srtransaction table 351 Srconﬁrmedtransaction table Srqueuecoordinate tableEach row marks the corresponding row in srtransaction 352 Command Reference for Adaptive Server Anywhere 353 354 Alter Remote Message Type statement Been createdMust have DBA authority Automatic commit Create Publication statement Create Publication statement ASA SQL Reference,Subscribe by expression Create Remote Message Type statement Messages from a databaseCreate Remote Message Type statement SQL Remote ASA SQL Create Remote Message Type message-system Create Subscription statement Create Subscription statement SQL Remote ASA SQL Reference Create Trigger statement Update statement on Must have Resource authority and have Alter permissions onTable and thus requires exclusive use of the table Create Trigger statement SQL Remote ASA SQL Reference Drop Publication statement Drop Publication statement ASA SQL Reference, Drop Remote Message Type statement Granted Remote or Consolidate permissions with this typeDrop Remote Message Type statement SQL Remote ASA SQL Drop Remote Message Type message-system Drop Subscription statement Drop Subscription statement SQL Remote ASA SQL Reference Grant Consolidate statement Current databaseGrant Consolidate statement SQL Remote ASA SQL Reference Send Every AT ’hhmm ss’ Grant Publish statement Grant Remote statement Identified by password Grant Remote DBA statementConnected from the Message Agent Grant Remote DBA statement SQL Remote ASA SQL Reference Passthrough mode Sppassthrough procedure onPassthrough statement Passthrough statement SQL Remote ASA SQL Reference, Remote Reset statement Remote Reset statement SQL Remote ASA SQL Reference,Subscriptions for a remote user in a single transaction No automatic commit is done by this statement Sprevokeconsolidate procedure on Revoke Consolidate statementSQL Remote messages from this database Automatic commit. Drops all subscriptions for the user Revoke Publish statement Current publisherRevoke Publish statement SQL Remote ASA SQL Reference Revoke Publish from userid Sprevokeremote procedure on Revoke Remote statementMessages from this database Revoke Remote statement SQL Remote ASA SQL Reference Revoke Remote DBA statement Revoke Remote DBA statement SQL Remote ASA SQL Reference Splinkoption procedure on SET Remote Option statementMessage link Start Subscription statement Start Subscription Stop Subscription statement Stop Subscription Synchronize Subscription statement Synchronize Subscription Update statement Update statement SQL Remote ASA SQL Reference,Create Trigger statement on Command Reference for Adaptive Server Enterprise 379 380 Spaddarticle procedure Making schema changes on SalesRepData382 Spaddarticlecol procedure To add a column to an article in a publicationSpaddarticlecol publicationname Spaddremotetable procedure To mark a table for SQL Remote replication Default conﬂict resolution Remote rows are stored in tables named oldCustomerRemoteCustomer, respectively 385 Spcreatepublication procedure Spdroppublication procedure onTo create a publication Spcreatepublication publicationname Spdroppublication procedure Spcreatepublication procedure onTo drop a publication from the database Spdroppublication publicationname Spdropremotetype procedure Spdropremotetype typename Spdropsqlremote procedure Spqueuedrop procedure onProcedure on Spgrantconsolidate procedure Databases acting as remote databases See also Description Example 392 Spgrantremote procedure Current database must be granted Remote permissions using Single quotesOne of the publications in the current database Speciﬁed 395 Splinkoption procedure SET Remote Option statement onDatabases ﬁle message system on Ftp message system onMapi message system on Smtp message system on Spmodifyarticle procedure To change the description of an article in a procedure Subscription expression Expression that is the repkey column399 Spmodifyremotetable procedure Spaddremotetable procedure onSpremoveremotetable procedure on Managing conﬂicts on 401 Sppassthrough procedure Sppassthroughpiece procedure To build a long SQL statement for passthroughSppassthroughpiece string 404 Sppassthroughstop procedure Resents passthrough modeSppassthroughstop procedure resents the list of recipients Currently being built Sppassthroughsubscription procedure StatementsSppassthroughsubscription publicationname Sppassthroughuser procedure Sppassthroughuser username Sppopulatesqlanywhere procedure Consolidated databaseCalled directly Sppopulatesqlanywhere Sppublisher procedure Sppublisher username Spqueueclean procedure SpqueuecleanBe called directly Log was scanned Spqueueconﬁrmeddeleteold procedure Spqueueconﬁrmeddeleteold Spqueueconﬁrmedtransaction procedure Match this offsetSpqueueconﬁrmedtransaction offset Spqueuedeleteold procedure Been conﬁrmed by all remote databasesSpqueuedeleteold Spqueuedrop procedure Spdropsqlremote procedure on Spqueuedumpdatabase procedure Spqueuedumptransaction procedure onStable queue recovery issues on Spqueuedumptransaction procedure Spqueuedumpdatabase procedure onSpqueuedumptransaction Spqueuegetstate procedure QueueSpqueuegetstate Spqueuelogtransferreset procedure Be called directly. It resets the page and row IDs to zeroSpqueuelogtransferreset Spqueueread procedure Use by the Message AgentSpqueueread startoffset Spqueuereset procedure Ready for a new SQL Remote setupSpqueuereset Spqueuesetconﬁrm procedure Users in the srqueuestate tableSpqueuesetconﬁrm conﬁrmoffset Spqueuesetprogress procedure Spqueuesetprogress pageid Spqueuetransaction procedure Spqueuetransaction offset Spremote procedure Message tracking information in the srremoteuser tableSpremote operation Spremoteoption procedure To set a SQL Remote option 426 Spremotetype procedure Spdropremotetype procedure onAlter Remote Message Type statement on Spremovearticle procedure Spaddarticle procedure onTo remove an article from a publication Spremovearticle publicationname Spremovearticlecol procedure To remove a column from an article in a publicationSpremovearticlecol publicationname Spremoveremotetable procedure Spmodifyremotetable procedure onTo mark a table as unavailable for SQL Remote replication Spremoveremotetable tablename Sprevokeconsolidate procedure Spgrantconsolidate procedure onSprevokeconsolidate username Sprevokeremote procedure Sprevokeremote username Spsubscription procedure Spsubscriptionreset procedure To reset all SQL Remote information for all remote usersSpsubscriptionreset Srsubscription tables to zero or Null Appendices Page 437 Types of difference Differences in functionality Differences in approach 441 Limitations for Enterprise to Enterprise replication Page Page Supported Platforms and Message Links 445 Supported message systems Supported operating systems Windows NT/2000/XP All message links 448 Symbols Index Index 450 451 Current Remote User 452 Sqlremote 453 454 LTM 455 456 Subscribebyremote 457 Remote 458 459 460 Send Every 461 462 463 Tempdb 464 465 466 467 Unix