Member Avatar for Shulc

i was wondering how can database replication on a sql server be accomplished?

  • 4 Contributors
  • 6 Replies
  • 297 Views
  • 7 Years Discussion Span
  • Latest Post Latest Post by dhirajraj1

Dani AI

Generated

— quick reality check and a compact how-to.

What showed (DTS Import/Export) is a perfectly fine way to make a one‑time copy of a SQL Server 2000 database (you can copy objects and data), but it is not the same as setting up replication. Replication is an ongoing synchronization feature and SQL Server 2000 supports three built‑in replication models: Snapshot, Transactional and Merge. (flylib.com)

If you want ongoing sync rather than a one‑off copy, the usual workflow is: pick the replication type that matches your needs (snapshot = periodic full copies, transactional = near‑real‑time one‑way, merge = bi‑directional/offline clients), configure a Distributor (local or remote), create a Publication and choose the Articles (tables, views, procedures where appropriate), then create Subscriptions (push or pull). Replication agents (Snapshot Agent, Log Reader, Distribution and Merge Agents) perform the work and are manageable from Enterprise Manager / Replication Monitor. (codemag.com)

Practical gotchas to watch for: merge replication requires a ROWGUID/UNIQUEIDENTIFIER column (SQL Server will add one if needed), and published tables must have suitable keys — plan schema changes ahead. Replication does not copy server‑level objects (logins, jobs, linked servers); use the documented sp_help_revlogin approach or scripted deployment to move logins and recreate jobs. Replication agents must run under accounts with the right permissions, and initial snapshots can affect performance so schedule them off‑peak or use concurrent snapshot processing where appropriate. (documentation.help)

Recommendation: use DTS or backup/restore for a simple one‑time clone (as did), but plan a proper replication topology if you need continuous synchronization. Test the full initialize/sync cycle in a lab, verify agent security and network access, and monitor with Replication Monitor before switching to production. (learn.microsoft.com)

Recommended Answers

All 6 Replies

Member Avatar for Letscode

i was wondering how can database replication on a sql server be accomplished?

Do you mean,You want to create a copy of an existing database?

Member Avatar for Shulc

in short words, yes

Member Avatar for Letscode

1.Go to Enterprise Manager and right click the databases and create a new database

2.Right click the database which you created and select the ALL Tasks and then select IMPORT DATA

3.Now you should see DTS Import/Export Wizard.Hit next. Select the option Choose the data Source,Under the database select the database which you want to take a copy of.Hit next

4.Your destination will be the new database which you created.Select it and Hit next

5.Select the option Copy tables and source from the source database.Hit next

6.Hit the option Select all

7.Hit next and next until the tables start copying

8.All the tables and views will be stored on to the new database.

Hopy it Helps

in short words, yes

Member Avatar for Shulc

thnx, that helped a lot!

Member Avatar for AnilPatil

Do u known how to use replication of data base in sql server 2008

Member Avatar for dhirajraj1

To copy complete database including stored procedure, functions etc..you must have to choose the option "copy object and Data between SQL Server database" otherwise only tables with data would get copied

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.