This article was submitted by Sujoy Paul. He writes "
The plethora of mission critical databases in most companies has made it imperative to harness the valuable data by integrating across various database vendors and on different platforms. The seamless integration of the complexities involved is made possible by using replication across heterogeneous sources. MSSQLServer 2000 enables the usage of both snapshot and merge replication to take place across heterogeneous databases by OLEDB and ODBC. This article explains the usage of replicating data from MSSQLServer 2000 database to Access database." Introduction
Laptop Battery Replication enables data and database objects to be copied and modified from one database to another across different networks and platforms. Yet, the process of synchronization maintains the consistency of the database. The physical separation of the databases and latency are the integral part of the design process in replication. These characteristics enhance, among other things, the performance of the application. Other benefits, as cited by Books Online, but not limited to, includes facilitating greater autonomy to users who can work with a local copy of the database and then transfer the changes to remote or mobile users across the network or over the Internet.
..and many more other useful features. SQL server 2000 and SQL server 2005 are the best and the most complex database management tools on the market, and they can do much more than just managing and administrating databases, so they are the best choice if your company needs database administration and maintenance. A good source for rmation on how to install, customize, and make the most use of a SQL Server implementation, visit the Microsoft practice section of www.unitekconsulting.com.
Thinkpad MSSQLServer 2000 permits 3 different kinds of replication. They are snapshot, transactional and merge. Snapshot makes a copy of the data and propagates the changes of the whole set of data rather than individual transactions, thereby making it a discontinuous process and entailing a higher degree of latency. Transaction replication allows incremental changes of data to be transferred either continuously or at specific time intervals. Merge replication permits a higher degree of autonomy and allows the subscribers to update changes and then propagates the changes to the publishers which in turn transfers to other subscribers. This article describes this elegant transfer of data across a different data source like Access 2002 using snapshot replication. Configuration
Errata Description Chapter (PDF) Table of Contents (PDF) Index (PDF) Author Information Complete. Authoritative. Practical. The only SQL Server book you need. Mastering SQL Server 2000 is the one indispensable resource for anyone working with the latest version of SQL Server. Whether you build or administer SQL Server databases or write applications that communicate with them, you'll find the background knowledge and the practical instruction you need to accomplish any task, from the most basic to the most advanced.
Microsoft MSSQLServer 2000 communicates with heterogeneous databases like Access 2002 either by ODBC or OLEDB. The use of a linked server facilitates the execution of queries on heterogeneous databases from SQLServer. However, this requires that the OLEDB driver for the Access database reside on the same server as the SQLServer. Establishing a Linked Server
Get all the benefits of Microsoft SQL Server 7 with none of the headaches. in wizards, the Enterprise Manager, and the Query Analyzer. use stored procedures, save time by automating common tasks with Jobs and Alerts, import and export data to and from an SQL Server database, and keep your data secure and backed up with the tips, tricks, and techniques you'll discover inside Microsoft SQL Server 7 For Dummies. have SQL Server tools, too, including programs for automatically building multitier applications, creating SQL Server objects, friendly Visual Basic code.
Laptop Computers The linked server is set up using T-SQL. The steps needed in setting up a linked server are as follows: (a) create a linked server using sp_addlinkedserver, (b) setup the server options using sp_serveroption and (c) the login value of the linked server
Candidates who want to become an MCDBA must take a total of 4 exams. One exam covers administering SQL Server (either version 7 or 2000), one exam covers how to design a SQL Server database architecture, one exam covers administering Microsoft Server. The last exam must be taken from a list of electives. For rmation on the exams and requirements, click on the Exam Chart and Sybex Products link.
Laptop Computer (a) Firstly the linked server is created:
sp_addlinkedserver @server ="ACCESSSERVERPATIENTGP", @provider="Microsoft.Jet.OLEDB.4.0", @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\research\sqlserver\replication\GPPatient.mdb' go
How to find an ASP web host You will need to find a web host who will host your website on a Windows 2000 server. It is safer to host your ASP pages on a Windows 2000 server as they are more stable and most ASP components work with IIS (Internet Information Services), specific to Windows. You also need to consider if your web site uses a database (e.g. SQL, Access) and then make sure your web host provides support for the type of database your web site uses.
Desktop Computer Since the heterogeneous database is Access 2002, the name of the provider as specified in the online documentation is Microsoft.Jet.OLEDB.4.0. The name of the server is ACCESSSERVERPATIENTGP while the datasrc parameter specifies the file directory of the database.
Notebooks (b) The server options with their corresponding parameters for the linked server are then specified:
sp_serveroption 'ACCESSSERVERPATIENTGP','collation compatible', 'true' go sp_serveroption 'ACCESSSERVERPATIENTGP','rpc','true' go sp_serveroption 'ACCESSSERVERPATIENTGP','rpc out', 'true' go
Lenovo (c) Finally, login value is set to true so that SQLServer logins can be used to connect to the linked server:
sp_addlinkedsrvlogin 'ACCESSSERVERPATIENTGP', true go
Creation of the Database
Hard Drive In this scenario a database is created for a patient tracking system whereby the patients health plan, the symptoms and the possible prescription drugs are stored in the SQLServer 2000 repository. The database, entitled GPPatient, can be created by using the Enterprise Manager and following the create database wizard. The E-R diagram for the database is shown in Figure 1.
Travelstar Figure 1: E-R diagram of the GPPatient database Establishing a Distributor
Gateway The distributor along with the publisher can reside on the same server or on a remote server. In this case, the distributor is set up on the same server as the publisher. Administrators can set it up using the wizard in the Enterprise Manager by selecting Wizards/Replication/Configuring Publication and Distribution Wizard and following the steps. Establishing a Publisher
Laptop Parts The publisher is set up on the database called GPPatient. The steps needed to set up a publisher are: (a) create a publication using sp_addpublication, (b) create an agent for the publication in this case, snapshotusing sp_addpublication_snapshot, (c) grant publication access to the users using sp_grantpublication access and (d) create articles for publication using sp_addarticle. Alternatively, you can create the publication using the Tools/Replication/Create and Manage Publication wizard in Enterprise Manager and following the steps accordingly. Here, T-SQL has been used for the purpose.
Software (a) Create the snapshot publication called GPPatient on the publication database GPPatient. (The sp_replicationdboption can be used to set the database for replication)
sp_addpublication @publication = 'GPPatient', @sync_method = 'character', @repl_freq = 'snapshot', @status = 'active', @allow_push = 'true', @allow_pull = 'true', @allow_anonymous = 'false', @independent_agent = 'false', @immediate_sync = 'false', @allow_sync_tran = 'false', @autogen_sync_procs = 'false', @retention = 336, @allow_queued_tran = 'false', @snapshot_in_defaultfolder = 'true', @compress_snapshot = 'false', @allow_dts = 'false', @allow_subscription_copy = 'false', @add_to_active_directory = 'false'
Hard Drives The retention time is 336 hr, which is the default for subscription activity. If set to 0 then it is set to infinity.
Electronics (b) Create the snapshot agent for the publication GPPatient
sp_addpublication_snapshot @publication = 'GPPatient', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @snapshot_job_name = 'GPPatientAccess'
Canon (c) Grant publication access to the users
exec sp_grant_publication_access @publication = 'GPPatient', @login = 'distributor_admin' GO exec sp_grant_publication_access @publication = 'GPPatient', @login = 'sa' GO
Desktop Pc (d) Next we create the articles
-- Adding the snapshot articles sp_addarticle @publication = 'GPPatient', @article = 'Patient', @source_owner = 'dbo', @source_object = 'Patient', @destination_table = 'Patient', @type = 'logbased', @creation_script = null, @description = null, @pre_creation_cmd = 'drop', @schema_option = 0x00000000000000F1, @status = 0, @vertical_partition = 'false', @ins_cmd ='SQL', @del_cmd = 'SQL', @upd_cmd = 'SQL', @filter = null, @sync_object = null, @auto_identity_range = 'false'
Desktop Computers We use the same command as above for the rest of the tables in the database. The parameters ins_cmd, del_cmd and upd_cmd when set to SQL allows INSERT, DELETE and UPDATE commands to be replicated. Establishing the subscriber
Think Pad The linked server needs to be added as a subscriber. This is achieved by selecting Tools/Replication/Configure Publishers, Distributors and Subscribers from the Enterprise manager and then selecting Subscribers as shown below
Repair Figure 2: Publisher and Distributor Properties
Data Recovery We then select New and choose Microsoft Jet 4.0 database (Microsoft Access)
Cisco Figure 3: Enabling the New Subscriber
Keyboard This then brings up the new window as shown below. Enter the login and the password for Access.
Monitor
Desktop Figure 4: Setting up the subscriber for the linked server ACCESSSERVERPATIENTGP
Infosys We now need to select the subscription. Select Tools/Replication/Push Subscriptions to others
Refurbished Laptops
Wipro Figure 5: Selecting the publication for creating the new Subscription
Lap Top Highlight the publication that you want to subscribe and then select Push New Subscription
Refurbished
Memory Figure 6: Selecting the subscriber for the subscription
Intel After selecting the subscriber, select the destination database which happens to be GPPatient
As400
Averatec Figure 7: Selecting the database for the subscription
Hardware Set the agent as a continuous process for this process
Dual Xeon
Storage Figure 8: Set the schedule for the subscription
Seagate Then initialize the subscription as shown below
Computer Sales
Computer Hardware Figure 9: Set the initialization of subscription
Printers We will then insert some values in the Patient table to verify that they are replicated:
Insert into patient (firstname, lastname, dateofbirth, gender) Values('Smith','Joe','1/12/1950','M') Go Insert into patient (firstname, lastname, dateofbirth, gender) Values('West','S','1/12/1975','F') Go Insert into patient (firstname, lastname, dateofbirth, gender) Values('East','A','7/1/1955','M') Go Insert into patient (firstname, lastname, dateofbirth, gender) Values('Cheng','Bill','12/17/1959','M')
Technology Next we run the snapshot agent from Replication Monitor/Agents/Snapshot Agents from the Enterprise Manager for the data to be replicated.
Mainframe Using the OPENQUERY function we can then check to ensure that the data has been replicated from MSSQLServer 2000 to MSAccess 2002:
select * from openquery( ACCESSSERVERPATIENTGP, 'select firstname, lastname, dateofbirth, gender from patient')
Samsung You can also check the data in MSAccess2002 to verify that the data has been replicated.
[ Comment, Edit or Article Submission ]
