Building upon my last post regarding the setup of dual development environments and how to keep them in sync, this time I want to focus on keeping the development database in sync and how I have approached it.
In my setup, I have an office PC and a home laptop, both running SQL Express as my database server. The goal: to have all changes performed from either location be reflected on both. Here's a diagram (I love pictures) of my current setup:
In reality, a database is nothing more than a file, so in theory then, if we can manage to keep that file synchronized across different machines, we should then be able to keep our database synchronized as well. With that in mind, here are the steps taken to accomplish my goal:
MACHINE 1
1. Create an account with FolderShare (www.foldershare.com), or your favorite file syncing service;
2. On machine 1, create a folder to house your database (when you create a database in SQL Express, it wants to know WHERE the actual database file should reside on your hard drive...you'll be pointing it to this directory when the time comes);
3. Create a share on the FolderShare site that points to the directory you created in step 2;
4. Fire up SQL Management Studio, connect to your local database server, and create a new database (again, being sure to make sure the location of the data and log files are the directory you created);
All done on the first machine! Straightforward stuff. Now for the other machine.
MACHINE 2
1. On machine 2, create a folder to house your database, just as you did on machine 1. You can name it anything you like, and it can live on any drive you like.
2. Using FolderShare, direct the share you created earlier to be synchronized with the folder you created on this machine;
3. Go get a cup of coffee and give FolderShare time to synchronize the directories (copy down the MDF and LDF sql database files);
4. IMPORTANT! Open up your Control Panel -> Services utility and scroll down to the "SQL Server (SQLEXPRESS)" service. Right click it and choose properties. Click on the "Log On" tab, and click the "Local System Account" radio button. Click 'OK', then RESTART THE SQL SERVICE;
changing SQL Server's log on credentials
5. Fire up your SQL Management Studio and connect to your local database server;
6. Right click 'Databases' and choose 'Attach'. Click the 'Add' button in the resulting dialogue, then navigate your way to the folder you created in step 1;
7. Select the MDF file that should now be there and complete the attach process.
screen shot for attaching the database
VOILA! You are now using a synchronized copy of the same MDF that your other dev machine is! This is the exact setup I have running, and it's working pretty doggone good if I do say so myself.
Okay, all that having been said, please note a few caveats I have found when using this arrangement:
1. Only ever have one SQL Management Studio open at a time. If you are doing inserts or modifying tables from both machines at once, funky things happen and FolderShare suddenly becomes suicidal. In theory you should only be working from one machine at a time anyway, so this shouldn't be too much of an annoyance.
2. Remember that synchronizing takes a few minutes to kick off sometimes, so you'll have to allow a little time when going from one machine to the other before your changes will be visible.
3. If you do NOT perform the services step outlined in the tasks for machine 2, your database will attach, but it will be read-only, and that won't be much help to you.
That should be it! If anybody else has any tricks they use for keeping their dev environments in sync, we're all ears!
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
In our environment we have a central DB server that developers can access via VPN or locally. All the scripts used to develop the database solution are stored in SVN. If they really are going to be going offline and cant use the central server then they can use the scripts to build a copy for their dev ennvironment. Naturally this gives them test data, so they would still need to export then import.
I was under the impression that while SQL Server was running it kept an exclusive lock on the ldf file.
Thanx Doug. Great stuff.
BTW. Your captcha is hell on us dyslexics ;)
another interesting post but im still convinced that the solution of running a virtual machine (with web/sql server) on a portable drive is the way to go. no added internet layer or syncing issues, you can just power up the vm and connect to it's shares or with your sql management client on either your home pc or laptop.
the only downside i could possibly see is if you need more than one person to access the server at the same time but im assuming this is primarily just for the lone gunmen ;-)
cheers,
Luke
glad you like it!
initially i hit this plan because like you i needed to work in two places but was fed up with all the sync issues surrounding the files and tools a web dev needs. in work, the I.T. infrastucture was being overhauled and one of my collegues was keen to tell me about the wonder of Virtual Machines in a multi-server environment - as soon as i learnt exactly what it all entailed i could see it offered all the things i needed - albeit on a smaller scale. in my case this was done using the VMware platform but there are many others out there - i believe Microsoft offers a free one (Virtual PC).
basically the idea is this, you create a Virtual Machine (will call em a VM from now) which is just a software image (directory of files) of a real machine (size is a few gig depending on OS). this VM image can sit anywhere, in our case it's on your PC or Laptop's harddrive and you can 'run' it anytime from a bit of client software called VMware Player (http://www.vmware.com/products/player/) which you install. so all you need to do to make this machine portable is wack a copy of it onto your preffered drive and have a copy of the player on each host machine you want to run it on.
in my case i got my collegue buddy to create me a small virtual machine (win2k server) using WMware Workstation (it's about 4gig in total as it doesnt need much OS wise - hence i can get it on a MicroSD and carry it in my wallet). you can pickup a 30 day trial copy here: http://www.vmware.com/products/ws/ this will really be the only 'new' bit to you im sure and you'll need a copy of whatever OS you want to create. you then point the software at the he OS CD and it creates you a shiny new machine which you can move around.
once you have the image of the machine you can run it in the player and connect to it through it's virtual network. a virtual network is what get set up when it runs (but for all intents and purposes it's just a normal network with your machine on). im sure you've remote administered a machine before using either remote desktop / vnc or something... this is no different, the machine appears to your host (laptop or pc) as another box on the network so you can connect to it remotely (in a local way!) and then install whatever webserver, coldfusion, sqlserver and any other server bits n' bobs you might need (network shares etc). you can also connect to it using SQLserver management studio... note, the im only explaining the virtual network for the purposes of understanding - it's in reality just part of the player.
then all you need to do is hit something like the \\virtualbox\wwwroot\[someDevSite] share and store files there you can then hit http://virtualbox/[someDevSite] and be served it up.
there are so many benefits to doing this such as snapshoting your server at different states for backup purposes but primarily the fact that you have it all in one place. the only downside (for me!) is the fact that the initial setting up *can* bet a bit fiddly, it took a few attempts for me to get a small enough machine but thats a matter of preference - a standard server image will easily fit on a USB2 hdd. you can run Linux images too, great for testing without having to rebuild a physical machine.
i hope this helps a bit! do let me know if you want any more tips or pointers...
cheers,
Luke
Trust me, it isn't as difficult as it may seem - in essence, you create the VM, install the OS you require and any other software you need. You then power up the virtual machine jsut like you would any other box - you just run it in a window on the host machine, rather than flicking a real switch.