May 22, 2012 at 1:13 pm
I am looking into creating an HR database for an organization with a central admin office and remote locations. According to my supervisor, they need to keep copies of employee records at the remote location for the employees assigned to that location. (Of course that pertains to physical records on paper...)
The managers at the locations will be able to view/enter/edit the data.
Setting aside the question "if it's electronic do they need a copy?" is there a way to automate having a remote copy of a subset of the database at several locations and keep changes synched between them over the internet? Or does it need to be done with code? Or can it be done even with code?
We have 2008 R2 Web edition.
Any advice or ideas to point me in the right direction would be helpful (quit? :-D)
May 22, 2012 at 1:20 pm
I would take a look at Merge Replication.
Jared
CE - Microsoft
May 22, 2012 at 1:36 pm
We have 2008 R2 Web edition
This implies that the application is web-based. Does each remote site have their own web server and database server or do they all hit a central server?
May 22, 2012 at 1:48 pm
We have 2008 R2 Web edition
This implies that the application is web-based. Does each remote site have their own web server and database server or do they all hit a central server?
Actually someone else ordered the web edition before I arrived.
The implementation has not been decided. I was thinking a web application with everyone hitting the central server, until she threw in this monkey wrench...
I would take a look at Merge Replication.
Will that work with the subscribers running SQL Server Express? Our resources are sort of limited...
(Still working through the documentation on this)
(How does the quoting thing work to get the names of people who posted them?)
May 22, 2012 at 1:51 pm
timwell (5/22/2012)
We have 2008 R2 Web edition
This implies that the application is web-based. Does each remote site have their own web server and database server or do they all hit a central server?
Actually someone else ordered the web edition before I arrived.
The implementation has not been decided. I was thinking a web application with everyone hitting the central server, until she threw in this monkey wrench...
I would take a look at Merge Replication.
Will that work with the subscribers running SQL Server Express? Our resources are sort of limited...
(Still working through the documentation on this)
(How does the quoting thing work to get the names of people who posted them?)
So each site will have a web-server and a database and this remote database will need to stay insync with the database at the central office.
May 22, 2012 at 1:54 pm
Lynn Pettis (5/22/2012)
timwell (5/22/2012)
We have 2008 R2 Web edition
This implies that the application is web-based. Does each remote site have their own web server and database server or do they all hit a central server?
Actually someone else ordered the web edition before I arrived.
The implementation has not been decided. I was thinking a web application with everyone hitting the central server, until she threw in this monkey wrench...
I would take a look at Merge Replication.
Will that work with the subscribers running SQL Server Express? Our resources are sort of limited...
(Still working through the documentation on this)
(How does the quoting thing work to get the names of people who posted them?)
So each site will have a web-server and a database and this remote database will need to stay insync with the database at the central office.
Lynn, is this a question or a statement? 🙂 To the OP, as long as the subscribers are <= publisher version, you should be okay. Also, you can filter the data so that each office only has access to their own data: http://msdn.microsoft.com/en-us/library/ms151318.aspx
Jared
CE - Microsoft
May 22, 2012 at 1:57 pm
So each site will have a web-server and a database and this remote database will need to stay insync with the database at the central office.
According to the Merge Replication scenario.
I am looking for suggestions. Maybe it sends XML back and forth and a C# application handles the synchronizing...
May 22, 2012 at 1:57 pm
SQLKnowItAll (5/22/2012)
Lynn Pettis (5/22/2012)
timwell (5/22/2012)
We have 2008 R2 Web edition
This implies that the application is web-based. Does each remote site have their own web server and database server or do they all hit a central server?
Actually someone else ordered the web edition before I arrived.
The implementation has not been decided. I was thinking a web application with everyone hitting the central server, until she threw in this monkey wrench...
I would take a look at Merge Replication.
Will that work with the subscribers running SQL Server Express? Our resources are sort of limited...
(Still working through the documentation on this)
(How does the quoting thing work to get the names of people who posted them?)
So each site will have a web-server and a database and this remote database will need to stay insync with the database at the central office.
Lynn, is this a question or a statement? 🙂 To the OP, as long as the subscribers are <= publisher version, you should be okay.
Both, I just want to understand the environment. I agree, it can easily be done with merge replication (not having worked with it I'd have to verify that Express Edition can be a subscriber (which I think it can without looking it up)).
Just seems like a lot of extra work for a web-based application.
May 22, 2012 at 2:03 pm
I suppose it will depend if you want the locations to be independent of each other. For example, if your web application is in need of maintenance and you need to do database maintenance... All offices are SOL. If you need the data to be instantaneously replicated, then merge replication is probably also not a good fit. Merger replication can be set to sync at intervals, and since the individual sites don't need access to other sites I thought merge would be a good solution to give each office its independence. Then you can schedule a sync several times a day, once a day, every other day, etc.
Jared
CE - Microsoft
May 22, 2012 at 2:15 pm
I suppose it will depend if you want the locations to be independent of each other. For example, if your web application is in need of maintenance and you need to do database maintenance... All offices are SOL. If you need the data to be instantaneously replicated, then merge replication is probably also not a good fit. Merger replication can be set to sync at intervals, and since the individual sites don't need access to other sites I thought merge would be a good solution to give each office its independence. Then you can schedule a sync several times a day, once a day, every other day, etc.
Since each site has their own employees it's probably not a problem to sync periodically.
I was thinking maybe it does a sync back to the main server when they hit enter, or every hour or so otherwise.
Just seems like a lot of extra work for a web-based application.
Do you mean doing it with merge replication?
I am more of a developer than a DBA so maybe I'm getting deeper in this than necessary...
May 22, 2012 at 2:16 pm
2008 R2 Web edition
Unfortunately, this edition of SQL Server only supports being a subscriber to replication.
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
May 22, 2012 at 2:21 pm
timwell (5/22/2012)
I suppose it will depend if you want the locations to be independent of each other. For example, if your web application is in need of maintenance and you need to do database maintenance... All offices are SOL. If you need the data to be instantaneously replicated, then merge replication is probably also not a good fit. Merger replication can be set to sync at intervals, and since the individual sites don't need access to other sites I thought merge would be a good solution to give each office its independence. Then you can schedule a sync several times a day, once a day, every other day, etc.
Since each site has their own employees it's probably not a problem to sync periodically.
I was thinking maybe it does a sync back to the main server when they hit enter, or every hour or so otherwise.
Just seems like a lot of extra work for a web-based application.
Do you mean doing it with merge replication?
I am more of a developer than a DBA so maybe I'm getting deeper in this than necessary...
I think Lynn is saying that developing and deploying an application to each site versus a web app is much more resource intensive. I think in order to give you better ideas, we need to know what the plan is for the application, if there is one. i.e. People are going to enter data and get reports via a web app or a local app? Even as granular as an intranet app versus local. Will the central office also be adding data, and if so, do the satellite offices ever need access to all of the data? Does the application need to report on all data, but only allow modifications and details for the specific site? It really all depends on what you need the app to do.
If each site does not need access to all of the data and needs to maintain its own database for whatever the reason, I would not even do merge replication. I would simply have each site be a publisher using transactional replication to the centralized database for reporting. That assumes that no modifications will be made to that central database that need to be replicated to the other sites.
Jared
CE - Microsoft
May 22, 2012 at 2:45 pm
Lynn Pettis (5/22/2012)
2008 R2 Web edition
Unfortunately, this edition of SQL Server only supports being a subscriber to replication.
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
OK. That won't help... 🙁
SQLKnowItAll (5/22/2012)
I think in order to give you better ideas, we need to know what the plan is for the application, if there is one.
The plan is an electronic HR database to have a database to replace or augment the paper files. The managers at the remote locations will need to access their employees and should be able to submit changes and additions to the information.
How that happens is still being decided. We have SQL Server 2008 R2 and Visual Studio 2010 and the server is Windows Server 2008 R2 Enterprise.
SQLKnowItAll (5/22/2012)
I would simply have each site be a publisher using transactional replication to the centralized database for reporting. That assumes that no modifications will be made to that central database that need to be replicated to the other sites.
According to the link sent by Lynn, transactional replication is also not an option either unless we can get the Standard (or higher) edition for the admin office all the sites (about 10-15).
So, any other ideas? XML and C#? :ermm:
May 22, 2012 at 2:49 pm
timwell (5/22/2012)
Lynn Pettis (5/22/2012)
2008 R2 Web edition
Unfortunately, this edition of SQL Server only supports being a subscriber to replication.
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
OK. That won't help... 🙁
SQLKnowItAll (5/22/2012)
I think in order to give you better ideas, we need to know what the plan is for the application, if there is one.The plan is an electronic HR database to have a database to replace or augment the paper files. The managers at the remote locations will need to access their employees and should be able to submit changes and additions to the information.
How that happens is still being decided. We have SQL Server 2008 R2 and Visual Studio 2010 and the server is Windows Server 2008 R2 Enterprise.
SQLKnowItAll (5/22/2012)
I would simply have each site be a publisher using transactional replication to the centralized database for reporting. That assumes that no modifications will be made to that central database that need to be replicated to the other sites.According to the link sent by Lynn, transactional replication is also not an option either unless we can get the Standard (or higher) edition for the admin office all the sites (about 10-15).
So, any other ideas? XML and C#? :ermm:
I say develop a web application (if that is allowable by your business rules) that uses 1 database. All required employees will have their own credentials with the proper permissions set for them. Simple!
Jared
CE - Microsoft
May 22, 2012 at 2:57 pm
SQLKnowItAll (5/22/2012)
I say develop a web application (if that is allowable by your business rules) that uses 1 database. All required employees will have their own credentials with the proper permissions set for them. Simple!
That's what I had in mind at the beginning, until my supervisor said they need to have a copy of the records at each site. If I can verify that that means "need to have access to the records" then maybe it IS that simple.
That requirement is from CARF (www.carf.org), so I guess I will dig into that some more.
Thanks Lynn and Jared for your responses.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply