April 27, 2011 at 12:47 pm
We have a customer which connects to our servers through Citrix. However, they have a major concern that the facilities in which they want to run our applications, at many times have connection issues. So, they want to run our applications locally (on specific PC's). Data from the host system would feed to the local machines periodically. If the connection is lost, the PC still have enough data to conduct it's business until the connection is restored. At that time, the data changes from the PC are reflected back to the host system and visa versa.
I guess we would be considering some type of replication (this is all new to me). This has many questions but here are the main ones.
1. If we replicate, then they need a copy of SQL Server on each PC. We are talking about 30 sites which would be very expensive due to licensing. Also, other support costs.
2. Is it better to always run replication or only in the event that the connection was lost?
3. How does the local system get in synch with the hosted system?
Just looking for a better/less expensive solution. I can provide a few more details if needed.
April 27, 2011 at 1:12 pm
Rog Saber (4/27/2011)
We have a customer which connects to our servers through Citrix. However, they have a major concern that the facilities in which they want to run our applications, at many times have connection issues. So, they want to run our applications locally (on specific PC's). Data from the host system would feed to the local machines periodically. If the connection is lost, the PC still have enough data to conduct it's business until the connection is restored. At that time, the data changes from the PC are reflected back to the host system and visa versa.I guess we would be considering some type of replication (this is all new to me). This has many questions but here are the main ones.
1. If we replicate, then they need a copy of SQL Server on each PC. We are talking about 30 sites which would be very expensive due to licensing. Also, other support costs.
2. Is it better to always run replication or only in the event that the connection was lost?
3. How does the local system get in synch with the hosted system?
Just looking for a better/less expensive solution. I can provide a few more details if needed.
How big is the database? If it is small enough, you might get away with using SQL Server Express, though I'd need to check the licensing to make sure that was legal. The "other support costs" you're not going to get away from.
You'd likely be best served by running the same way all the time. That means fewer scenarios for you to test. I'd say if you can run locally and synch on a regular schedule that would be your best method. How frequently do you see needing to synch?
What kind of replication are you thinking of? Log shipping? Messaging? XML files? Direct queries? SSIS packages?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 1:19 pm
The database is not huge but it's not small either. However, to run their application we're probably only talking about 30 tables which is not big at all.
As far as the replication process, I don't know which method - that one of the reasons I posted here.
Synching would occur maybe hourly.
April 27, 2011 at 1:32 pm
Rog Saber (4/27/2011)
The database is not huge but it's not small either. However, to run their application we're probably only talking about 30 tables which is not big at all.As far as the replication process, I don't know which method - that one of the reasons I posted here.
Synching would occur maybe hourly.
If the 30 tables they'd have would amount to less than 2 gig you should look into SQL Server Express and see if the licensing allows you to use it for free in that kind of situation. Otherwise you're stuck with full SQL Server for each location.
How you synch the databases comes down to how whomever is programming them feels most confident and comfortable. If you don't know any of the particular technologies and aren't sure that you'll be able to have connectivity when you plan on synching, I'd suggest XML files. You can write SSIS packages to import the data and do your merges on both sides. If you don't have connectivity, the XML files will still be generated and will sit on the servers awating a successful transfer.
The main things you have to watch out for here are
Making sure if you have a backlog of files, they're imported in the correct order or you could have older data overwriting newer data.
Deciding which data takes precidence if your Main server and the Satellite servers change the same data in the same time period.
Having the bandwidth to send/receive all the files at the same time. If you don't, you may want to offset each server's send/receive by a few minutes.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 2:40 pm
Stefan Krzywicki (4/27/2011)
If the 30 tables they'd have would amount to less than 2 gig you should look into SQL Server Express and see if the licensing allows you to use it for free in that kind of situation.
Actually, the 2G limit was on old versions like MSDE from back in the day. SQL Server Express allows for a 4GB database size for 2008 and 10GB in size for 2008R2. http://www.microsoft.com/sqlserver/2008/en/us/express.aspx
In addition, technologies like Mirroring allow for the use of Express for the Witness and a FULL EDITION for the mirror, but without needing an additional license. This only applies if the 2nd full edition server is 'passive' (no queries executed against it), like a failover mirror, failover clustering, log shipping target, etc. http://www.microsoft.com/sqlserver/2005/en/us/special-considerations.aspx.
As another poor-mans option for small databases, you may use Red Gate's SQL Data Compare programmers API to write a quick app that will sync data regularily. However, this means that the small databases are COPIED OVER THE NETWORK every hour when you compare/sync. This is why it is only good for small databases and poor men.
So this may open up a few other options to consider.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 27, 2011 at 2:54 pm
[Jim].[dba].[Murphy] (4/27/2011)
Stefan Krzywicki (4/27/2011)
If the 30 tables they'd have would amount to less than 2 gig you should look into SQL Server Express and see if the licensing allows you to use it for free in that kind of situation.Actually, the 2G limit was on old versions like MSDE from back in the day. SQL Server Express allows for a 4GB database size for 2008 and 10GB in size for 2008R2. http://www.microsoft.com/sqlserver/2008/en/us/express.aspx
In addition, technologies like Mirroring allow for the use of Express for the Witness and a FULL EDITION for the mirror, but without needing an additional license. This only applies if the 2nd full edition server is 'passive' (no queries executed against it), like a failover mirror, failover clustering, log shipping target, etc. http://www.microsoft.com/sqlserver/2005/en/us/special-considerations.aspx.
As another poor-mans option for small databases, you may use Red Gate's SQL Data Compare programmers API to write a quick app that will sync data regularily. However, this means that the small databases are COPIED OVER THE NETWORK every hour when you compare/sync. This is why it is only good for small databases and poor men.
So this may open up a few other options to consider.
Jim
Thanks Jim, sorry about listing the old size.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 3:05 pm
Rog Saber (4/27/2011)
We have a customer which connects to our servers through Citrix. However, they have a major concern that the facilities in which they want to run our applications, at many times have connection issues. So, they want to run our applications locally (on specific PC's). Data from the host system would feed to the local machines periodically. If the connection is lost, the PC still have enough data to conduct it's business until the connection is restored. At that time, the data changes from the PC are reflected back to the host system and visa versa.I guess we would be considering some type of replication (this is all new to me). This has many questions but here are the main ones.
1. If we replicate, then they need a copy of SQL Server on each PC. We are talking about 30 sites which would be very expensive due to licensing. Also, other support costs.
2. Is it better to always run replication or only in the event that the connection was lost?
3. How does the local system get in synch with the hosted system?
Just looking for a better/less expensive solution. I can provide a few more details if needed.
From previous posts...is your DB under 10GB?
It's not trivial to setup such an environment because of the conflict-resolution for all publications/subscriptions as well as jobs to do all the pushing and pulling but "Merge Replication" could satisfy the requirements as stated. Each client-machine can be running SQL Server Express Edition (free), the central-server would be running a fully licensed copy of SQL Server (not free) and the push and pull jobs would run on the central-server to get new data from clients to merge into the central-server, as well as push updated data out to all clients.
.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 3:07 pm
Stefan Krzywicki (4/27/2011)
Thanks Jim, sorry about listing the old size.
No sweat. Things change every year or two so it makes it hard to keep up with the latest limits. Especially when folks like you and I typically use Enterprise, Standard or Developers edition day in and day out.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 27, 2011 at 3:09 pm
How would "Merge Replication" work? Is it reliable? What is the potential for data conflict pushing and pulling?
I guess I am just wondering how well this works in the real world.
I will try and find out the size of our DB. I don't know how accurate this is or even if it is correct but I looked at the properties of our DB and it states about 40MB. However, that is without our new functionality but again it should add that much extra.
What limits would we run in to running the Express edition?
April 27, 2011 at 3:10 pm
opc.three (4/27/2011)
Rog Saber (4/27/2011)
We have a customer which connects to our servers through Citrix. However, they have a major concern that the facilities in which they want to run our applications, at many times have connection issues. So, they want to run our applications locally (on specific PC's). Data from the host system would feed to the local machines periodically. If the connection is lost, the PC still have enough data to conduct it's business until the connection is restored. At that time, the data changes from the PC are reflected back to the host system and visa versa.I guess we would be considering some type of replication (this is all new to me). This has many questions but here are the main ones.
1. If we replicate, then they need a copy of SQL Server on each PC. We are talking about 30 sites which would be very expensive due to licensing. Also, other support costs.
2. Is it better to always run replication or only in the event that the connection was lost?
3. How does the local system get in synch with the hosted system?
Just looking for a better/less expensive solution. I can provide a few more details if needed.
From previous posts...is your DB under 10GB?
It's not trivial to setup such an environment because of the conflict-resolution for all publications/subscriptions as well as jobs to do all the pushing and pulling but "Merge Replication" could satisfy the requirements as stated. Each client-machine can be running SQL Server Express Edition (free), the central-server would be running a fully licensed copy of SQL Server (not free) and the push and pull jobs would run on the central-server to get new data from clients to merge into the central-server, as well as push updated data out to all clients.
.
Good to know though, it'll make it easier to switch one of our managers off Access.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 3:13 pm
Rog Saber (4/27/2011)
How would "Merge Replication" work? Is it reliable? What is the potential for data conflict pushing and pulling.What limits would we run in to running the Express edition?
This should get you started http://www.microsoft.com/sqlserver/2008/en/us/express.aspx
and
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 3:34 pm
Also submitted for your reading pleasure...here is a high-level overview of a fictional situation Microsoft conjured to illustrate an ideal application of Merge Replication technology. The fictional company's situation is eerily similar to yours...it's like they had your company in mind 😀
http://msdn.microsoft.com/en-us/library/ms151790.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply