Hi,
I have the following scenario, and need to configure
replication for this. Pls advice.
We have 3 SQL server databases, at 3 locations on SQL
server 2000 servers. They are connected over WAN by 2
MBPS link (triangle). I have to configure replication on
this. Database has around 200 tables, and size of
database would be around 200-500 MB each. Changes would
be minor on daily basis.
I have these servers at following locations.
Loc1 (HeadOffice)
Loc2 (branch office)
Loc3 (branch office)
Data has to replicate from Loc2 and Loc3 to Loc1 at short
intervals during the day.
Somewhere in midnight (off peak hours), data has to
replicate from Loc1 to Loc2 and Loc3 (for changes made at
Loc1 to be reflected in Loc2 and Loc3).
These database are modified only during the day, and
number of users at each location will not exceed 30.
I have following queries
- Can I configure Transactional replication for Loc2 ->
Loc1 and Loc3 -> Loc1 to replicate at short intervals.
And additionally Snapshot to happen at midnight from
Loc1 -> Loc2 and Loc3.
Or do I require Merge replication for this scenario. (or
any other solution you think would suit this requirement)
Pls suggest.
Thanx,
Shrikant,
the easiest scenario would be mege replication. Snapshot will copy over the
whole tables and unless the tables are small or all rows are always updated,
this is not recommended, and you'd have to set up replication each time
after the snapshot - possible but not so nice. Also, if the data is
partitioned then great, but if not then merge will take care of that with
the conflict resolvers. The only thing is that you won't be able to easily
prevent the download of data from Loc1 until the evening, as these changes
will propagate during the frequent synchronizations. If this is not at all
desirable, than you might have to consider a more radical solution - you
could have Loc1 as the publisher but use unidirectional merge with the
subscribers and reinitialize each evening.
HTH,
Paul Ibison
|||Hi Paul,
Thanx for your reply.
But would not be using the bi-directional transactional
replication solve this ?
Loc1 acts as Publisher with Loc2 and Loc3 as subscribers.
And then Loc2 and Loc3 as Publishers, and Loc1 as
Subscriber ?
regds,
Shrikant
>--Original Message--
>Shrikant,
>the easiest scenario would be mege replication. Snapshot
will copy over the
>whole tables and unless the tables are small or all rows
are always updated,
>this is not recommended, and you'd have to set up
replication each time
>after the snapshot - possible but not so nice. Also, if
the data is
>partitioned then great, but if not then merge will take
care of that with
>the conflict resolvers. The only thing is that you won't
be able to easily
>prevent the download of data from Loc1 until the
evening, as these changes
>will propagate during the frequent synchronizations. If
this is not at all
>desirable, than you might have to consider a more
radical solution - you
>could have Loc1 as the publisher but use unidirectional
merge with the
>subscribers and reinitialize each evening.
>HTH,
>Paul Ibison
>
>.
>
|||Shrikant,
this would be an option. If you expect conflicts then it can become terribly complicated, and the initial setup is not straightforward, but you're right - it is in some ways a simpler solution. To You might want to have a look at the most recent issue of
SQL Server magazine if you're thinking of going down this route - there is a nice article on setting it up.
Regards,
Paul Ibison
Showing posts with label locations. Show all posts
Showing posts with label locations. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Replication between 2 or more SQL Express databases
Problem description
There is one central SQL Express database. And one (or more) SQL Express
database(s) on remote locations. This approach is mandatory due to some
application features later-on.
The local database(s) on the remote location(s) will act as a sort of backup
in case there is no network connection. Meaning that the data is stored
locally at all times. And moved to the central database once the network
connection is on.
Updates to the local database(s) should trigger a process where all data is
moved to the central database if there is a network connection. So for now,
it’s a one way stream: remote to central.
This approach gives us the option to delay the moves - if needed. For
example, the daily data is kept local and moved to the central site over
night. Which is a good thing to have if network resources are limited during
office hours.
Solution
To make this happen, I was thinking allong the lines of using triggered
stored procedures.
Where a local database update triggers a stored procedure that checks the
network connection and performs the move to the central database.
If there is more than one record in the remote database, all records are
moved to the central database. Also, an entry should be added to a log-file.
If the network connection is not there for whatever reason, an entry should
be written in a log-file stating that there was no connection to the central
database.
Options
Another way of solving this is using RMO-based publications and subscribers.
Where the database on the remote locations are publishers. And the central
database are subscribers. However, according to Microsoft:
- SQL Express can only act as a subscriber.
- Data is copied. And not moved.
According to the SQL-BOL, the first limitation doesn’t exist when using
programmatic, transactional replication.
However, the second needs to be solved as well. And in the examples of
SQL-BOL, this is not mentioned. At least not that I’m aware of.
There are some advantages using RMO. For example deploying a new version of
the application. Or deploying an updated database schema. Please let me know
if this is proven to be really usefull. If so, this would be the preferred
method – provided that the limitations mentioned earlier are solved. And if
using this method, I would prefer stored procedures or Visual Basic for
programming this.
Help wanted
Since I’m a newbie on these replication matters, I would like some help with
this.
Who is willing to help with examples and suggestions?
Sofar, I’ve been able to:
- Install SQL Express with network connections enabled
- Running the Management Studio and create the data model I would like to use
- Add data manually and via an ODBC connection (using a system wide DSN).
- Run SQL select commands against the added data.
So the infrastructure is up and running….
firing triggers over the network is not scalable, as there is significant
latency involved for each trigger firing. For a batch operation it is
painful. Your transactions will also hang for up to 20 seconds if the
connection is down.
You might want to look at SSIS (Integration Services) or perhaps even bcp
for what you are trying to do.
You might also want to upgrade from Express to Standard so you can do
transactional replication which will do exactly what you are trying to do.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:474B4797-1811-4ADC-8576-0137A4FCBAF9@.microsoft.com...
> Problem description
> --
> There is one central SQL Express database. And one (or more) SQL Express
> database(s) on remote locations. This approach is mandatory due to some
> application features later-on.
> The local database(s) on the remote location(s) will act as a sort of
> backup
> in case there is no network connection. Meaning that the data is stored
> locally at all times. And moved to the central database once the network
> connection is on.
> Updates to the local database(s) should trigger a process where all data
> is
> moved to the central database if there is a network connection. So for
> now,
> it's a one way stream: remote to central.
> This approach gives us the option to delay the moves - if needed. For
> example, the daily data is kept local and moved to the central site over
> night. Which is a good thing to have if network resources are limited
> during
> office hours.
> Solution
> --
> To make this happen, I was thinking allong the lines of using triggered
> stored procedures.
> Where a local database update triggers a stored procedure that checks the
> network connection and performs the move to the central database.
> If there is more than one record in the remote database, all records are
> moved to the central database. Also, an entry should be added to a
> log-file.
> If the network connection is not there for whatever reason, an entry
> should
> be written in a log-file stating that there was no connection to the
> central
> database.
> Options
> Another way of solving this is using RMO-based publications and
> subscribers.
> Where the database on the remote locations are publishers. And the central
> database are subscribers. However, according to Microsoft:
> - SQL Express can only act as a subscriber.
> - Data is copied. And not moved.
> According to the SQL-BOL, the first limitation doesn't exist when using
> programmatic, transactional replication.
> However, the second needs to be solved as well. And in the examples of
> SQL-BOL, this is not mentioned. At least not that I'm aware of.
> There are some advantages using RMO. For example deploying a new version
> of
> the application. Or deploying an updated database schema. Please let me
> know
> if this is proven to be really usefull. If so, this would be the preferred
> method - provided that the limitations mentioned earlier are solved. And
> if
> using this method, I would prefer stored procedures or Visual Basic for
> programming this.
> Help wanted
> --
> Since I'm a newbie on these replication matters, I would like some help
> with
> this.
> Who is willing to help with examples and suggestions?
> Sofar, I've been able to:
> - Install SQL Express with network connections enabled
> - Running the Management Studio and create the data model I would like to
> use
> - Add data manually and via an ODBC connection (using a system wide DSN).
> - Run SQL select commands against the added data.
> So the infrastructure is up and running..
>
|||Hi Hilary,
Thanks for your support and feedback. I really do appreciate this!!!
> firing triggers over the network is not scalable, as there is significant
> latency involved for each trigger firing. For a batch operation it is
> painful. Your transactions will also hang for up to 20 seconds if the
> connection is down.
Mmm - sounds like we have a different understanding of the term trigger.
I would like to use triggers on the remote databases - not the central one.
As a result, triggers are not travelling across the network.
> You might want to look at SSIS (Integration Services) or perhaps even bcp
> for what you are trying to do.
I looked at SSIS. That would require
I'm not familiar with BCP. But it looks like a bulk COPY tool.
How would this help in MOVING the data?
> You might also want to upgrade from Express to Standard so you can do
> transactional replication which will do exactly what you are trying to do.
Do you mean replace Express with standaard on the remote side?
On the central side? Or both?
Keep in mind that SQL Express on the remote sites needs to run on
Workstations.
Ans therefor requires a light footprint.
Will
|||Answers inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:E6201678-BFD9-4290-ABAC-1D7A5F6E1962@.microsoft.com...
> Hi Hilary,
> Thanks for your support and feedback. I really do appreciate this!!!
>
> Mmm - sounds like we have a different understanding of the term trigger.
> I would like to use triggers on the remote databases - not the central
> one.
> As a result, triggers are not travelling across the network.
>
> I looked at SSIS. That would require
> I'm not familiar with BCP. But it looks like a bulk COPY tool.
> How would this help in MOVING the data?
>
it doesn't move data, it copies it row by row. I know of no technology
which actually moves that, all copy it and you have to delete it yourself on
the source.
> Do you mean replace Express with standaard on the remote side?
> On the central side? Or both?
> Keep in mind that SQL Express on the remote sites needs to run on
> Workstations.
> Ans therefor requires a light footprint.
In retrospect you will probably need a code solution. Put a timestamp on
your tables which you can use to determine which rows have changed since the
last time you "moved" data. Anything greater than the timestamp the last
time you polled must be moved.
You will probably be able to use any version of SQL Server for this.
> --
> Will
>
There is one central SQL Express database. And one (or more) SQL Express
database(s) on remote locations. This approach is mandatory due to some
application features later-on.
The local database(s) on the remote location(s) will act as a sort of backup
in case there is no network connection. Meaning that the data is stored
locally at all times. And moved to the central database once the network
connection is on.
Updates to the local database(s) should trigger a process where all data is
moved to the central database if there is a network connection. So for now,
it’s a one way stream: remote to central.
This approach gives us the option to delay the moves - if needed. For
example, the daily data is kept local and moved to the central site over
night. Which is a good thing to have if network resources are limited during
office hours.
Solution
To make this happen, I was thinking allong the lines of using triggered
stored procedures.
Where a local database update triggers a stored procedure that checks the
network connection and performs the move to the central database.
If there is more than one record in the remote database, all records are
moved to the central database. Also, an entry should be added to a log-file.
If the network connection is not there for whatever reason, an entry should
be written in a log-file stating that there was no connection to the central
database.
Options
Another way of solving this is using RMO-based publications and subscribers.
Where the database on the remote locations are publishers. And the central
database are subscribers. However, according to Microsoft:
- SQL Express can only act as a subscriber.
- Data is copied. And not moved.
According to the SQL-BOL, the first limitation doesn’t exist when using
programmatic, transactional replication.
However, the second needs to be solved as well. And in the examples of
SQL-BOL, this is not mentioned. At least not that I’m aware of.
There are some advantages using RMO. For example deploying a new version of
the application. Or deploying an updated database schema. Please let me know
if this is proven to be really usefull. If so, this would be the preferred
method – provided that the limitations mentioned earlier are solved. And if
using this method, I would prefer stored procedures or Visual Basic for
programming this.
Help wanted
Since I’m a newbie on these replication matters, I would like some help with
this.
Who is willing to help with examples and suggestions?
Sofar, I’ve been able to:
- Install SQL Express with network connections enabled
- Running the Management Studio and create the data model I would like to use
- Add data manually and via an ODBC connection (using a system wide DSN).
- Run SQL select commands against the added data.
So the infrastructure is up and running….
firing triggers over the network is not scalable, as there is significant
latency involved for each trigger firing. For a batch operation it is
painful. Your transactions will also hang for up to 20 seconds if the
connection is down.
You might want to look at SSIS (Integration Services) or perhaps even bcp
for what you are trying to do.
You might also want to upgrade from Express to Standard so you can do
transactional replication which will do exactly what you are trying to do.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:474B4797-1811-4ADC-8576-0137A4FCBAF9@.microsoft.com...
> Problem description
> --
> There is one central SQL Express database. And one (or more) SQL Express
> database(s) on remote locations. This approach is mandatory due to some
> application features later-on.
> The local database(s) on the remote location(s) will act as a sort of
> backup
> in case there is no network connection. Meaning that the data is stored
> locally at all times. And moved to the central database once the network
> connection is on.
> Updates to the local database(s) should trigger a process where all data
> is
> moved to the central database if there is a network connection. So for
> now,
> it's a one way stream: remote to central.
> This approach gives us the option to delay the moves - if needed. For
> example, the daily data is kept local and moved to the central site over
> night. Which is a good thing to have if network resources are limited
> during
> office hours.
> Solution
> --
> To make this happen, I was thinking allong the lines of using triggered
> stored procedures.
> Where a local database update triggers a stored procedure that checks the
> network connection and performs the move to the central database.
> If there is more than one record in the remote database, all records are
> moved to the central database. Also, an entry should be added to a
> log-file.
> If the network connection is not there for whatever reason, an entry
> should
> be written in a log-file stating that there was no connection to the
> central
> database.
> Options
> Another way of solving this is using RMO-based publications and
> subscribers.
> Where the database on the remote locations are publishers. And the central
> database are subscribers. However, according to Microsoft:
> - SQL Express can only act as a subscriber.
> - Data is copied. And not moved.
> According to the SQL-BOL, the first limitation doesn't exist when using
> programmatic, transactional replication.
> However, the second needs to be solved as well. And in the examples of
> SQL-BOL, this is not mentioned. At least not that I'm aware of.
> There are some advantages using RMO. For example deploying a new version
> of
> the application. Or deploying an updated database schema. Please let me
> know
> if this is proven to be really usefull. If so, this would be the preferred
> method - provided that the limitations mentioned earlier are solved. And
> if
> using this method, I would prefer stored procedures or Visual Basic for
> programming this.
> Help wanted
> --
> Since I'm a newbie on these replication matters, I would like some help
> with
> this.
> Who is willing to help with examples and suggestions?
> Sofar, I've been able to:
> - Install SQL Express with network connections enabled
> - Running the Management Studio and create the data model I would like to
> use
> - Add data manually and via an ODBC connection (using a system wide DSN).
> - Run SQL select commands against the added data.
> So the infrastructure is up and running..
>
|||Hi Hilary,
Thanks for your support and feedback. I really do appreciate this!!!
> firing triggers over the network is not scalable, as there is significant
> latency involved for each trigger firing. For a batch operation it is
> painful. Your transactions will also hang for up to 20 seconds if the
> connection is down.
Mmm - sounds like we have a different understanding of the term trigger.
I would like to use triggers on the remote databases - not the central one.
As a result, triggers are not travelling across the network.
> You might want to look at SSIS (Integration Services) or perhaps even bcp
> for what you are trying to do.
I looked at SSIS. That would require
I'm not familiar with BCP. But it looks like a bulk COPY tool.
How would this help in MOVING the data?
> You might also want to upgrade from Express to Standard so you can do
> transactional replication which will do exactly what you are trying to do.
Do you mean replace Express with standaard on the remote side?
On the central side? Or both?
Keep in mind that SQL Express on the remote sites needs to run on
Workstations.
Ans therefor requires a light footprint.
Will
|||Answers inline.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Will" <Will@.discussions.microsoft.com> wrote in message
news:E6201678-BFD9-4290-ABAC-1D7A5F6E1962@.microsoft.com...
> Hi Hilary,
> Thanks for your support and feedback. I really do appreciate this!!!
>
> Mmm - sounds like we have a different understanding of the term trigger.
> I would like to use triggers on the remote databases - not the central
> one.
> As a result, triggers are not travelling across the network.
>
> I looked at SSIS. That would require
> I'm not familiar with BCP. But it looks like a bulk COPY tool.
> How would this help in MOVING the data?
>
it doesn't move data, it copies it row by row. I know of no technology
which actually moves that, all copy it and you have to delete it yourself on
the source.
> Do you mean replace Express with standaard on the remote side?
> On the central side? Or both?
> Keep in mind that SQL Express on the remote sites needs to run on
> Workstations.
> Ans therefor requires a light footprint.
In retrospect you will probably need a code solution. Put a timestamp on
your tables which you can use to determine which rows have changed since the
last time you "moved" data. Anything greater than the timestamp the last
time you polled must be moved.
You will probably be able to use any version of SQL Server for this.
> --
> Will
>
Tuesday, March 20, 2012
Replication and permisions
Hi, I have some troubles with replication and permissions. I have two SQL
Servers on different phisical locations. Both servers establish your
comunication through a VPN. But replication don't works due permissions
problems between both servers (this happens on Windows and SQL. I think that
the solution is to deploy a DC on a server, and make member of that domain
to other server and clients. I'm confused and it's critical.
Sorry my english, I'll wait for some suggestions-
Thanks
Hernn Paggi
Argentina
What is the exact error message?
Replication will work between servers on different, untrusted domains so there is probably no need to set up additional domains.
Nick
"Hernán Paggi" wrote:
> Hi, I have some troubles with replication and permissions. I have two SQL
> Servers on different phisical locations. Both servers establish your
> comunication through a VPN. But replication don't works due permissions
> problems between both servers (this happens on Windows and SQL. I think that
> the solution is to deploy a DC on a server, and make member of that domain
> to other server and clients. I'm confused and it's critical.
> Sorry my english, I'll wait for some suggestions-
> Thanks
>
> Hernán Paggi
> Argentina
>
>
Servers on different phisical locations. Both servers establish your
comunication through a VPN. But replication don't works due permissions
problems between both servers (this happens on Windows and SQL. I think that
the solution is to deploy a DC on a server, and make member of that domain
to other server and clients. I'm confused and it's critical.
Sorry my english, I'll wait for some suggestions-
Thanks
Hernn Paggi
Argentina
What is the exact error message?
Replication will work between servers on different, untrusted domains so there is probably no need to set up additional domains.
Nick
"Hernán Paggi" wrote:
> Hi, I have some troubles with replication and permissions. I have two SQL
> Servers on different phisical locations. Both servers establish your
> comunication through a VPN. But replication don't works due permissions
> problems between both servers (this happens on Windows and SQL. I think that
> the solution is to deploy a DC on a server, and make member of that domain
> to other server and clients. I'm confused and it's critical.
> Sorry my english, I'll wait for some suggestions-
> Thanks
>
> Hernán Paggi
> Argentina
>
>
Subscribe to:
Posts (Atom)