Introduction 1
Part 1 Replication 5
Chapter 1 Introducing Replication 7
Replication Model 8
Introducing the Types of Replication 10
Introducing Replication Options 12
Typical Uses of Replication 14
Reporting, Decision Support, and Data Warehousing Applications 15
Online/Offline Applications 17
Web-Based Applications 19
Keeping Data Close to Users 20
How Replication Works 21
Methods of Implementation 23
Agents and Monitors 26
Data Needs and Characteristics 26
Chapter 2 Planning For Replication 29
Distributed Update Factors 29
Business Objectives and Requirements 32
Evaluating the Replication Environment 32
Network Considerations 34
Security Considerations 35
Planning for Application Development 39
Planning for Each Type of Replication 40
Planning for Snapshot Replication 41
Planning for Transactional Replication 42
Planning for Merge Replication 45
Planning for Replication Options 49
Merge Replication or Updateable Subscriptions 52
Designing a Replication Topology 52
Physical Replication Models 53
Chapter3 Types of Replication 61
Snapshot Replication 62
How Snapshot Replication Works 63
Transactional Replication 66
How Transactional Replication Works 67
Merge Replication 73
How Merge Replication Works 74
Merge Replication Conflict Detection and Resolution 83
Chapter 4 Replication Tools 103
Replication and SQL Server Enterprise Manager 103
Replication Wizards 104
Replication Properties 107
Replication Icons 112
Replication Programming Interfaces 114
Programming Replication with ActiveX Controls 114
Programming Replication with SQL-DMO 115
Programming Replication with the Replication Distributor Interface 116
Transact-SQL System Stored Procedures 116
Windows Synchronization Manager 117
Active Directory Services 118
Chapter 5 Implementing Replication 121
Configuring Replication 122
Publishers, Distributors, and Subscribers 123
Publishing Data and Database Objects 125
Disabling Publishing and Distribution 125
Publishing Stored Procedure Execution 128
Using Custom Stored Procedures in Articles 131
Subscribing to Publications 133
Push Subscriptions 135
Pull Subscriptions 137
Anonymous Subscriptions 138
Applying the Initial Snapshot 139
Generating the Initial Snapshot 140
Compressed Snapshot Files 141
Alternate Snapshot Locations 141
Exploring Snapshots 143
Transferring Snapshots 143
Attachable Subscription Databases 145
Improving Performance While Generating and Applying Snapshots 147
Executing Scripts Before and After the Snapshot is Applied 149
Reinitializing Subscriptions 150
Synchronizing Data 151
Scripting Replication 153
Schema Changes on Publication Databases 154
Implementing Replication Over the Internet 158
Publishing Data Over the Internet Using VPN 159
Publishing Data Over the Internet Using Microsoft Proxy Server 159
Publishing Data Over the Internet Using TCP/IP and FTP 160
Replication Between Different Versions of SQL Server 162
SQL Server 7.0 Publisher/Distributor to SQL Server 6.5 Subscriber 163
SQL 6.5 Publisher/Distributor to SQL Server 7.0 Subscriber 165
SQL Server 6.5 Publisher to SQL Server 7.0 Distributor 165
SQL Server 7.0 Publisher/Distributor to SQL Server 6.0 Subscriber 165
Replication with SQL Server 2000 Windows CE Edition 166
Chapter 6 Replication Options 167
Filtering Published Data 169
Row Filters 169
Column Filters 172
Dynamic Filters 174
Join Filters 180
User-Defined Functions and Static Filters 183
User-Defined Functions and Dynamic Filters 184
Updatable Subscriptions 185
Immediate Updating 186
Queued Updating 194
Immediate Updating with Queued Updating as a Failover 206
Transforming Published Data 207
Alternate Synchronization Partners 219
How Alternate Synchronization Partners Works 219
Optimizing Synchronization 220
Using IDENTITY Values with Replication 223
Chapter 7 Replication Data Considerations 223
Managing Identity Values 224
Identity Ranges with Immediate Updating and Queued Updating 227
Managing Replicated timestamp Data 228
Using NOT FOR REPLICATION 229
Chapter 8 Administering and Monitoring Replication 231
Tools for Administering and Monitoring Replication 231
Replication Monitor 232
ActiveX Controls for Replication 233
Replication Agent Utilities 233
Windows NT Performance Monitor and Windows 2000 System Monitor 235
Setting Agent Parameters 235
Agent Profiles 236
Replication Agents 236
SQL Server Agent 237
Snapshot Agents 237
Distribution Agents 238
Log Reader Agents 240
Queue Reader Agents 241
Merge Agents 242
Miscellaneous Agents 245
Viewing Agent History 246
Handling Agent Errors 248
Remote Agent Activation 250
Replication Alerts 253
Automating a Response to an Alert 255
Subscription Deactivation and Expiration 256
Predefined Response Jobs 256
Validating Replicated Data 259
Chapter 9 Replication and Heterogeneous Data Sources 267
Heterogeneous Subscribers 268
Access Subscribers 270
Oracle Subscribers 272
IBM DB2/AS400 Subscribers 275
IBM DB2/MVS Subscribers 277
Other Heterogeneous Subscribers 278
Implementing Merge Replication to Access Subscribers 283
Heterogeneous Publishers 285
Chapter 10 Replication Security 287
Role Requirements 288
Connecting to the Distributor 290
Snapshot Folder Security 290
Publication Access Lists 291
Agent Login Security 292
Security and Replication Options 293
Security and Replication Over the Internet 296
Chapter 11 Enhancing Replication Performance 299
Enhancing Snapshot Replication Performance 304
Enhancing Snapshot and Transactional Replication Performance 305
Enhancing Transactional Replication Performance 307
Enhancing Merge Replication Performance 309
Chapter 12 Backing Up and Restoring Replication Databases 317
Strategies for Backing Up and Restoring Snapshot Replication 319
Strategies for Backing Up and Restoring Transactional Replication 321
Transactional Replication and Log Shipping 326
Strategies for Backing Up and Restoring Merge Replication 328
Restoring Backups of Replicated Databases to the Same Server and Database 334
Restoring Backups of Replicated Databases to a Different Server or Database 335
Part 2 English Query 337
Chapter 13 English Query Overview 339
What s New In English Query 339
Installation Requirements and Considerations 340
Chapter 14 English Query Fundamentals 343
English Query Fundamentals 343
SQL Database Normalization Rules 347
Updating or Replacing Models 354
Importing and Exporting Models to Meta Data Services 354
Chapter 15 Developing and Deploying English Query Applications 355
Creating an English Query Model 356
Expanding an English Query Model 357
Creating a Mixed English Query Model 358
Enabling Analysis Services for an English Query Model 359
Enabling SQL for an English Query Model 359
Enabling Full-Text Search for an English Query Model 360
Defining Entities in an English Query Model 361
Defining Relationship Phrasings 365
Using Command Relationships in an English Query Model 369
Authoring with Semantic Modeling Format 372
Testing an English Query Model 373
Compiling an English Query Application 375
Deploying an English Query Application 376
Adding Question Builder to an English Query Application 377
Automatic Clarification of Questions 379
Run-Time Environment for English Query Applications 380
Redistributing the English Query Run-time DLLs 381
Sample Applications and Client Interfaces 383
Chapter 16 Analysis Services in English Query 385
Naming Entities in the OLAP Project Wizard 386
Creating Relationships in the OLAP Project Wizard 387
Level-Level Relationships 389
Additional Relationships 389
Analysis Services Requirements for (All) Level 390
Chapter 17 Object Model Reference 391
Authoring Object Model 391
Authoring Object Model Diagram 392
Authoring Objects Summary 392
Authoring Properties Summary 396
Authoring Methods Summary 402
Authoring Sample Script 413
Engine Object Model 415
Engine Objects Summary 418
Engine Object Model Diagram 418
Engine Methods Summary 467
Engine Collections Summary 473
Engine Collections Methods Summary 478
Question Builder Object Model 479
Question Builder Object Model Diagram 480
Question Builder Objects Summary 480
Question Builder Properties Summary 484
Question Builder Methods Summary 493
Question Builder Collections Summary 495
Question Builder Collections Methods Summary 498
Chapter 18 How To Perform Common English Query Tasks 501
Working with Projects 501
How to add a new project to a solution 501
How to add an existing project to a solution 502
How to create a new project 503
How to create a new project using the SQL Project wizard 504
How to remove a project from the solution 506
How to create and modify project properties 506
How to remove a database from a project 506
How to add a module 507
How to remove a module 507
How to modify the data source in a project 507
Working with Models 508
How to add entities and relationships using the Create Semantics command 508
How to add or change a SQL connection 509
How to add or change an Analysis Services (formerly OLAP) connection 510
How to create a mixed model 510
How to delete a field from a model 511
How to edit a field in a model 511
Working with Joins 512
How to add a jion 512
How to delete a jion 513
How to edit a join 513
How to add a field to a table 514
How to add a table 514
Working with Tables 514
How to add tables and views from a SQL data source 515
How to change the table options for a relationship 516
How to delete a table 516
How to edit a table 517
How to enable full-text searching for a field 517
How to refresh all tables in a project 518
Working with Entities 518
How to add an entity to a relationship 518
How to create an entity 519
How to delete an entity 520
How to delete an entity from a relationship 521
How to edit an entity 521
How to edit the entity s role in a relationship 522
Working with Relationships 522
How to add a relationship 523
How to add phrasings to a relationship 524
How to add time and location options to a relationship 524
How to create a relationship using the Canvas pane 525
How to create relationships using the Create Relationships command 526
How to create relationships from the Model Test Window 526
How to associate English Data values with a relationship 527
How to delete a relationship 527
How to modify a relationship 528
Working with Dictionary Entries 528
How to add a dictionary entry 529
How to delete a dictionary entry 529
How to edit a dictionary entry 529
How to add a phrasing to a command relationship 530
Working with Command Relationships 530
How to create a command relationship 531
How to create time and location options for command relationships 532
How to delete a command relationship 532
How to edit command relationship 533
Working with Analysis Services (formerly OLAP Services) 533
How to add an Analysis Services (formerly OLAP) cube to a model 534
How to edit an Analysis Services (formerly OLAP) cube 534
How to edit Analysis Services (formerly OLAP) measures 535
How to edit Analysis Services levels 535
How to edit Analysis Services (formerly OLAP) properties 536
How to enable Analysis Services (formerly OLAP) 536
How to manually map OLAP objects to the underlying SQL database objects 537
How to remove an Analysis Services (formerly OLAP) cube 538
Testing and Building a Model 538
How to add a new or existing regression test file to a project 539
How to build an application 539
How to remove a regression test file 540
How to edit a regression test file 540
How to rename a regression test file 541
How to save model test information to a regression test file 541
How to save the regression output to a regression test file 541
How to set the Sample Data option 542
How to test a model 542
How to view the differences between the current regression test file and the regression output file 543
How to view the output of a regression test created using the Run Regression command 544
How to deploy an application to the Web 544
Index A Topics in the SQL Server 2000 Reference Library 545
Index B Index of English Query Elements 577