October 30, 2007 at 7:33 pm
I managed to create a transactional replication in our two testing servers. Replication Monitor shows 3 items namely : snaphot agent,log reader, and the articlesI run some testings (like inserting a table in the publisher side) then started the snaphot agent. After i think a minute the snapshot agent says that : "A snapshot was not generated because no subscription needed initialization" then the table i created in the publisher side is not reflected in the subscriber side. Am i missing something??? what would be the process of a transactional replication in order to copy all the tables from the publisher to the subscriber (even the newly created or updated table)???? thanks in advance....
"-=Still Learning=-"
Lester Policarpio
October 30, 2007 at 8:15 pm
To get the table definitions and data to the replica database, you will need to create a subscription. In Enterprise Manager, you should see a node called "Replication". Expand it and you should see "Publications". In this you should see your publication. Right click on it and add a subscription.
Changing the definition of a table (adding/removing/changing columns, indexes etc) is not real good in SQL 2000. You either can't do it or it is not noticed by replication (depending on what you are changing). You can add or remove a column if you use the stored procedures sp_repladdcolumn and sp_repldropcolumn but you cannot use the ALTER TABLE command.
Adding a table requires you to add that table as a new article to the publication (sp_addarticle from memory) and include that article in the subscription. You can also do this using the publication properties.
The bottom line is there is no automation that is going to help you.
Life does get better with SQL 2005 - it does notice a lot more changes automatically. So, if you can migrate to SQL 2005, you will have a much better experience.
October 30, 2007 at 8:33 pm
I thought the duty of replication is to replicate or copy data from 1 publisher to subscriber/s?? Our reason of replicating databases is that my superior wnats a live backup (to another server) of our production database. I thought that this could be done using transactional replication so whenever the production database is updated (or whatever transaction is happening there) it is always replicated in the subscriber server. And also the reason of my superior for making a replication is that when the production server crashes or something bad happen we can switch to the subscriber server in an instant to avoid delay of the transactions since the production server is a delicate server (bank industry). Any suggestions??
P.S. Since views and sp(s) are not frequently changed we are planning to make a transactional replication of all tables of the database. Is this a good idea??? need help from experienced DBA (like you :D)
"-=Still Learning=-"
Lester Policarpio
October 30, 2007 at 10:37 pm
If you are needing a high availability solution, replication is not really a very good choice.
Have a look at SQL Server clustering and also at Log Shipping. These are intended to give you high availbility solutions.
Clustering has the potential to give the highest availability - but be warned, you do not simply walk up and say "let install a SQL Server cluster" tomorrow morning. It requires the correct software (e.g. SQL Server 2000 Enterprise Edition), hardware that is on the MS HCL, detailed planning of the installation and ongoing maintenance.
Log shipping is a little easier to implement but it does need some hand holding when a failover situation occurs. It also requires SQL Server 2000 Enterprise Edition.
There are other products out there that address availability as well. I am sure that a few minutes using google will help find them.
If you are genuinely need this sort of solution I would strongly recommend that you engage and experienced DBA who has experience with these sort of environments.
December 3, 2007 at 3:07 am
Yes, if the views and SPs are not so frequently changed/modified then transactional replication is the best solution to replicate the modified data (in tables) from the production server to the backup/secondary server for any activity like testing, upgrading, etc.
Hemant Sengar
December 28, 2007 at 6:40 am
But in transaction replication, if a table does not have any primary key then that table will not get replicated through transaction replication model.
If I am wrong please correct me
Nandhalal
December 28, 2007 at 9:30 pm
Hi Nandhalal,
You are absolutely correct, if the table does not have Primary Key than that table will not be replicated in transaction replication.
You have to define Primary Key on each table which need to be replicated.
Regards,
Hemant Sengar.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply