Hi, I need opinion for my system that I consider replication.
Our company has about 300 stores and each store has Sql-Server DB, called TimeDB.
Each store TimeDB contains their own data only.
And main office has Sql-server DB called TimeDBHQ which contains all stores' data that are posted from all the stores.
Most of time, store post their data to TimeDBHQ in daily base by application. And TimeDBHQ distribute policy data and modified data to stores corresponding the data.
All data transfer are handled by application.
But, I consider Replication for the periodical transfer and consider application as preserved way.
The issue I considered first time when I designed the system without using replication is that there are too many subscriber/distributros.
I use replication for several servers, about 3-5, which is very small number comparing to 300 or more.
Here is my question,
Is it good that I use replication for my system?
because replication made by MS is more stable and bug free than my application.
and My first hasitation is on whether replication works stable among more than 300 servers..
If anyone has experiance with this issue please post any idea,
It will be big help!!!
thank you..What compamy?
Even at 10k a licence, that's huge...
You sure we're talking sql server?|||we use sql server personal editon on store side.
anyway..
please consern the issue of the design.
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Replication between servers
Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
Replication between servers
Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluation/features/replication.asp
Steve
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluation/features/replication.asp
Steve
Replication between servers
Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
Replication between servers
Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
sql
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
sql
Replication between servers
Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evalua...replication.asp
Steve
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evalua...replication.asp
Steve
Wednesday, March 21, 2012
Replication between offices
Hi. I work for a building company and they have about 20 sales offices spread throughout the city. We have the main server in the corp office with SQL Server 2000 and then each sales office will have will have a computer with MSDE 2000 on it. We are trying to replicate from the sales office up to the main server and then replicate that data out to all of the sales offices. Also occasionally the sales offices will be disconnected because of someone running over the line or some other reason so the offices will be connected but not all of the time. Merge Replication using a pull subscription seems the best way to go about it. But if anyone has any suggestions on another way to do this or hints that would be greatly appreciated.May refer to this link (http://vyaskn.tripod.com/repl_ques.htm) for more information.sql
Tuesday, March 20, 2012
replication and schema change
Hello everyone!
We are software developement company. We are planning to setup replication
for few our customers. But our software is still under developement and
database schema is changing quite often.
What is the best, fastest and easiest way to implement this database chnages
at our "replication" customers?
Uros
Uros,
sp_repladdcolumn and sp_repldropcolumn cater for a lot of schema changes. To
alter a column take a look at this article:
http://www.replicationanswers.com/AddColumn.asp. On SQL Server 2005 things
are easier: http://www.replicationanswers.com/AlterSchema2005.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||An alternative method which allows for more than just adding or removing 1
column at a time...
--Drop Subscription & Article
exec sp_dropsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
exec sp_droparticle @.publication = 'fxDB6_Pub1'
, @.article = 'MyTable'
-- Make DDL changes
ALTER TABLE MyTable
ALTER COLUMN...
-- Add article
exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
@.source_owner = N'dbo'
, @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
N'logbased', @.creation_script = null
, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16
, @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
@.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
@.filter = null
, @.sync_object = null, @.auto_identity_range = N'false'
-- Add Subscription(s)
exec sp_addsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
, @.destination_db = 'SubscrDBName'
, @.sync_type = 'automatic'
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Another solution for changes to many tables:
- drop all subscriptions
- drop publication
- make changes
- re-create publication, with script of course (ui takes too long w/ many
tables)
- add subscriptions
- fire up agents...
Regards,
ChrisB
www.MyDatabaseAdmin.com
"uros" wrote:
> Hello everyone!
> We are software developement company. We are planning to setup replication
> for few our customers. But our software is still under developement and
> database schema is changing quite often.
> What is the best, fastest and easiest way to implement this database chnages
> at our "replication" customers?
> Uros
|||Chris - Good suggestion. However, "@.schema_option =
0x00000000000000F3" can be a different value depending on the options one
may have chosen through the interface during the first attempt for the setup.
-A
"Chris" wrote:
[vbcol=seagreen]
> An alternative method which allows for more than just adding or removing 1
> column at a time...
> --Drop Subscription & Article
> exec sp_dropsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> exec sp_droparticle @.publication = 'fxDB6_Pub1'
> , @.article = 'MyTable'
> -- Make DDL changes
> ALTER TABLE MyTable
> ALTER COLUMN...
> -- Add article
> exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
> @.source_owner = N'dbo'
> , @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
> N'logbased', @.creation_script = null
> , @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x00000000000000F3, @.status = 16
> , @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
> @.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
> @.filter = null
> , @.sync_object = null, @.auto_identity_range = N'false'
> -- Add Subscription(s)
> exec sp_addsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> , @.destination_db = 'SubscrDBName'
> , @.sync_type = 'automatic'
> -- Start Snapshot agent - creates snapshot only for MyTable article
> -- Distribution agents will ship to subscribers
> Another solution for changes to many tables:
> - drop all subscriptions
> - drop publication
> - make changes
> - re-create publication, with script of course (ui takes too long w/ many
> tables)
> - add subscriptions
> - fire up agents...
> Regards,
> ChrisB
> www.MyDatabaseAdmin.com
> "uros" wrote:
We are software developement company. We are planning to setup replication
for few our customers. But our software is still under developement and
database schema is changing quite often.
What is the best, fastest and easiest way to implement this database chnages
at our "replication" customers?
Uros
Uros,
sp_repladdcolumn and sp_repldropcolumn cater for a lot of schema changes. To
alter a column take a look at this article:
http://www.replicationanswers.com/AddColumn.asp. On SQL Server 2005 things
are easier: http://www.replicationanswers.com/AlterSchema2005.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||An alternative method which allows for more than just adding or removing 1
column at a time...
--Drop Subscription & Article
exec sp_dropsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
exec sp_droparticle @.publication = 'fxDB6_Pub1'
, @.article = 'MyTable'
-- Make DDL changes
ALTER TABLE MyTable
ALTER COLUMN...
-- Add article
exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
@.source_owner = N'dbo'
, @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
N'logbased', @.creation_script = null
, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16
, @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
@.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
@.filter = null
, @.sync_object = null, @.auto_identity_range = N'false'
-- Add Subscription(s)
exec sp_addsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
, @.destination_db = 'SubscrDBName'
, @.sync_type = 'automatic'
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Another solution for changes to many tables:
- drop all subscriptions
- drop publication
- make changes
- re-create publication, with script of course (ui takes too long w/ many
tables)
- add subscriptions
- fire up agents...
Regards,
ChrisB
www.MyDatabaseAdmin.com
"uros" wrote:
> Hello everyone!
> We are software developement company. We are planning to setup replication
> for few our customers. But our software is still under developement and
> database schema is changing quite often.
> What is the best, fastest and easiest way to implement this database chnages
> at our "replication" customers?
> Uros
|||Chris - Good suggestion. However, "@.schema_option =
0x00000000000000F3" can be a different value depending on the options one
may have chosen through the interface during the first attempt for the setup.
-A
"Chris" wrote:
[vbcol=seagreen]
> An alternative method which allows for more than just adding or removing 1
> column at a time...
> --Drop Subscription & Article
> exec sp_dropsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> exec sp_droparticle @.publication = 'fxDB6_Pub1'
> , @.article = 'MyTable'
> -- Make DDL changes
> ALTER TABLE MyTable
> ALTER COLUMN...
> -- Add article
> exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
> @.source_owner = N'dbo'
> , @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
> N'logbased', @.creation_script = null
> , @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x00000000000000F3, @.status = 16
> , @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
> @.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
> @.filter = null
> , @.sync_object = null, @.auto_identity_range = N'false'
> -- Add Subscription(s)
> exec sp_addsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> , @.destination_db = 'SubscrDBName'
> , @.sync_type = 'automatic'
> -- Start Snapshot agent - creates snapshot only for MyTable article
> -- Distribution agents will ship to subscribers
> Another solution for changes to many tables:
> - drop all subscriptions
> - drop publication
> - make changes
> - re-create publication, with script of course (ui takes too long w/ many
> tables)
> - add subscriptions
> - fire up agents...
> Regards,
> ChrisB
> www.MyDatabaseAdmin.com
> "uros" wrote:
Monday, March 12, 2012
Replication Advice needed
Hello,
We are developing a software for a logistic company, which will have around 1000 branches, and we need to synchronize the database(SQL Server) between all the branches using the database at web server, i.e the branches can get the new data from the web server,as well as push the data at the web server, please tell us how can we accomplish this task,
ThanksThis wont work if I'm reading this correctly. You want to have a server both receiving and pushing data. Besides using it as the web server and replication on one server to feed 1000 sites. I say get 4 deal Racs Use
1. database server
2. replication server
3. webserver
4. backupserver.
at sites, have 2 server interfaces and one for live and the other for replication. Users should never get the live data. They only see the replication and thats transparent to them. Hope this helps. I'd also checkout lazydba.com real good info
We are developing a software for a logistic company, which will have around 1000 branches, and we need to synchronize the database(SQL Server) between all the branches using the database at web server, i.e the branches can get the new data from the web server,as well as push the data at the web server, please tell us how can we accomplish this task,
ThanksThis wont work if I'm reading this correctly. You want to have a server both receiving and pushing data. Besides using it as the web server and replication on one server to feed 1000 sites. I say get 4 deal Racs Use
1. database server
2. replication server
3. webserver
4. backupserver.
at sites, have 2 server interfaces and one for live and the other for replication. Users should never get the live data. They only see the replication and thats transparent to them. Hope this helps. I'd also checkout lazydba.com real good info
Friday, March 9, 2012
Replication ( Local to Hosting Company )
Hi,
I wanna replicate two SQL Server 2000 DB, On is in my Company behind a DSL
Router, the other is in a Machine on somewhere which I purchase as a Hosting
package..
Configuration of Local SQL Server is already Done by Me and Publication is
Created, I chose Snapshot one...
The problem is, How My remote SQL Server subscribe to the publisher, How can
I Subscribe and, If I can, How my Remote Server See my local Snapshot files
in order to get them in Remote DB, Should I send Snapshot files to Remote or
We have another solution ?
( Note that on Remote, I only can make a subscription, others are disabled )
What kind of solution do you propose ?
Have a look at this article: http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Configure your publication for an anoymous pull. Configure your ftp site for
anonymous access, and configure your publication to create the snapshot
within the ftp site.
Configure the subscriber for replication and then pull the subscription from
the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"BonGee" <Bongee@.bonbon.n.e.t> wrote in message
news:ufJsvNI3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I wanna replicate two SQL Server 2000 DB, On is in my Company behind a DSL
> Router, the other is in a Machine on somewhere which I purchase as a
> Hosting
> package..
> Configuration of Local SQL Server is already Done by Me and Publication is
> Created, I chose Snapshot one...
> The problem is, How My remote SQL Server subscribe to the publisher, How
> can
> I Subscribe and, If I can, How my Remote Server See my local Snapshot
> files
> in order to get them in Remote DB, Should I send Snapshot files to Remote
> or
> We have another solution ?
> ( Note that on Remote, I only can make a subscription, others are
> disabled )
> What kind of solution do you propose ?
>
I wanna replicate two SQL Server 2000 DB, On is in my Company behind a DSL
Router, the other is in a Machine on somewhere which I purchase as a Hosting
package..
Configuration of Local SQL Server is already Done by Me and Publication is
Created, I chose Snapshot one...
The problem is, How My remote SQL Server subscribe to the publisher, How can
I Subscribe and, If I can, How my Remote Server See my local Snapshot files
in order to get them in Remote DB, Should I send Snapshot files to Remote or
We have another solution ?
( Note that on Remote, I only can make a subscription, others are disabled )
What kind of solution do you propose ?
Have a look at this article: http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Configure your publication for an anoymous pull. Configure your ftp site for
anonymous access, and configure your publication to create the snapshot
within the ftp site.
Configure the subscriber for replication and then pull the subscription from
the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"BonGee" <Bongee@.bonbon.n.e.t> wrote in message
news:ufJsvNI3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I wanna replicate two SQL Server 2000 DB, On is in my Company behind a DSL
> Router, the other is in a Machine on somewhere which I purchase as a
> Hosting
> package..
> Configuration of Local SQL Server is already Done by Me and Publication is
> Created, I chose Snapshot one...
> The problem is, How My remote SQL Server subscribe to the publisher, How
> can
> I Subscribe and, If I can, How my Remote Server See my local Snapshot
> files
> in order to get them in Remote DB, Should I send Snapshot files to Remote
> or
> We have another solution ?
> ( Note that on Remote, I only can make a subscription, others are
> disabled )
> What kind of solution do you propose ?
>
Subscribe to:
Posts (Atom)