December 1, 2011 at 10:11 pm
Hi,
Server A:
Version - SQL SERVER 2000 with SP4.
Database size- 600 GB
OS - Windows server 2003 Enterprise edition.
Server B:
Version - SQL SERVER 2008 with SP3 - 64 bit
OS - Windows server 2008 R2 Enterprise edition - 64 bit.
This is centralize production database & size 600 GB and connected 5 different geography sites, currently database running on SQL server 2000 version.
Could anyone guide, suggestion me how to replicate database higher version from lower version? please provide step by step document. and which replicate types suitable for the same. 1.snapshot, 2. merge, 3. Transactional.
snapshot type recommended for small medium transaction and low database size.
Transactional type should required every table PK and always communicate between two server, there is no schedule time for transfer data to replicate server. But this
Thanks
ananda
December 1, 2011 at 11:02 pm
ananda.murugesan (12/1/2011)
Hi,Server A:
Version - SQL SERVER 2000 with SP4.
Database size- 600 GB
OS - Windows server 2003 Enterprise edition.
Server B:
Version - SQL SERVER 2008 with SP3 - 64 bit
OS - Windows server 2008 R2 Enterprise edition - 64 bit.
This is centralize production database & size 600 GB and connected 5 different geography sites, currently database running on SQL server 2000 version.
Could anyone guide, suggestion me how to replicate database higher version from lower version? please provide step by step document. and which replicate types suitable for the same. 1.snapshot, 2. merge, 3. Transactional.
snapshot type recommended for small medium transaction and low database size.
Transactional type should required every table PK and always communicate between two server, there is no schedule time for transfer data to replicate server. But this
Thanks
ananda
You should choose the replication type based on your business requierments only. You have to analyze what suits your business requirements best.
Rest, you can easily replicate tables from SQL Server 2000 database to SQL Server 2008, if you are going to use snapshot or transactional replication.
You can go through the below article to understand this better:
http://msdn.microsoft.com/en-us/library/ms143241.aspx
December 1, 2011 at 11:09 pm
Thanks Sujeet for your reply
As per business best is database should be 24x7 running.
Could you confirm, In transactional Replication should required for all tabled primary keys then only this replication type will work.
thanks
ananda
December 1, 2011 at 11:19 pm
All published tables in transactional replication must contain a declared primary key.
Considerations for Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151254(v=SQL.100).aspx
December 1, 2011 at 11:28 pm
Dev (12/1/2011)
All published tables in transactional replication must contain a declared primary key.Considerations for Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151254(v=SQL.100).aspx
Ok..All published tables in transactional replication must contain a declared primary key.
In this case, I am going to replicate existing database version sql 2000 to sql 2008. In existing database some of the tables not declared PK and more 500000 records stored. , IF any impact after creating PK those tables.
thanks
ananda
December 1, 2011 at 11:36 pm
ananda.murugesan (12/1/2011)
Dev (12/1/2011)
All published tables in transactional replication must contain a declared primary key.Considerations for Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151254(v=SQL.100).aspx
Ok..All published tables in transactional replication must contain a declared primary key.
In this case, I am going to replicate existing database version sql 2000 to sql 2008. In existing database some of the tables not declared PK and more 500000 records stored. , IF any impact after creating PK those tables.
thanks
ananda
If you have any key column to identify each record uniquely then please declare as PK. It will add an index (Clustered by default) which is good in most of the cases.
December 1, 2011 at 11:38 pm
ananda.murugesan (12/1/2011)
Dev (12/1/2011)
All published tables in transactional replication must contain a declared primary key.Considerations for Transactional Replication
http://msdn.microsoft.com/en-us/library/ms151254(v=SQL.100).aspx
Ok..All published tables in transactional replication must contain a declared primary key.
In this case, I am going to replicate existing database version sql 2000 to sql 2008. In existing database some of the tables not declared PK and more 500000 records stored. , IF any impact after creating PK those tables.
thanks
ananda
As Dev said, yes, primary key is required for all those tables you want to replicate.
If you can provide more details about why do you want to replicate all the tables to other database, we can help you better.
1. If you are doing the replication to prepare a database which can be used for reporting purpose, may be you need to replicate only those tables which are required for report processing.
2. If you are doing for some high availability option, then you can look for log shipping or mirroring instead of replication.
December 1, 2011 at 11:48 pm
Hi, i am going for high availability option not for report purpose,
Mirroring option- It is not possible for configure for different version because primary database is SQL 2000.
December 2, 2011 at 12:31 am
ananda.murugesan (12/1/2011)
Hi, i am going for high availability option not for report purpose,Mirroring option- It is not possible for configure for different version because primary database is SQL 2000.
Yes, as you are using SQL Server 2000 in production the options are limited 🙂
If you can add the primary key in all the tables then replication is a good option here. In case you can't, then you can look for the log shipping.
December 2, 2011 at 12:34 am
Divine Flame (12/2/2011)
ananda.murugesan (12/1/2011)
Hi, i am going for high availability option not for report purpose,Mirroring option- It is not possible for configure for different version because primary database is SQL 2000.
Yes, as you are using SQL Server 2000 in production the options are limited 🙂
If you can add the primary key in all the tables then replication is a good option here. In case you can't, then you can look for the log shipping.
I believe even Log Shipping is not available in SS2K. It's available since SS2K5.
December 2, 2011 at 12:49 am
Log shipping was available in SQL Server 2000 whereas Mirroring was introduced in SQL Server 2005 only.
December 2, 2011 at 1:02 am
You are right. Just found following 'Setting Up Log Shipping'.
http://msdn.microsoft.com/en-us/library/aa496029(v=SQL.80).aspx
But when I go to my bookmarked page 'Log Shipping Overview', I don't see SS2K and that made me confuse.
http://msdn.microsoft.com/en-us/library/ms187103.aspx
Besides all these facts, I am just thinking Can we log-ship from SS2K to SS2K8?
December 2, 2011 at 1:45 am
Yes Log Shipping is possible between SQL Server 2000 (Primary) & SQL Server 2008 (Secondary). However, secondary server would be in "NoRecovery" mode only. If we use same versions for both Primary & Secondary servers then secondary can be in "StandBy/ReadOnly" also.
December 2, 2011 at 1:58 am
Divine Flame (12/2/2011)
Yes Log Shipping is possible between SQL Server 2000 (Primary) & SQL Server 2008 (Secondary). However, secondary server would be in "NoRecovery" mode only. If we use same versions for both Primary & Secondary servers then secondary can be in "StandBy/ReadOnly" also.
Many other limitations.
Migrating a SQL Server 2000 Log Shipping Configuration to SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms188297.aspx
@ananda: Do you have any plans to upgrade your SS2K server anytime soon? I think you should consider it as well.
December 2, 2011 at 2:12 am
Dev (12/2/2011)
@ananda: Do you have any plans to upgrade your SS2K server anytime soon? I think you should consider it as well.
lol :-D, I think this is the first thing he should do this time.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply