Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JeffRo
Starting Member
4 Posts |
 Posted - 2015-02-06 : 11:22:24
|
| I was recently asked to create a database for a customer in a unusual way and have questions on this.The front end is all done in VB.net and uses sql 2008 tables.The customer has three parts to his database.1) Weight Scale2) Shipping3) Front Office(Invoicing etc...)There are several Weight Scale computers and several Shipping computer and only one front office computer.They want the weight scale computers to be able to run independent in the event of server failure so that they can keep working.They want the same for the shipping computers.At the same time they want the front office to be able to see all the data from production and keep track of where production is at in the process.I designed the weight scale app and setup its tables and it prints a label after each product is weighed and I have designed the shipping app which allows then to select a customer and scan a barcode to assign to that customers order.I have designed other apps in the past that all the data is on the server so they can share data.What they are asking is for each machine to run separate and also as a group but not to tranfer any of the information of the weight scale until it is complete and not to tranfer and of the information of the shipping app until it is complete.Is there a way to do linked tables easily in sql express or is this idea the customer has just not practical. I really look forward to hearing some replies. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-06 : 16:03:12
|
| Some questions:1. How long should the weight scale computers and shipping computers be able to run independently in case of a server failure? The answer will inform your strategy. e.g. if a few seconds, you can cache the data in memory and update the server when it gets back onlineif a few minutes or hours, you can cache locally (e.g. in sql express) and update the server later. The drag is that sql express does not have agent, so you'll need to schedule those updates another way (e.g. windows scheduler)2. How long can the front office continue without access to the data in the other departments?3. What is your server strategy? All eggs in one basket? Failover cluster? Always ON? Mirroring? |
 |
|
|
JeffRo
Starting Member
4 Posts |
Posted - 2015-02-06 : 16:25:17
|
| Is there not a way of doing linked tables in sql express or as they call it replicate (Distributor and Subscriber) or do you have to have the full blown version.The time that the scale computers and shipping computer could be disconnected would depend on the situation from server going down to power outage.I suggested just putting the tables on the server as you would normally do but they want a way to run independent in the event of a failure.The front office would be directly connected to the tables on the server, so if the server goes down they are out of luck. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-06 : 20:25:49
|
| Sql express can subscribe but not publish. You need std for that. See https://msdn.microsoft.com/en-us/library/cc645993.aspxI guess what I was asking was, "What's the longest server outage you need to prepare for where the shipping ans weighing will run independently?" |
|
|
|
JeffRo
Starting Member
4 Posts |
Posted - 2015-02-11 : 14:50:55
|
| I have no real definition of time as it can vary all over the board.If their VPN goes down it could be not just hours but days depending on how long it takes the ISP to fix it.If a router goes out then we are talking whatever time it takes to get another router so it varies all over the board. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-11 : 15:31:26
|
| Then,since you cannot make Sql Express a publisher for replication, you need to set up some jobs on the front office server to pull updates from the shipping/weighing depts when the front office comes back online after an outage. |
|
|
|
JeffRo
Starting Member
4 Posts |
Posted - 2015-02-25 : 16:28:34
|
| I found a way to make it work for this software. So I felt it was only fair to share it so other can benefit from it.First I set up a linked server, nothing hard about that.Then after some trial and error I put together a query that would transfer the data from from one table on the local machine to the same table on the server.Here is an example of the query.insert into [Server name].[database].dbo.Table Name(Field1,Field2,Field3,Field4,Field4,Field5)select Field1,Field2,Field3,Field4,Field4,Field5from Database name.dbo.Table Namewith (nolock)where Field1 not in ( select Field1 from Server Name.Database.dbo.Table Name )This seems to be working quite well and who knows I might be able to finish the project. |
|
|
|
|
|
|
|
|
Database/Tables