How to synch local mssql 2000 with jodo?

L

liming

Perch
Hi,

I'm creating on .NET 2.0 platform and I'm wondering how do you synch your local mssql 2000 with a remote sql 2000 on jodo?

I tried to use DTS and copy tables/views, but that doesn't seem to include store procedures that .NET 2.0 membership/roles/etc API needed.

So I end up dropping the old db, create it again, use aspnet_regsql to manually create the .NET 2.0 db, only then I upload the tables I used in my own app.

Any suggestion is appreciated. Any tutorial links will be really helpful too.

Also, I submitted a ticket to jodo because I had a permission issue realted to using "dbo"... I'm wondering if there is anything I can do to avoid submitting a ticket everytime like right now?

Thanks again.
 
DTS is the best way for you to control it, but the BEST way is for you to take a BAK file of the database, all objects owned by DBO, and upload it to your FTP root, send a ticket and we will restore the DB.

SPs typically are not updated often/much, so it is not something that you would need regularly, and you could do DTS to sync them later.

DBO, you have to request via ticket, no other way to get it at this point.
 
okay thanks stephen.

I just tested it out and seems like there are relations missing using dts for some reasons.

I'll back it up and submit a ticket later. Thanks for the tip.
 
For ongoing sync'ing, I just write my own app. It's faster to run than doing a full DTS job and you have more control. Also you can give the app to the client and let them control when they want to back up their data.

Jodo is good to provide dbo access at all. Not all hosting companies allow it on shared systems, or perform db restores for free either.
 
liming,

I have moved SQL DBs using DTS with relations and not had that issue before, is there a specific place there is an issue with relations?
 
Antic my man, thanks again. Writing your own synch process is really complicated isn't it? I dont' even know where to start with that. Got any useful links?

Stephen, first, about back up. Do i right click on db name, all task->BackUp Db->Database-Complete & give a destinateion filename? Second about my relation problem. Because the app runs fine locally. After DTS to jodo, I ran the app on jodo again, I kept getting this foreign key constraint issue even for adding data to a simple table. so I figure? mabye relation wasn't setup correctly while I did DTS. *Note. locally, all my tables are named "dbo.table1, dbo.table2" etc etc. On Jodo, some are named "dbo.*, but some other are named [mydbusername].table1, etc". I'm not sure if that mess it up. I need to double check my code again though.

In addition, after I used DTS, all tables which had a primary key (int)with Identity Specification "yes' increaement by "1", Identity seed "1" gets losted. so I had to reset them again.

BTW, i used Antic's instruction to use EM to connect to jodo

http://support.jodohost.com/showthread.php?t=6843

I keep gettign timed out. I use Mangement Studio Like Antic suggested, no problem at all. Excellent, fast and furious. The only issue I have with managment studio is that I can't seem to use "SQL 2000 diagram" which I try to use it and see if db relations is setup corretly on jodo.

Thanks again guys.
 
I've found that SQLMS has it's pitfalls. Lots of ppl, even MS MVPs are *****ing about it. :) I now still use the original EM for all my data manipulation, but just use SQLMS when changing the structure of a table (adding a new field etc) coz it's heaps faster than EM for that sort of thing.

If you're having foreign key issues, and u have a complex web of relationships, when it's going to be a hassle. You basically need to do 2 things; 1) use the "copy tables and views" option in your DTS jobs, and specify "enable identity insert"; 2) construct the DTS job so that your "topmost" tables are copied first.

It can take some time to put the DTS job together properly if it's a complex db.

Alternatively, and this might be easier, use some SQL to delete ALL the relationships first on the destination db, then simply copy all the data across in any table order (but also with "enable identity insert" on) and then recreate the relationships again using some more SQL. This isn't good for a *live* db, just for initially setting one up.
 
Thanks Antic. Looks like backing it all up might be just easier.

Good suggestion on deleteing relationships first and then copy it over. I'll really have to think about this from now on. First time synching 2000 with a remote server.. boy.. what a hassel.
 
I'd definitely recommend the separate-app solution tho, if it's client data. Then u just double-click the EXE to update, and also u can give it to the client and make it their responsibility to do backups.
 
Antic, sorry for asking so many question cuz it seems like you know everythign that I haven't thought about yet.

For sepearate app solution, do you use sql server 2000 to genreate the database sql? I just took a look at it, it has a "Gerneate SQL script" option.... and then setup a visual studio database project to execute these sql? I haven't done anything like that before, but this idea is brilliant. I really need to get into this.

also, how do i back up the db into *.bak for jodo to upload? thanks man. really really appreicate it.
 
also, how do i back up the db into *.bak for jodo to upload? thanks man. really really appreicate it.


>The right click, all tasks, backup method works.

As for the table names dbo and user, it would kill some items, it needs to all be one OR the other to work.
 
To create a BAK file, select the database in EM, then click the "Tools > Backup Database" menu option. Zip it up before uploading, it'll be heaps smaller.

Ok, attached is a template project I've made from the code I wrote to backup my databases. It's pretty basic. If you know VB.NET, you won't have any trouble understanding what to edit to suit your database. There's only 2 places u need to change, and I've commented them for you.

Hopefully I've taken out all my own logins and passwords. :) If you see any, let me know!

Unzip into a folder of your choice, making sure you keep the zip's subfolder structure.

Hope that helps.
 

Attachments

  • DBUpdate.zip
    34.4 KB · Views: 50
antic said:
No-one wants to use my appy... *sniff* :)
Click to expand...

Now, Now, antic...don't be sad lil' buckaroo...I've downloaded your app and as soon as I've had the chance to take a gander at it, I'll give it a whirl...patience, young grasshopper...:smokin:
 
When you can snatch the appy from my hand, you will be ready to leave the temple...lol
kungfu.gif
 
The counter is not working, as I did download and look at it, so don't depend on that :D
 
been tight down with my job, forgot to check back. THANK YOU antic. At least, I can have something to base off my work with now Great work bro. Thanks for sharing.
 
You must log in or register to reply here.
Back
Top