November 2, 2015 at 12:48 pm
Hello
I am a complete novice on SQL Server. I currently use Access and have found that I need to migrate to SQL Server. I need some help with setting it up.
Currently, I have multiple computers (home, office, laptop, etc.). On each of these computers, I have a folder which syncs via Dropbox. In this folder, I have my Access database which allows me to read/write to the Access database from any computer, and these changes are automatically synced to all the other computers via Dropbox.
I am trying to do the same thing with SQL Server but cannot figure out how to do it.
I have installed the SQL Server database in the Dropbox folder then installed Management Studio on each computer. But only the computer which did the database install can link to the the SQL Server database. The other Management Studios cannot see the database.
Is there a way to achieve what I am after or is this a limitation of SQL Server? I do not want to have to manage a network or anything. I need to keep it simple.
Can anyone help with this?
Thank you.
November 2, 2015 at 1:01 pm
I *strongly* recommend you don't try using SQL this way.
Technically the data and log files could be created in a dropbox synced folder, however SQL Server takes exclusive locks on the files while it's running, so drop box won't be able to sync until SQL's shut down, and if you have two instances running at the same time pointing at files in a drop box folder, there will be file conflicts and, worst case, a corrupt database.
Access is a file-based database system. SQL Server is not, it's a server.
What you can do, assuming the DB is small, is take a backup once you're done on one machine, put the backup into dropbox and restore the database when you start working on the other machine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2015 at 1:19 pm
Thanks for your reply Gila.
Some follow up questions:
1. The database is about 100MB. Is that considered small?
2. If I am diligent about making sure there is only 1 computer accessing the DB at a time so that there are no conflicts, in this case, will it work ok? I am not looking for the ability to simultaneously access the database as I am the convenience of having it reside in 1 location. The idea of the backups puts the burden upon me to keep track of which computer is the most recent, etc.
3. If I want to try this Dropbox solution, how would I actually do it? I have installed the DB to the Dropbox folder. Do I install SSMS to the other computers? How do I make them see the database in the Dropbox folder?
Thank you.
November 2, 2015 at 1:33 pm
biocentrism (11/2/2015)
Thanks for your reply Gila.Some follow up questions:
1. The database is about 100MB. Is that considered small?
There's no value for small. I means small enough that you can back up and restore quickly enough that it's not a major hassle.
2. If I am diligent about making sure there is only 1 computer accessing the DB at a time so that there are no conflicts, in this case, will it work ok?
Maybe. But the day you are in a rush and boot the laptop before stopping the SQL service on the desktop, you may corrupt the DB.
I strongly recommend you do not do this. It is not how SQL works. SQL is not a file-based database, it's a service that runs on the machine and provides the database access. To ensure that only one computer is accessing the database, you need to manually start and stop the SQL Server services. IT's not enough for you not to be using the DB, the service actually has to be explicitly stopped.
3. If I want to try this Dropbox solution, how would I actually do it? I have installed the DB to the Dropbox folder. Do I install SSMS to the other computers? How do I make them see the database in the Dropbox folder?
I'm not going to tell you how to do it, because it's a really bad idea. Sooner or later, it's going to screw the database up.
Again, SQL Server is NOT a file-based database. You need the SQL Server service installed on any machine you want to use a SQL database on.
You can make the backup to be a very simple process. Last thing you do on machine 1 is backup the DB and write the backup file to drop box. First thing you do on machine 2 is restore the backup to the SQL instance on machine 2. Repeat for any machines
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2015 at 2:15 pm
If your database needs to be available from more than one machine, and you don't have a server to host it, you might consider an Azure solution instead.
November 2, 2015 at 2:42 pm
Manic Star (11/2/2015)
If your database needs to be available from more than one machine, and you don't have a server to host it, you might consider an Azure solution instead.
Or some other Cloud solution. Exactly what I was thinking and a much better idea than SQL Server/Dropbox. You'd be better off staying with Access than doing this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2015 at 3:34 pm
Azure seems incredibly slow for a database of any size unless I am willing to shell out a lot of money each month. Access is too limited for what I need to do or else I would happily stay with it. Maybe I will just install it on a single machine and do all the database work on that machine.
Thanks everyone for all your advice!
November 2, 2015 at 3:36 pm
Try Azure SQL Database rather than an Azure VM.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply