Data server, Client applications | Sybase DC38133-01-0902-01

Chapter 2. SQL Remote Concepts

The data server

The data server may be an Adaptive Server Enterprise or an Adaptive Server

Anywhere server. At the remote site the data server is commonly an

Adaptive Server Anywhere personal server, but can also be an Adaptive

Server Enterprise or Adaptive Server Anywhere server.

Client applications

Client applications work with the data in the database. Client applications use one of the client/server interfaces supported by the data server:

♦For Adaptive Server Anywhere, the client application may use ODBC, Embedded SQL, or Sybase Open Client to work with Adaptive Server Anywhere.

♦For Adaptive Server Enterprise, the client application may use one of the Sybase Client Server interfaces, ODBC, or Embedded SQL.

Client applications do not have to know if they are using a consolidated or remote database. From the client application perspective, there is no difference.

The Message Agent

The SQL Remote Message Agent sends and receives replication messages. It is a client application that sends and receives messages from database to database. The Message Agent must be installed at both the consolidated and at the remote sites.

For Adaptive Server Anywhere, the Message Agent is a program called dbremote.exe on PC operating systems, and dbremote on UNIX.

For Adaptive Server Enterprise, the Message Agent is a program called ssremote.exe on PC operating systems, and ssremote on UNIX.

Page 27

Image 27

Sybase DC38133-01-0902-01 manual Data server, Client applications, Message Agent

Contents

Part number DC38133-01-0902-01 Last modiﬁed October SQL Remote User’s GuidePage Contents SQL Remote Design for Adaptive Server Enterprise 141 SQL Remote Administration 199Principles of SQL Remote Design SQL Remote Design for Adaptive Server Anywhere Utilities and Options Reference 291 Administering SQL Remote for Adaptive Server Anywhere241Using SQL Remote with Replication Server 277 System Objects for Adaptive Server Anywhere 325 Command Reference for Adaptive Server Enterprise 379 Command Reference for Adaptive Server Anywhere 353 Index 449 Server Anywhere Differences 437SQL Remote for Adaptive Server Enterprise and Adaptive Supported Platforms and Message Links 445 Viii About This Manual Mail or ﬁle transferSystems Adaptive Server Anywhere Database Administration Guide This SQL Anywhere Studio documentationPage Page Release Savepoint savepoint-name Documentation conventions Xiv Following icons are used in this documentationClient application Database server, such as Sybase Adaptive Server Anywhere Asademo.db Adaptive Server Anywhere sample database Finding out more and providing feedback Page Xviii Introduction to SQL Remote Page This chapter introduces SQL Remote and the documentation Welcome to SQL Remote About SQL Remote SQL Remote for Adaptive Server Anywhere Enables replication About this manual Product installationTo install the SQL Remote software Windows To install the SQL Remote software UnixPage SQL Remote SQL Remote Concepts SQL Remote components Data server Client applicationsMessage Agent Message system client Two-table synchronization definition Publications and subscriptions At the remote database Replication SQL Remote features Page Server-to-laptop replication for mobile workforces Some sample installations Server-to-server replication among ofﬁces More Page Setting Up SQL Remote You must take some steps to Setup overviewEach user in the installation Sybase System Administrator should perform all SQL Remote Ensuring Tempdb is large enough Installing the SQL Remote system objectsTo install the SQL Remote system objects Preparing your Adaptive Server Enterprise server SQL Remote system objects To install the stable queue Command-line installation of the stable queuePage Upgrading SQL Remote for Adaptive Server Enterprise To upgrade SQL Remote 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 Database Just on those issues important for replicationIntroduction Goals Tables are described in more detail as follows Tutorials for Adaptive Server Anywhere Users Custkey Replication goalsRepkey Name Sybase Central or command-line utilities To prepare for the tutorial Preparing for the Sybase Central replication tutorial To add tables to the consolidated database Add a SQL Remote message type Setting up a consolidated database Add the publisher and remote user to the database To add an address to a message type To create a new user as the publisher To add a remote user Add publications and subscriptionsTo add a publication Publication for the remote database user ﬁelduser Set up the remote database in Sybase CentralEnter the expression repkey Click Finish to create the publication To extract a database To create the databases and directories for the tutorial Preparing for the replication 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 First, enter some data into the consolidated database Start replicating dataEnter data at the consolidated database Send data from the consolidated database Receive data at the remote database To send the data to the remote databaseTo receive data at the remote database Commit the insertion by executing the following statement To verify that the data has arrived Custkey Name Repkey To add the publication to the sample database Sample publicationPage Tutorial for Adaptive Server Enterprise Users Database schema for the tutorial is illustrated in the ﬁgure SalesRep table is many-to-one Tutorial for Adaptive Server Enterprise UsersPage First steps Tutorial Adaptive Server Enterprise replication 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 necessary users and permissions Create the message links and addresses To create the subscriber To create the publisher Create an empty publication Create the publication and subscription Extract the remote database To create a subscriptionCreate a subscription to SalesRepData for ﬁelduser , with a Subscription value of rep1 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 Connect to the Adaptive Server Enterprise server from isql To enter data at the Adaptive Server Enterprise database To receive the data at Adaptive Server Anywhere To replicate the data from Adaptive Server Enterprise Commit the row Custkey Name Repkey Page Replication Design for SQL Remote Page Principles of SQL Remote Design SQL Remote replicates data Adaptive Server Enterprise System Administrator or databaseDesign overview Ensuring compatible databases Between Ssremote and Adaptive Server Enterprise whenever Using compatible sort orders and character setsSQL Remote Message Agent does not perform any character set Conversions 819LATIN1 Eucjapan Replication of updates How statements are replicatedReplication of inserts and deletes Representatives Clause of the Update statementUser, the row to be updated is not found Statement Replication of triggers Replication of proceduresThis case, the Update does not update anything Procedure call is not replicated Some Before triggers can produce Message causing the conﬂictBody of your triggers Actions carried out by conﬂict Replication of data deﬁnition statements Replication of blobs How data types are replicated Replication of dates and times Select Who gets what? Principles of SQL Remote Design Replication errors and conﬂicts Replication errorsReplication conﬂicts Detected Tracking SQL errorsIssue the following statement VARCHAR, or Long VARCHAR. The procedure is called once withPage SQL Remote Design for Adaptive Server Anywhere Information each user receives Publications determine what informationIs shared among which databases Subscriptions determine what To publish one or more entire tables SQL Publishing dataPublishing whole tables To publish one or more entire tables Sybase Central Publishing only some columns in a table For more information, see the Create Publication statementTo publish only some columns in a table Sybase Central To publish only some columns in a table SQL Publishing only some rows in a table 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 Column To create an article using a subscription expression SQL To add articles Sybase Central Altering existing publications 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, Publication design for Adaptive Server Anywhere Design issues overview Design tips for performance Conditions for valid articles 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 For which there is no longer a Customer RepsProperly transferred to the new sales representative Along with the Customer Special Update statement for publications ∙ Customer table Understanding this helps in designing efﬁcient publicationsAssume the following data ∙ SalesRep table SalesRepData Publication Name rep1 Before list Sharing rows among several subscriptions Policy exampleMany-to-many relationship in the database 113 With the following informationEntire SalesRep table Involving the sales rep subscribed to the data Publication Contact example on Territory realignment with a many-to-many relationship 116 Remote databases contain the proper data Dealing with the customer, taken from the Policy table Customer being transferred Where custkey = OldRow.custkeyThat this subquery can be multi-valued Can be multi-valued Customer SQL Remote Design for Adaptive Server Anywhere ID Rep Dept Ann Marc Managing conﬂicts Updating Must be excluded from the installation by proper designSQL Remote installation at the consolidated database How SQL Remote handles conﬂicts Using conﬂict resolution triggers Implementing conﬂict resolution Referencing OLD AS oldval Create Trigger trigger-name Resolving date conﬂicts Conﬂict resolution examples Resolving inventory conﬂicts Reporting conﬂicts Increments from the two updates. For example Designing to avoid referential integrity errors Designing triggers to avoid errorsIncludes the salesorder table Page Ensuring unique primary keys Using global autoincrement default column values Setting the Globaldatabaseid value Declaring default global autoincrement 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 Cursor for 136 Else Adding new customers Insert Primary key pool summary To create and manage subscriptions in Sybase Central Creating subscriptions 140 For more information, see Create Subscription statement on 141 SQL Remote Design for Adaptive Server Enterprise 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 149 To handle this case, using an exampleContain the subscription column Two tables described earlier in this chapter 150 Are realigned Exec spcreatepublication ’SalesRepData’ go Adding a subscription-list column to the Contact table Log entries are values, not subscribers For the following operations Update on the Contact tableUpdate on the Customer table Maintaining the subscription-list column SQL Remote Design for Adaptive Server Enterprise Tuning extraction performance To create a subscription view Create a view that contains this subquery. For example156 Solving the problem Foreign KEY 158 Subscriptions to this publication take the following form These objects159 Triggers For Insert AS Begin 161 Ensure the quotedidentiﬁer option is set to on Tuning extraction performance for shared rows 163 Customer Managing conﬂicts When the conﬂicting message arrived Consolidated database, no row will be updatedAny conﬂict resolution procedures are called Database objects to handle the resolution This table must have the same column names Names of the conﬂict resolution objectsRemoterowtable. These three are discussed in turn Table only ever contains a single row ﬁrst conﬂict resolution example Two-table database is as follows168 @lostname value is the value that was overridden by Conﬂict-causing UpdateAfter the procedure is run, the rows in the OldCustomer Follows Second conﬂict resolution example To test the exampleConﬂictLog table has a single row, showing the conﬂict Replaces it with the value that was previously present It when the procedure is executedLost and won names User ID of the remote user. The table is as follows Currentremoteuser column of the temporary table #remote Where conﬂict resolution is carried out by Before triggersThere are several points of note here User ID of the remote user is stored by the Message Agent 173 Page Columns of this table have the following meanings 175 Primary key pool 176 To replicate the primary key pool 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 Conﬂicts as they occur Resolve update conﬂicts are all easy to doTest before deployment Changes to avoid on a running systemPage Mixed operating systems and database extraction Synchronizing databases Example To create a remote database from the reload ﬁle Using the extraction utilityWhere path is the path of the reload command ﬁle 191 Creating a database from the reload ﬁles Before extracting a database Using the extraction utility from Sybase Central To extract a database for a remote user Sybase Central Designing an efﬁcient extraction procedure An efﬁcient approach to extracting many databases Extracting groups Limits to using the extraction utility This setup is illustrated in the following diagram Using the extraction utility for Adaptive Server Enterprise Customizing the system tables Synchronizing data over a message system 199 SQL Remote Administration Management overview Granting and revoking Publish permissions Managing SQL Remote permissions 202 To create a new user as the publisher Sybase CentralTo make an existing user the publisher Sybase Central To revoke Publish permissions Sybase Central Exec sppublisher ’SBeaulieu’ go Granting Remote permissions Granting and revoking Remote and Consolidate permissions Send messages to e-mail address sbeaulieu@acme.com To make an existing user remote Sybase CentralFollowing options Use an Smtp e-mail system Selecting a send frequency Agent is run Permissions to user hquser, using the ﬁle message linkGranting Consolidate permissions Statement is executed automatically at the remote database Assigning permissions in multi-tier installations To revoke Remote permissions Sybase CentralPermission from user SBeaulieu Publish Working with message types Using message types Using Sybase Central to work with message types To add a message type Sybase CentralTo alter a message type Sybase Central Create Remote Message Type type-nameADDRESS address-string Using commands to work with message typesTo drop a message type Sybase Central To create a message type SQL 213 To alter a message type SQLTo drop a message type SQL Create Remote Message Type statement on To set a message control parameter Adaptive Server Anywhere Setting message type control parameters 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 Enterprise works on Running the Message AgentMessage Agent batch and continuous modes Names are ssremote and dbremote, respectively Connections used by the Message Agent Replication system recovery proceduresTo run the Message Agent in continuous mode To run the Message Agent in batch mode Management on Ensuring consistent Message Agent settingsSee the sections Transaction log and backup management on Adaptive Server Enterprise transaction log and backup Message Agent and replication security Troubleshooting errors at remote sites For more information, see The Message Agent on Execute the following command against the remote databaseTo conﬁgure a consolidated site to receive log information Use either the -roor the -rtMessage Agent option Tuning throughput by controlling Message Agent threading Tuning Message Agent performance Tuning throughput by caching messages Polling interval Tuning incoming message polling Requesting resends Example231 Tuning the message sending process Resending messages To those when tuning the incoming-message polling frequencyYour choices dictate how often updates are sent to When a remote user requests that a message be Dbremote -ru 1h Encoding scheme Encoding and compressing messages Creating custom encoding schemes Message tracking system Status information in the remoteuser tableTracking messages by transaction log offsets Transaction log Agent -loption to change this settingAre offsets in the local database transaction log, Cannot be a later offset than logsent Its resendcount Handling of lost or corrupt messagesPage 241 Administering SQL Remote for Adaptive Server Anywhere Starting the Message Agent Running the Message Agent as a service DBA authority grants them no extra permissions Full DBA permissions from the Message Agent When connectingFull DBA permissions on the database Suggested practice is to grant Remote DBA authority atPage Agent output Error reporting and handlingDefault error handling Ignoring errors 246 Implementing error handling proceduresExample e-mailing notiﬁcation of errors Following stored procedure implements this notiﬁcation Commit Work Columns have the following meaning 247 248 Here is a sample insert into the table from the above error Setting the transaction log directory Transaction log and backup management Backup utility options Using the live directory as the transaction log directorySuch a directory is kept in proper shape Using the backup directory as the transaction log directory Following command line 252 Managing old transaction logs Recovery with a single transaction log To recover the database254 Recovery with multiple transaction logs To recover from media failure on the C driveBackup the mirrored transaction log d\mirdir\consol.mlg Start the database using the following command line Make a copy of the database and log ﬁle Backups daily using the following commandCommand line would be Backup the mirrored transaction log d\mirdir\cons.mlg Backup procedures at remote databases Apply the renamed transaction logs in order, as followsRestart your system as normal 258 Upgrading consolidated databases To unload and reload a consolidated database manual Using passthrough mode Uses and limitations of passthrough mode However, the following dynamic SQL statement is replicated Operations not replicated in passthrough modeOn a running SQL Remote setup Procedure on the replicate side has the correct effect 263 Administering SQL Remote for Adaptive Server Enterprise How the Message Agent for Adaptive Server Enterprise works Scanning the transaction logMessage Agent on Stable queue Administering SQL Remote for Adaptive Server Enterprise 266 Message Agent operation phases 267 TransactionStable Logqueue Message Agent 268 Running multiple Message Agents For setup instructions, see Setting Up SQL Remote on To execute Agent output Protecting against media failure on the transaction log Transaction log management Stable queue recovery issues 274 With the following commandTruncation point can be reset with the following command Freed by Dump Transaction Making schema changes Schema modiﬁcations 277 Using SQL Remote with Replication Server When you need to use the SQL Remote Open Server How the pieces ﬁt together Architecture for Replication Server/SQL Remote installations Message system Stable Queue Agent On Windows operating systems, the SQL Remote Open Server is Information to retrieve it when it startsRemote Open Server on SQL Remote Open Server is the following executable 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 Create replication deﬁnitions for SQL Remote data Conﬁguring Replication ServerSet the dsixactgroupsize parameter Set the dsinumthreads parameter Suspend and restart the connection Other issues 288 This part presents reference material for SQL Remote Page 291 Utilities and Options Reference Message Agent Tracking system to ensure message deliveryDbremote ssremote options directory Utilities and Options Reference Replication security on Listing, see Connections used by the Message Agent on 295 Languagename,charsetname,sortorder For more information, see Running multiple Message Agents on Page Dbremote -rd 30s Page NetWare Registry, at the following locationYou should create a ﬁle named dbremote.ini Windows Extracting a remote database in Sybase Central Database Extraction utility 303 Extraction utility 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 SQL Remote Open Server Server Enterprise and Adaptive Server Anywhere databases Name of the executable is as follows Default value for the open server name is SSQueueGain access to the SQL Remote system tables Values default to the -cvaluesPage SQL Remote options Page Yy yyyy mm mmm dd Hh nn mm Ss.s 318 Relationships on SQL Remote event-hook procedures Sphookdbremotebegin and sphookssrmtbeginSphookdbremoteend and sphookssrmtend Sphookdbremoteshutdown and sphookssrmtshutdown Sphookdbremotereceivebegin and sphookssrmtreceivebeginSphookdbremotereceiveend and sphookssrmtreceiveend Sphookdbremotemessagemissing and sphookssrmtmessagemissing Sphookdbremotesendbegin and sphookssrmtsendbeginSphookdbremotesendend and sphookssrmtsendend Sphookdbremotemessagesent and sphookssrmtmessagesent 323 Has applied a set of messages from a userPage 325 System Objects for Adaptive Server Anywhere 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 Each row describes a SQL Remote publication Syspublication tableSysremoteoption table Sysremoteoptiontype table 329 Sysremotetype tableSysremoteuser table Publisher address Consolidate 330 Syssubscription table Remote permissions to one publication331 332 Syspublications view SQL Remote system viewsSysarticles view Sysarticlecols view Sysremoteusers view Sysremoteoptions view Syssubscriptions view Each row lists information about a subscription335 336 337 System Objects for Adaptive Server Enterprise Table has a single row #remote tableSrarticle table Manage SQL Remote information 339 Srarticlecol tableSrmarker table Databases in the same session Each row describes a replication option used by SQL Remote Srobject tableSroption table Srpassthrough table Srpublication table Srpublisher tableSrremoteoption table 342 Srremoteoptiontype tableSrremotetable table Srremotetype table 343 Srremoteuser table Received 344 345 Srsubscription table Srarticles view Srarticlecols viewSrpublications view Srremoteoptions view Srremotetables viewSrremotetypes view 348 Srremoteusers view 349 Srsubscriptions view 350 Stable Queue tablesSrqueuestate table Stable queue 351 Srtransaction table 352 Srconﬁrmedtransaction tableSrqueuecoordinate table Each row marks the corresponding row in srtransaction 353 Command Reference for Adaptive Server Anywhere 354 Automatic commit Alter Remote Message Type statementBeen created Must have DBA authority Create Publication statement Create Publication statement ASA SQL Reference,Subscribe by expression Create Remote Message Type message-system Create Remote Message Type statementMessages from a database Create Remote Message Type statement SQL Remote ASA SQL Create Subscription statement SQL Remote ASA SQL Reference Create Subscription statement Create Trigger statement Create Trigger statement SQL Remote ASA SQL Reference Update statement onMust have Resource authority and have Alter permissions on Table and thus requires exclusive use of the table Drop Publication statement ASA SQL Reference, Drop Publication statement Drop Remote Message Type message-system Drop Remote Message Type statementGranted Remote or Consolidate permissions with this type Drop Remote Message Type statement SQL Remote ASA SQL Drop Subscription statement SQL Remote ASA SQL Reference Drop Subscription statement Send Every AT ’hhmm ss’ Grant Consolidate statementCurrent database Grant Consolidate statement SQL Remote ASA SQL Reference Grant Publish statement Grant Remote statement Grant Remote DBA statement SQL Remote ASA SQL Reference Identified by passwordGrant Remote DBA statement Connected from the Message Agent Passthrough statement SQL Remote ASA SQL Reference, Passthrough modeSppassthrough procedure on Passthrough statement No automatic commit is done by this statement Remote Reset statementRemote Reset statement SQL Remote ASA SQL Reference, Subscriptions for a remote user in a single transaction Automatic commit. Drops all subscriptions for the user Sprevokeconsolidate procedure onRevoke Consolidate statement SQL Remote messages from this database Revoke Publish from userid Revoke Publish statementCurrent publisher Revoke Publish statement SQL Remote ASA SQL Reference Revoke Remote statement SQL Remote ASA SQL Reference Sprevokeremote procedure onRevoke Remote statement Messages from this database Revoke Remote DBA statement SQL Remote ASA SQL Reference Revoke Remote DBA statement Splinkoption procedure on SET Remote Option statementMessage link Start Subscription Start Subscription statement Stop Subscription Stop Subscription statement Synchronize Subscription Synchronize Subscription statement Update statement Update statement SQL Remote ASA SQL Reference,Create Trigger statement on 379 Command Reference for Adaptive Server Enterprise 380 Spaddarticle procedure Making schema changes on SalesRepData382 Spaddarticlecol procedure To add a column to an article in a publicationSpaddarticlecol publicationname To mark a table for SQL Remote replication Spaddremotetable procedure 385 Default conﬂict resolutionRemote rows are stored in tables named oldCustomer RemoteCustomer, respectively Spcreatepublication publicationname Spcreatepublication procedureSpdroppublication procedure on To create a publication Spdroppublication publicationname Spdroppublication procedureSpcreatepublication procedure on To drop a publication from the database Spdropremotetype typename Spdropremotetype procedure Spdropsqlremote procedure Spqueuedrop procedure onProcedure on Databases acting as remote databases Spgrantconsolidate procedure See also Description Example 392 Spgrantremote procedure Speciﬁed Current database must be granted Remote permissions usingSingle quotes One of the publications in the current database 395 Splinkoption procedure SET Remote Option statement onDatabases Smtp message system on ﬁle message system onFtp message system on Mapi message system on To change the description of an article in a procedure Spmodifyarticle procedure Subscription expression Expression that is the repkey column399 Managing conﬂicts on Spmodifyremotetable procedureSpaddremotetable procedure on Spremoveremotetable procedure on 401 Sppassthrough procedure Sppassthroughpiece procedure To build a long SQL statement for passthroughSppassthroughpiece string 404 Currently being built Sppassthroughstop procedureResents passthrough mode Sppassthroughstop procedure resents the list of recipients Sppassthroughsubscription procedure StatementsSppassthroughsubscription publicationname Sppassthroughuser username Sppassthroughuser procedure Sppopulatesqlanywhere Sppopulatesqlanywhere procedureConsolidated database Called directly Sppublisher username Sppublisher procedure Log was scanned Spqueueclean procedureSpqueueclean Be called directly Spqueueconﬁrmeddeleteold Spqueueconﬁrmeddeleteold procedure Spqueueconﬁrmedtransaction procedure Match this offsetSpqueueconﬁrmedtransaction offset Spqueuedeleteold procedure Been conﬁrmed by all remote databasesSpqueuedeleteold Spdropsqlremote procedure on Spqueuedrop procedure 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 pageid Spqueuesetprogress procedure Spqueuetransaction offset Spqueuetransaction procedure Spremote procedure Message tracking information in the srremoteuser tableSpremote operation To set a SQL Remote option Spremoteoption procedure 426 Spremotetype procedure Spdropremotetype procedure onAlter Remote Message Type statement on Spremovearticle publicationname Spremovearticle procedureSpaddarticle procedure on To remove an article from a publication Spremovearticlecol procedure To remove a column from an article in a publicationSpremovearticlecol publicationname Spremoveremotetable tablename Spremoveremotetable procedureSpmodifyremotetable procedure on To mark a table as unavailable for SQL Remote replication Sprevokeconsolidate procedure Spgrantconsolidate procedure onSprevokeconsolidate username Sprevokeremote username Sprevokeremote procedure Spsubscription procedure Srsubscription tables to zero or Null Spsubscriptionreset procedureTo reset all SQL Remote information for all remote users Spsubscriptionreset Appendices Page 437 Types of difference Differences in functionality Differences in approach 441 Limitations for Enterprise to Enterprise replication Page Page 445 Supported Platforms and Message Links Supported message systems Windows NT/2000/XP All message links Supported operating systems 448 Index Symbols 450 Index Current Remote User 451 Sqlremote 452 453 LTM 454 455 Subscribebyremote 456 Remote 457 458 459 Send Every 460 461 462 Tempdb 463 464 465 466 Unix 467