| Author |
Topic |
|
vimukthilk
Starting Member
4 Posts |
 Posted - 2012-02-27 : 08:32:56
|
| My setupI have 2 machines (say Server1 and Server2) on which I run separate instances of MS SQL Server. The MDF file and associated log file for my database called Disso is saved on a dropbox folder. The idea is that I can then access the same database from both servers and any changes made through one is synchronised. I can successfully access the database from both machines.My issueI have tried adding a record to a table in the database through Server1 (using Management Studio), but when I refresh the database on Server2, the change is not reflected. The reverse doesn't work either.What am I missing here, please?Many thanks. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 08:50:21
|
| don't do that!the mdf and ldf are not stupid files. I very much doubt you could successfully run two instances of sql server pointing at the same set of files.The usual way to do as you describe is through merge replication. (which I understand is not without it's problems)However, why would you need to have 2 instances of sql server running? why not simply connect to the one instance from both your places. Much easier.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
vimukthilk
Starting Member
4 Posts |
Posted - 2012-02-27 : 09:37:20
|
| Thank you Charlie and GilaMonster very much for the prompt replies.The reasons I was trying to do this are:a) I'm developing an application which requires a database for University dissertation.b) I'm working on PC when at home and laptop when at Uni.c) Didn't want to leave my home machine on when not at home.I don't need both servers running concurrently. Would it work then; my guess is you're going to say no. Out of interest, why is what I'm trying to do not possible?Thank you very much again! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-27 : 09:43:43
|
| Take a backup on the one machine. Restore on the other when you want to use the second. Repeat to go back. Far easier, far less likely to cause odd problems. Put the backup into dropbox if you likeIt won't work for a number of reasons.- The files are locked open and exclusive by SQL Server and SQL requires full access, so no other processes may touch them- The database files aren't treated like other files, like Word or the like, they're not guaranteed to be in a consistent state at any time other than when SQL is shut down or the database is detached--Gail ShawSQL Server MVP |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 09:44:58
|
| Can your problem be solved simply by:1) Set db to simple recovery mode -- doesn't sound like you need point in time recovery. Google if you don't understand this.2) Backup the db when you are about to go home from uni.3) put the backup file onto your dropbox folder4) go home -- restore the backup on your laptop5) do work. Back up db to drop box folder (with a new name maybe?)6) restore on uni machine when you get to uni.if you back up the db you can keep older 'versions' to refer to.Are you using any kind of source control (SVN, CVS....)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 09:45:30
|
 Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
vimukthilk
Starting Member
4 Posts |
Posted - 2012-02-27 : 09:46:52
|
quote:
Take a backup on the one machine.
When you say backup, is there a specific process I need to follow for that or do you mean a simple copy-paste?Many thanks. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 09:48:23
|
| ms howto.http://msdn.microsoft.com/en-us/library/ms187510.aspxyou can do it with sql commands or in management studio. But yes, we mean a backup process rather than copy-paste.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
vimukthilk
Starting Member
4 Posts |
Posted - 2012-02-27 : 09:51:33
|
| Thanks Charlie.I will follow the tutorial, now that I am aware what can/cannot be done.To both of you : much gratitude. I was considering pulling my hair out!Best.Vim. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-27 : 10:18:05
|
quote:
Originally posted by vimukthilk
When you say backup, is there a specific process I need to follow for that or do you mean a simple copy-paste?
No, not a simple copy-paste, that's the same as the drop box attempt.BACKUP DATABASE ... TO DISKit creates a backup file. Copy that any way you like and restore on the other machine.--Gail ShawSQL Server MVP |
|
|
|
|
MDF files not synchronising