…posted by davidjmedlock:
When it comes to databases, sometimes we simply have to work with what we’ve got and hope it will get better at some point in the future. For example, sometimes you may find yourself able to use only Microsoft Access. Now, Access was not designed to power web sites. It works okay for small web sites, but it quickly becomes unmanageable, in a few ways:
- When the Access database is accessed via the website it is locked, making it virtually impossible to overwrite if you need to upload changes to the database.
- If users are interacting with your site you’ll have to make sure you don’t lose any data when you make structural changes to the database. This means making the changes on your development machine, downloading the current version from your website, and combing through the database to make sure you’ve added and modified all the right columns and tables, then making sure the data holds up, then breaking the lock on the Access file (usually by changing the DSN), then uploading the new database… (Yes, it’s messy.)
- An Access database is far more limited in the number of concurrent users it can support and once it reaches a certain size you’ll see some serious performance issues.
So, at some point moving to an actual database server is going to be a necessity for many rapidly growing sites. For Access users, the choice is often SQL Server, since it’s so incredibly easy to move data between Access and SQL Server, using Data Transformation Services.
Now, over the weekend, I found myself in the position of needing to move a database for a site over from Access to SQL Server. It was easier than I thought. Here’s all I had to do:
[list=1][*]Create the SQL Server databases: one for testing, one for production.[*]Create the ODBC datasources[*]Export the data from Access to the test database[*]Change my DSN variable in my application.cfm file[*]Change any Now() functions in my queries to GetDate() functions.[*]Test the code on a subdomain of the site[*]Upload the new code[/list]In total, it might have taken two or three hours, just because I had to find a copy of Enterprise Manager that I could use with a remote host. Fortunately for those of us who are perpetually broke, you can download the trial version of SQL Server and you get a non-expiring version of Enterprise Manager. (See referenced thread above.) Once I had that, everything else was a breeeze. :)
So, really, the only application level change I had to make was the DSN name and the current date function in my queries. The current date function could be solved in a couple of ways:
1. By setting a variable called “NowFunction” (or whatever) equal to the name of the function you need for the current database:
[left]INSERT INTO myTable ( date_added ) VALUES ( #NowFunction# )[/left]
2. By using the ColdFusion function Now() to insert the date:
INSERT INTO myTable ( date_added ) VALUES ( #Now()# )
And always be sure to test everything fully in a testing environment before you deploy it. And that is all I have to say. For now…
(Note: The code blocks above aren’t working correctly just yet. I’ll let the guys at SP know so they can check it out.)
davidjmedlock
