February 16, 2011 at 5:37 pm
Sorry for posting a noob question like this. Im pretty new to SQL and I am trying to work on an ASP.NET web front end for a personal project that I am doing. There are 2 databases, 1 local and 1 remote (hosted online). The local db (db1) is running 2008 SQL and contains around 50 tables. The online db (deonline) is running 2005 and contains only 5 tables. Both sets of the tables are identical. I just peeled out the few tables I needed for the online part of this website.
What I am doing is this. I have some local apps on my network that update the local database. I have been running the SSIS Data Export wizard and sending the data out to the hosted database (pretty much dropping and recreating the tables each time). I wasnt thinking too much the other day, and started making changes to the hosted database. Now both tables have data changes in them that need to get synched back together. Currently there are around 100 records that need to be updated out of the 1000 out there.
So Im trying to find a to do the following:
1.) Allow me to synch the data from the local database to the hosted one and update the data. Now on this, there will be times where new records will be inserted, some records may be deleted, but most of the time it will be the records getting updated. Usually when it gets updated it will be 2 or 3 fields that will be updated and nothing else.
2.) I will be allowing people using the web front end to make changes via webforms so I will need to carry those changes over to the local database.
Here is the Images table that I am using. (The actual table has like 70 fields but im only using like 30 and of those these are the only ones that are being used online)
Images
[OrderID] [char](31) NOT NULL,
[Roll] [char](31) NOT NULL,
[Frame] [char](31) NOT NULL,
[Inspect] [int] NULL,
[Rejected] [int] NULL,
[FileType] [char](6) NULL,
[Path] [char](255) NULL,
[ImageSource] [char](20) NULL,
[OnlinePath] [char](255) NULL,
[CameraType] [varchar](50) NULL
These Images tables are identical except for the current data I put in them.
If anyone has suggestions, examples, or even a generic idea on how to proceed I would be so grateful. I could update the local database manually but in the end if someone makes a change online I have to get that to sync up locally again.
Im using Godaddy for the hosted server so I know there are limitations, but I am able to do SSIS packages right now.
Ive been working on this for the last several days and spent part of today looking around this site. Ive seen lots of good help and ideas but nothing that exactly fits my problem, or maybe Im just not understanding it.
Thanks to all who read this and even more so, to those who reply.
BK
February 16, 2011 at 8:15 pm
Please let me understand the following.
From where you want to update. Online database to Local database.
Why did not you want to use replication?
Give us more scenarios to give better solution.
February 16, 2011 at 9:18 pm
Im going to have to update from both ways.
Here is the workflow as it currently is:
1.) Pictures are taken.
2.) I upload them into our software which in turn gets added to the local database.
3.) The data needs to get pushed up to the online hosted database so that the other people have access to look at the work.
4.) As the pictures are edited and such web thumbnails are created for the pictures that are not rejected and they are ftp'ed to the website.
5.) The image path for the web thumbnail is then updated into the images table which in turn would be updated on the hosted db. This would allow the preview field to show the images as its pointed to the onlinepath field in the data for the online app.
Now at the same time there are other tables too, 4 others, which a staff member could go in and update the phone number, email address, etc of the customer. So these changes by the other people would have to be sent down to the local database as well.
I did try the replication wizard tonight. For some reason it seems that when I tried to configure the GoDaddy side Management Studio would roll over and die on me. But my cable has been acting funny so I cant tell whos issue it is right now.
I could point all the pcs that are local at the godaddy db but that means if you wanted to work on any images you must be online to do so and thats a problem as one of the main editing systems is a laptop.
At this point in time Ive just got a small piece of the ASP.NET site up and running. In fact its just a Image by Order datagrid page I created with the fields I mentioned before and a dynamic field for the ImagePreviews. I may be going about this completely wrong too...Im just trying to find the best practice that will fit what I need it to do.
I apologize if I dont have the right terminology for all of this. I havnt done much programming in years and just decided to start out by relearning SQL (started out back in 2001) and trying to learn ASP.NET
Thanks for your help!
February 16, 2011 at 9:31 pm
Can you check out Web Synchronization for Merge Replication and see if that will work?
If not let me know as I have a really clunky idea that might work. Ugly though...
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 2, 2011 at 8:04 am
Sorry for the delay on getting back to this.
I ended up researching the replication idea that was mentioned. So as I was trying to set that up my management studio would keep crashing when i tried to setup the replication wizard on the godaddy side of things. So I opened a case with godaddy.
1st reply back told me to make sure i was using SQL Server Management Studio...which I was and had mentioned that in the first line of the email I had sent to them.
Replied with the info stating that I was using the SSMS. I got another reply back stating that I needed to recreate my databases to use dynamic access. Hmmm in order to use SSMS I had to do that anyway....but ok. So they said to backup the database, then delete and recreate it on their server. I then restored the database like they said.
And....same problem!
My problem on their side is this: When i go to run the replication wizard it acts as though it times out. I know that there are tons of dbs out there cause when i expand the databases view to find mine i can see like a thousand more dbs.
I called them about it that night. I spoke to a nice gentlemen who was very understanding and willing to help. He was with me on the phone for about 45 minutes and had even called another tech in their network dept. After learning that I was using SSMS 2008 R2 they believed that was the problem. Due to different security and stuff they dont support 2008 R2 SSMS only 2005 at this time. So they suggested that I use the 2005 SSMS to set up the replication wizard. They were very surprised when i told them that 2008 R2 works great for everything im doing but the replication wizard.
So later that night I installed the 2005 SSMS and low and behold.....the problem with the timeout still exists... So ive got to continue on with the case and see what happens...
Thanks for all of the help so far!
March 2, 2011 at 9:02 am
Thanks for the update. I'm definitely looking forward to hearing the conclusion here. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 3, 2011 at 8:42 am
You may be running into networking issues since replication has specific requirements.
http://support.microsoft.com/kb/164667
'Synching' data is often problematic although the available tools have come a long way.
Is there any way you can consolidate the data at your host? I know I am not responding to your initial question but if you can simplify the architecture you may have a more pleasant experience. 🙂
March 3, 2011 at 9:00 am
I have a feeling that you may be right. Even though GoDaddy says its possible I still have some doubt.
My main issue is that if I consolidate onto their servers, everyone running the apps on their machines in this group would have to have internet access to do any kind of processing and there are times when it could be problematic.
Right now the database is on my image processing pc with just the 4 tables on the godaddy side. By moving the rest of the database up to them I then would have over 75 tables up there. It would just slow things down thats all, but thats what may happen in the end, or I may just have to do a read only setup where I export the data from the main image processing pc nightly. I would just be the one making all of the changes if there was an issue instead of splitting the load up between 4 of us.
Thanks!
March 3, 2011 at 11:25 pm
Couple of things:
1. With the SSMS crashing on the GoDaddy end, try deleting your shell folder underneath your profile for SSMS (C:\Documents and Settings\<user profile>\Application Data\Microsoft\Microsoft SQL Server\90\Tools . It will recreate itself the next time you open up SSMS. Your connection info will need to be re-input, and if you are concerned that you have too many settings that you really do not want to lose from there, then just back the folder up to another spot on your C:\DRIVE or another drive, and rename it (but then delete it from your profile path on your PC with SSMS installed).
2. If these columns on the hosted tables are of the data type IMAGE or VARBINARY, copy the image files over to the host server, and then do something like this to put them in.
INSERT INTO 'Table Name'('Column Name')
SELECT * FROM
OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)
Best wishes and lets us know how this works out.
March 4, 2011 at 8:44 am
I'm also new at SQL, so sorry to jump in, but wouldn't a MERGE query (run from the 2008 side) work?
March 4, 2011 at 9:08 am
A Merge clause was suggested to me as well. I just dont know exactly how that might work with changes being done on both sets of tables. I did try playing with a SELECT INTO and made staging tables on each end. Then a merge might work....but its been many many years since I did SQL and im trying to get back into this and its just totally escaping me. And of course at the same time im learning ASP.net to write the portal too...so the mind is already fried from all of that...and this is 2nd job type of deal so i can only work on it after I work my daily 8-5 job.
My concern is this:
If a job gets input with a record and the CameraType field is set to Canon20D and then its updated on the Godaddy side. Then a webuser changes the CameraType field to be RebelT2i instead and then makes a change to the SubjectName field. Those changes can be made from both sides and so both sides have to be in sync at all times (whether auto or manual i dont care). Since most of the processing is done on my end (ImageUpload, Job Creation, Web Thumbnails, and then posted to the portal) and the online part is just job tracking, job editing, and minor stuff like that. But if a change is made on the web side of things that means that there was a change in the order, or i fat-fingered something along the way.
Im just afraid of losing data on either end.
Maybe im making this way too complicated too...i dont know
I really do appreciate all of the advice that Ive recieved.
March 4, 2011 at 9:42 am
Quoting from Itzik Ben-Gan's T-SQL Fundamentals, Merge "allows you to modify data, applying different actions (INSERT, UPDATE, DELETE) based on conditional logic."
You have a source table and a target table, then up to 3 conditions: when matched, when not matched, when not matched by source. You might have to run it twice, once with the local table as target, modifying it with data from the hosted table (the source), and then a second DML statement reversing the target and source (and probably different logic for the possible conditions).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply