Solved

Duplicate Sql Server DB

Posted on 2016-08-03
7
86 Views
Last Modified: 2016-08-16
We time races and, at present, we place a copy of the race db in dropbox the morning of the race and then each crew manually pulls it down and times their race.  The changes made locally are uploaded to the server via our software.  What i want to know is:
1) Is there a better way to pull all the data down from the db, either completely or just the data pertinent to a race someone is timing?
2) Is there a way to pull the data down from the server and not lose the unqiue ids for each table?
3) If we are NOT able to do #2, how do we get the changes back to the server given that the unique ids of many of the tables have been changed?

Pulling it down part and parcel as we are doing now is a pain but the system works really well once we have done so...except that there are some "new" records (ie: new race participants) that can't be added because their local unique id will potentially be different than the unique id created on the server.

I know I am basically talking about replication but I have not had success with that process in the past.
0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Assisted Solution

by:Jonathan Kelly
Jonathan Kelly earned 125 total points
ID: 41741376
If you can you add a site id and use a composit key or perhaps use GUIDs you may be able to use a single db.

Make your server accessible over the web and have your, client software either interact directly or pull down, edit and push back the data.

Can you give more detail on your setup ?
0
 
LVL 13

Assisted Solution

by:Dustin Saunders
Dustin Saunders earned 250 total points
ID: 41741404
Hard to advise of a better solution with no knowledge of the app.  If you have it running on a server, can you just publish it as a remote app and have users access it that way (or through remote desktop?)  Then it never leaves the network, if it's not your own software this might be your ideal.

Hard to advise on how to change the database, though, without knowing what the conflict is, i.e. is the conflict with identity columns?  If so, you need to insert them without the identity to get a new one but then also update any tables that reference that old primary key.
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 125 total points
ID: 41741548
Can you please post your Sql server version, edition, sp?
Size of DB?


--

it looks like you may like SQl Server  "Merge Replication"

check these articles and let us know if you like this idea

"Merge Replication"
https://msdn.microsoft.com/en-us/library/ms152746.aspx


How Merge Replication Works
https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx

-How to Create Merge Replication in SQL Server
https://www.youtube.com/watch?v=HCMpD6wxCIU
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Bob Schneider
ID: 41743575
On the server we use sql server 2012 Web.  On our local machines we use 2012 express.  I will look at the merge replication.  If I were to refine the question, here is what I really need:  I need to be able to "pull down" a copy of the db on the server to each local machine with all relationships intact.  If I could do that I would be happy, whether it be via a simple "back-up" process, replication, or whatever.  The problem is, I would like to have a simple way to do that...ie: a script that can be embedded in a desktop icon to make it easy for folks with no database experience.

Here is what I use the database for:
We have a race timing service (www.gopherstateevents.com) that keeps it's data on a web server.  However when we go time an event, sometimes up to five events simultaneously, we place a copy of the database in dropbox (ldf and mdf files) and the timers pull it down to time their race.

As each crew times the race, the pertinent data (ie: race results) are uploaded to the web using a connection string in a vb6 application (sorry...been using it for almost 20 years and it works well..plus I can update  it easily) so no need exists to manually put the data back on the server after the fact.

What I want to do is take the "dropbox" step out and get a complete version of the data onto a local machine prior to the race.  I would like to do this without opening SQL Server Mgmt Studio if possible since many of our timers have no experience with this utility.
0
 
LVL 13

Accepted Solution

by:
Dustin Saunders earned 250 total points
ID: 41743671
SO this is an in-house app where you have control over the code?  How many tables contain information?  Because it sounds like the problem is you have identity columns in each separate local DB that conflict, is that correct?  

If that's the case, then you can code a 'briefcase' where you can have the local version mark where the database started and then when you go to re-import the 'briefcase' data you can have code insert the data with fixed identity fields.

But to echo a previous comment, is a remote app/desktop solution not viable? If everyone can run your app remote, then there isn't any need to move the data around and try and re-merge it.
0
 

Author Comment

by:Bob Schneider
ID: 41744291
I am moving towards running it solely off of the web server db, either with my existing vb6 app or simply using a web interface.  I just haven't had the time to get that done.

Here is what I am hearing as my options/non-options until I can simply move everything to the web:

1) Use Merge Replication.  I am leery of that due to my own limitations...I don't want to screw it up.  Maybe someone on this site would do this for me for a fee?  Is this a viable option given that I am using express locally?

2) Code a "briefcase".  I would need more information on that.

3) There is no way to run a back-up to the local machine that would serve as a functional database that I could use locally.  This would replace what I am doing now (placing the database in dropbox and then manually pulling down on each local machine).
0
 

Author Comment

by:Bob Schneider
ID: 41757828
I have decided to run our timing process off of the web server.  I will post another question about that process. Thanks for your help everyone!
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question