| Author |
 Topic  |
|
|
xatsnit
Starting Member
New Zealand
10 Posts |
 Posted - 22 September 2011 : 01:32:34
|
Hi
I have just upgraded from Access to MS SQL Server 2008. I largely used the process outlined at:
http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=63884
However some of the methods/tools it references are no longer relevant so I wanted to explain how I did it using "Microsoft SQL Server Migration Assistant for Access" (SSMA).
SSMA
Download "Microsoft SQL Server Migration Assistant for Access" from:
http://www.microsoft.com/download/en/details.aspx?id=959
How I upgraded
1. Create SQL Server 2008 database and add 2 users with passwords (method for this will depend on your setup, but with mine it was merely a function in my web site admin page)
2. Shutdown your forum using the function in Admin Options
3. Set strDBType = "sqlserver" in config.asp, comment out the "access" one
4. Then comment out Access connection str (also in config.asp) and specify the SQL Server connection string:
strConnString = "Provider=SQLOLEDB;Data Source=Some.Web_Adresss.com;database=forumname;uid=username;pwd=password;"
5. Go to Setup.asp and create the tables
6. Go to Admin Options, MOD Setup and create tables for all mods
7. Run SSMA 32bit or 64bit version to MATCH the version of Access that you have installed
(In my case I had Access 32bit running on a 64bit system, but found that SSMA 64bit did not import)
8. Use wizard steps to specify your local Access file (assumes you have downloaded it via FTP) and remote SQL database
Note: It can take a long time to load schema and objects (i.e. it will appear to be doing nothing for several minutes)
9. Follow the steps to complete the conversion
10. Check the database and forum and then start it up again using the function in Admin Options
Done!
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 22 September 2011 : 17:20:06
|
Nope, this will get you a Snitz running on a SQL Server database, but not with a structure with proper indexes (and don't even know if with the best data types chosen), which will have serious performance consequences for your forum.
Our recommended upgrade procedure remains unchanged, and it does not involve wizards. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
xatsnit
Starting Member
New Zealand
10 Posts |
Posted - 22 September 2011 : 19:15:05
|
Hi Rui
Thanks for the info. I was worried about the indexes, but using the tool available from host provider it did report that the relevant tables all had sizable indexes so I assumed it was OK.
Please feel free to delete my post. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
TastyNutz
Junior Member
USA
251 Posts |
Posted - 26 January 2012 : 13:43:26
|
quote:
Originally posted by ruirib
Nope, this will get you a Snitz running on a SQL Server database, but not with a structure with proper indexes (and don't even know if with the best data types chosen), which will have serious performance consequences for your forum.
Our recommended upgrade procedure remains unchanged, and it does not involve wizards.
ruirib, can you clarify the "official" stance on the SSMA for migrating Snitz? Several years ago, Huwr seemed to endorse its use, and you seemed to have softened your objections. But here you're back to saying it shouldn't be used.
I'm very interested in getting as much up-to-date information as possible because I have an Access database with 7 years of posts I'd love to preserve. It's gotten quite large (160mb), and while performance has been an issue for awhile now, it's recently becoming intolerable.
I'm currently experimenting with the SSMA and having good results.
My method is basically the same as the OP above... First, I had to upgrade my hosting account to "grid hosting" to allow direct access to the database. Then I created a new SQL Server database and installed the latest version of Snitz and created the database tables. I then ran my dbs_ file to create all the necessary mod tables. Finally, I used the SSMA to convert and migrate my data.
As mentioned in the archived topic, I had to correct some issues with Nulls, but after that, all my data has transferred and the forum (testing installation) is running faster than ever.
What can I do to check for the types of concerns you have with this method? I know you mentioned the indexes, of which I don't have a great understanding.
Thanks |
PowerQuad Disability Support Forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 January 2012 : 14:24:18
|
Nothing as changed regarding our advised migration method. Indexes are indispensable in a SQL Server database and no wizard will create them. So while you can have your data there without issues (and I cannot confirm that, as I didn't use that method ever), the indexing structure will be lacking.
If you are not knowledgeable about SQL Server, it's hard to advise how to proceed, other than recommeding our upgrade process. To fix an upgraded database, lots of things would be to be considered, from data types, to indexes created by the wizard, including the indexes Snitz creates itself. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
TastyNutz
Junior Member
USA
251 Posts |
Posted - 26 January 2012 : 15:11:58
|
quote:
Originally posted by ruirib
If you are not knowledgeable about SQL Server, it's hard to advise how to proceed, other than recommeding our upgrade process.
Where can this process be found? Is it up-to-date? As mentioned in the OP above, the archived migration topic uses superseded tools. |
PowerQuad Disability Support Forum |
|
|
|
TastyNutz
Junior Member
USA
251 Posts |
Posted - 26 January 2012 : 15:16:14
|
| Also, would you be interested in seeing a migrated database? I could easily give you access to poke around. |
PowerQuad Disability Support Forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 January 2012 : 17:10:25
|
What are the superseded tools? A migration requires only SQL Server Management Studio, nothing else.
I am terribly late on my work, sorry, I won't have any chances to look at the database. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
TastyNutz
Junior Member
USA
251 Posts |
Posted - 26 January 2012 : 20:17:12
|
| Okay, I've installed SQL Server Management Studio Express and will attempt migrating again. I'll report my experience later. |
PowerQuad Disability Support Forum |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
TastyNutz
Junior Member
USA
251 Posts |
Posted - 27 January 2012 : 00:28:23
|
quote:
Originally posted by ruirib
I am not sure you can do it with the express version. I never used it.
That was my concern as well, but I decided to give it a try.
I downloaded SQL Server 2008 Management Studio Express from HERE. It installed as a free version license and includes the import data functions.
I created a new database and ran setup and my dbs_ to create all the tables. I connected with Management Studio and the process was roughly the same as outlined in the archived topic you linked.
I got several data mapping errors because the wizard didn't correctly identify the TEXT data type in a couple of my mod tables. I got around that by opening the Access tables in question and changed the data type to MEMO. That resulted in warnings rather than errors, and the transfer completed successfully. I connected my testing forum to the new database and it's working well.
|
PowerQuad Disability Support Forum |
|
|
|
TastyNutz
Junior Member
USA
251 Posts |
Posted - 27 January 2012 : 00:29:26
|
My issue now is the new grid hosting (4GH) plan doesn't play well with Classic ASP applications and I keep getting "HTTP/1.1 New Session Failed" errors. I did some Googling and this is apparently on ongoing issue with GoDaddy. So, there's no way I can use that account to host my live forum. But I need a 4GH account if I want remote access to the database. Grrrr.
A workaround may be to leave the forum on the regular shared hosting account and have the connection string point to the 4GH database. I'll have to think it over. |
PowerQuad Disability Support Forum |
|
|
| |
Topic |
|
