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
Showing posts with label location. Show all posts
Showing posts with label location. Show all posts
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/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
Tuesday, March 20, 2012
Replication and secure off-site servers
FYI - I know basically next to nothing about replication.
We have production servers that are located in a secure location entirely
separate from our own location and networkd. We want to set up a hot-copy
that is, at most, a few minutes behind production but that would be located
in our own office, on our own network. Any clues as to whether this is
possible using SQL Server replication or would we need to consider a third
party solution, such as that offered by DataMirror?
Thanks,
Michael MacGregor
Database Architect
Michael,
some people have used transactional replication for this purposes. To keep
the 2 systems entirely in sync will require careful planning though, as not
all objects can be replicated - eg permissions, users and indexes, and by
default you'll lose identity attributes. Certainly I can see queued updating
subscribers being used in this way, which avoids the issues of not having
the identity columns, but it still leaves the matter of the other objects
being manually synchronized. Log shipping on the other hand will take all
the extra objects. In theory the log could be shipped once a minute but in
my experience you'll be pushing it to get a very low latency. I suspect this
might be the best option for your case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||How does that work when there is a firewall between the Production server
and the hot-copy?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
> Michael,
> some people have used transactional replication for this purposes. To keep
> the 2 systems entirely in sync will require careful planning though, as
not
> all objects can be replicated - eg permissions, users and indexes, and by
> default you'll lose identity attributes. Certainly I can see queued
updating
> subscribers being used in this way, which avoids the issues of not having
> the identity columns, but it still leaves the matter of the other objects
> being manually synchronized. Log shipping on the other hand will take all
> the extra objects. In theory the log could be shipped once a minute but in
> my experience you'll be pushing it to get a very low latency. I suspect
this
> might be the best option for your case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hello Michael,
It is suggested that you create a VPN connection to the remote network so
that publisher/subscriber are virtually in the same network. You may want
to adjust the repliciation agent profile parameters so that it can work
properly on a low bandwidth.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Mon, 15 Aug 2005 12:53:49 -0400
| Lines: 27
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15827
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How does that work when there is a firewall between the Production server
| and the hot-copy?
|
| "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
| news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
| > Michael,
| > some people have used transactional replication for this purposes. To
keep
| > the 2 systems entirely in sync will require careful planning though, as
| not
| > all objects can be replicated - eg permissions, users and indexes, and
by
| > default you'll lose identity attributes. Certainly I can see queued
| updating
| > subscribers being used in this way, which avoids the issues of not
having
| > the identity columns, but it still leaves the matter of the other
objects
| > being manually synchronized. Log shipping on the other hand will take
all
| > the extra objects. In theory the log could be shipped once a minute but
in
| > my experience you'll be pushing it to get a very low latency. I suspect
| this
| > might be the best option for your case.
| > Cheers,
| > Paul Ibison SQL Server MVP, www.replicationanswers.com
| > (recommended sql server 2000 replication book:
| > http://www.nwsu.com/0974973602p.html)
| >
| >
|
|
|
|||Michael,
I agree with Peter's advice re VPN, but if this is not possible, port 1433
(or whatever your sql port is) will need to be opened, as well as your FTP
port. This can still be locked down by restricting access to certain IP
address subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In SQL 2005, are the replication processes improved, are there better
interfaces, etc., compared to SQL 2000/
Michael MacGregor
Database Architect
|||Hello Michael,
As I know, there are some improvments in replication in such as management,
performance, monitor etc. I suggest that you go to SQL 2005 beta newsgroup
for more details about this.
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
<O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
<zGjivHioFHA.940@.TK2MSFTNGXA01.phx.gbl>
<#x61AvjoFHA.2180@.TK2MSFTNGP15.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Fri, 19 Aug 2005 10:44:49 -0400
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <#y5qXyMpFHA.3536@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15951
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| In SQL 2005, are the replication processes improved, are there better
| interfaces, etc., compared to SQL 2000/
|
| Michael MacGregor
| Database Architect
|
|
|
|||Michael,
I haven't done any performance measurements yet so can't really compare this
side of things.
From the interface point of view, the basic functionality is enhanced (eg
Hostname as a subscription property in Merge, tracer tokens exist in
Transactional), there are a few completely new areas (eg Oracle -> SQL,
Merge over HTTP) and GUIwise, the Replication Monitor is now isolated as a
separate application. I've written a few basic articles on
www.replicationanswers.com on a few of these.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
We have production servers that are located in a secure location entirely
separate from our own location and networkd. We want to set up a hot-copy
that is, at most, a few minutes behind production but that would be located
in our own office, on our own network. Any clues as to whether this is
possible using SQL Server replication or would we need to consider a third
party solution, such as that offered by DataMirror?
Thanks,
Michael MacGregor
Database Architect
Michael,
some people have used transactional replication for this purposes. To keep
the 2 systems entirely in sync will require careful planning though, as not
all objects can be replicated - eg permissions, users and indexes, and by
default you'll lose identity attributes. Certainly I can see queued updating
subscribers being used in this way, which avoids the issues of not having
the identity columns, but it still leaves the matter of the other objects
being manually synchronized. Log shipping on the other hand will take all
the extra objects. In theory the log could be shipped once a minute but in
my experience you'll be pushing it to get a very low latency. I suspect this
might be the best option for your case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||How does that work when there is a firewall between the Production server
and the hot-copy?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
> Michael,
> some people have used transactional replication for this purposes. To keep
> the 2 systems entirely in sync will require careful planning though, as
not
> all objects can be replicated - eg permissions, users and indexes, and by
> default you'll lose identity attributes. Certainly I can see queued
updating
> subscribers being used in this way, which avoids the issues of not having
> the identity columns, but it still leaves the matter of the other objects
> being manually synchronized. Log shipping on the other hand will take all
> the extra objects. In theory the log could be shipped once a minute but in
> my experience you'll be pushing it to get a very low latency. I suspect
this
> might be the best option for your case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hello Michael,
It is suggested that you create a VPN connection to the remote network so
that publisher/subscriber are virtually in the same network. You may want
to adjust the repliciation agent profile parameters so that it can work
properly on a low bandwidth.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Mon, 15 Aug 2005 12:53:49 -0400
| Lines: 27
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15827
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How does that work when there is a firewall between the Production server
| and the hot-copy?
|
| "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
| news:ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl...
| > Michael,
| > some people have used transactional replication for this purposes. To
keep
| > the 2 systems entirely in sync will require careful planning though, as
| not
| > all objects can be replicated - eg permissions, users and indexes, and
by
| > default you'll lose identity attributes. Certainly I can see queued
| updating
| > subscribers being used in this way, which avoids the issues of not
having
| > the identity columns, but it still leaves the matter of the other
objects
| > being manually synchronized. Log shipping on the other hand will take
all
| > the extra objects. In theory the log could be shipped once a minute but
in
| > my experience you'll be pushing it to get a very low latency. I suspect
| this
| > might be the best option for your case.
| > Cheers,
| > Paul Ibison SQL Server MVP, www.replicationanswers.com
| > (recommended sql server 2000 replication book:
| > http://www.nwsu.com/0974973602p.html)
| >
| >
|
|
|
|||Michael,
I agree with Peter's advice re VPN, but if this is not possible, port 1433
(or whatever your sql port is) will need to be opened, as well as your FTP
port. This can still be locked down by restricting access to certain IP
address subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In SQL 2005, are the replication processes improved, are there better
interfaces, etc., compared to SQL 2000/
Michael MacGregor
Database Architect
|||Hello Michael,
As I know, there are some improvments in replication in such as management,
performance, monitor etc. I suggest that you go to SQL 2005 beta newsgroup
for more details about this.
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Michael MacGregor" <nospam@.nospam.com>
| References: <OTScNXaoFHA.1044@.tk2msftngp13.phx.gbl>
<ekPb3DboFHA.3316@.tk2msftngp13.phx.gbl>
<O37aunboFHA.1996@.TK2MSFTNGP10.phx.gbl>
<zGjivHioFHA.940@.TK2MSFTNGXA01.phx.gbl>
<#x61AvjoFHA.2180@.TK2MSFTNGP15.phx.gbl>
| Subject: Re: Replication and secure off-site servers
| Date: Fri, 19 Aug 2005 10:44:49 -0400
| Lines: 7
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
| Message-ID: <#y5qXyMpFHA.3536@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 10.84.116.206.in-addr.arpa.salesdriver.com
206.116.84.10
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:15951
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| In SQL 2005, are the replication processes improved, are there better
| interfaces, etc., compared to SQL 2000/
|
| Michael MacGregor
| Database Architect
|
|
|
|||Michael,
I haven't done any performance measurements yet so can't really compare this
side of things.
From the interface point of view, the basic functionality is enhanced (eg
Hostname as a subscription property in Merge, tracer tokens exist in
Transactional), there are a few completely new areas (eg Oracle -> SQL,
Merge over HTTP) and GUIwise, the Replication Monitor is now isolated as a
separate application. I've written a few basic articles on
www.replicationanswers.com on a few of these.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Friday, March 9, 2012
Replication - How to create objects as dbo.object when not SA
I just completed creating a replication job between a server at my location, where I have SA privileges, to a subscription server at a location where I have db_owner rights (no SA). When replication runs, the objects get created on the subscriber as userid.object rather then dbo.object. How do I cause replication to create the objects as dbo.object?
DaveHere is some additional information. The subscriber server is at a remote location managed by another IT department. I connected to the subscriber with a user id that was created in the role db_owner. The security was setup in Enterprise Manager by clicking on Replication and then right clicking on Publication. From there I chose "Configure Publishing, Subscibers and Distribution..." and then the Subscribers tab. For the subscriber I chose "Use SQL Server Authentication" and entered my dbo user id. Since I do not have SA rights on this server I need a way to have my dbo id created objects as dbo.objectname.
Hope this helps.
Dave|||When you create the publication (through 'Publication Properties' in SQL-EM) you can specify the owner of the articles to be created in the subscription database. You do this with the 'Article Defaults' button on the 'Articles' tab. Enter dbo in destination table owner field (or destination stored procedure owner field for stored procedures). If you leave these fields blank then the destination objects will be created with the current user as their owner.|||Shortly after I submitted my question I discovered exactly what you indicated, but I found that SQL Server is creating its own stored procedures (sp_MSDel_tablename, sp_MSIns_tablename) using my userid and not dbo. I noticed the Commands tab lists these stored procedures for each table. I assume this is the place where I must prefix each procedure with dbo.. Does this sound correct or is there another way to address SQL Server created procedures?
Thanks, Dave
DaveHere is some additional information. The subscriber server is at a remote location managed by another IT department. I connected to the subscriber with a user id that was created in the role db_owner. The security was setup in Enterprise Manager by clicking on Replication and then right clicking on Publication. From there I chose "Configure Publishing, Subscibers and Distribution..." and then the Subscribers tab. For the subscriber I chose "Use SQL Server Authentication" and entered my dbo user id. Since I do not have SA rights on this server I need a way to have my dbo id created objects as dbo.objectname.
Hope this helps.
Dave|||When you create the publication (through 'Publication Properties' in SQL-EM) you can specify the owner of the articles to be created in the subscription database. You do this with the 'Article Defaults' button on the 'Articles' tab. Enter dbo in destination table owner field (or destination stored procedure owner field for stored procedures). If you leave these fields blank then the destination objects will be created with the current user as their owner.|||Shortly after I submitted my question I discovered exactly what you indicated, but I found that SQL Server is creating its own stored procedures (sp_MSDel_tablename, sp_MSIns_tablename) using my userid and not dbo. I noticed the Commands tab lists these stored procedures for each table. I assume this is the place where I must prefix each procedure with dbo.. Does this sound correct or is there another way to address SQL Server created procedures?
Thanks, Dave
Subscribe to:
Posts (Atom)