| Author |
 Topic  |
|
Carlos Miranda Levy
Starting Member
26 Posts |
 Posted - 30 January 2007 : 03:55:54
|
This is a step by step description of the procedure I used to migrate my 5 forums from Access to MS SQL.
I had tried before by importing the data into MS SQL, but was warned by Rui about that being a bad choice because of the indexes and other issues that would eventually affect performance.
I tried another time later by following the simple steps outlined, but it was not only after I had completed the migration that I learned of having to make sure the "Enable identity insert" is selected when importing the data...
One thing to worry here is that in both of the migrations listed above, the forum appeared to work fine, although I had not followed proper procedure. Reading several posts of other people who claimed to had successfully migrated their databases, I'm pretty sure several of them were making the same mistakes I made above. If it would not have been thanks to the warnings and advice of others like Rui, I would have carried on with my "successfully" migrated faulty forums.
So I decided to try again. The problem is that there are no formal or detailed instructions for migrating Snitz from Access to MS SQL. Rui has posted several times a general outline, but for someone doing it for the first time, the amount of questions and doubts faced is actually a bit frightening.
My third try was a mess. I did all things wrong and I knew things were not going well. So I started over, carefully looking at everything and documenting it until successfully completing the procedure, with the forum and mod tables created by Snitz itself, not by the Data Transformation Services wizard, the data imported with Identity insert and overcoming a couple of errors that I'm pretty sure others may encounter.
The following steps list the process of migrating a Snitz Forum database from Access to MS SQL by a user with full access to SQL Enterprise Server Manager.
<font size="1">In my case, I run the forum in one server and MS SQL in another and access both via Terminal Services (Remote Desktop). Those sharing an MS SQL may have other default options or server names, but the instructions will be pretty similar.</font id="size1">
<hr noshade size="1">
<font size="4">1. Open SQL Enterprise Server Manager</font id="size4">
Default Location under Windows 2003 would be:
Start / All Programs / Microsoft SQL Server / Enterprise Manager
<font size="4">2. Make sure "SQL Authentication" is set to "SQL Server and Windows"</font id="size4">
Within SQL Server Enterprise Manager's main console, right click on your main server, probably (local)(Windows NT):
[ ] Console Root
- Microsoft SQL Servers
- SQL Server Group
- (local)(Windows NT) <---- Right click here, whatever it's called in your computer.
- Databases
Then click on the "Security Tab" and make sure you have "SQL Server and Windows" selected. If you had to change it and are able to reboot your server, do so (dunno if this is necessary, but I like to pursue peace of mind and rule out problems in advance).
<font size="4">3. Create a new SQL Database.</font id="size4">
Right click on Databases, under your current server -- (local)(Windows NT) in our example above -- and select "New Database". Give it a name and click OK.
<font size="4">4. Create a new user and login access for the new database.</font id="size4">
a) Look for your new database under Databases in the console.
b) Right click on the database name and choose New / Database User.
c) In Login Name, choose <new> from the list available.
d) Enter a Login Name for the user, choose SQL Authentication and enter a password.
e) Click on OK.
f) Re-enter the password when prompted and click on OK
g) Back to the "Database User Properties - New User" screen, select the user you created from the list of users available in Login Name.
h) Then click on db_owner under Database role membership: / Permit in Database Role.
<font size="4">5. Make sure you have another user with system administrator permissions.</font id="size4">
If it's your server and you're the only one using it, you can simply take note of the sa user's password or change it so you know the password.
a) Look for the Security / Logins option in the left pane of Enterprise Manager.
b) Click on Logins
c) Right click on the sa user and select properties.
d) Change the password and confirm it.
<font size="4">6. Edit your config.asp to connect to the SQL database.</font id="size4">
a) Comment the old strDBType = "access" and the old strConnString lines.
b) Uncomment the strDBType = "sqlserver" line.
c) Uncomment the SQLOLEDB sample connection string (or your string of choice), and edit it to include the proper info, for example:
strConnString = "Provider=SQLOLEDB;Data Source=#.#.#.##;database=database_name;uid=loginuseryoucreated;pwd=password;"
You may use the local server name in Data Source=, however, if your SQL Server is running on a separate machine, entering the proper IP discards other sources of complication for connecting.
d) Save the file.
<font size="4">7. Run Setup.asp to create the database tables.</font id="size4">
a) In your web browser navigate to: www.yoursite.com/forumdirectory/setup.asp?RC=5
b) Choose your SQL version.
c) Enter a user/password with administration permissiones (user sa will do).
d) Enter a user/password (and confirm password) for a forum administrator user.
e) Click on OK.
<font size="4">8. You should have a successful install.</font id="size4">
a) Click on the link to check the database and if it tells you that there is no need to update the database, just click on the link to go to the forum.
b) You should now have an operational Snitz Forum running on a MS SQL database.
<font size="4">9. Create the required tables and table modifications for your installed mods.</font id="size4">
(Run the database scripts for your installed mods)
a) Log in to the new empty forum with the administration user you created on step 7.
b) Go to Administration Options.
c) Click on the Mod Installation option to go to admin_mod_dbsetup.asp
d) Choose each mod you use or have installed in the past from the drop down list and click on Update. One at a time. Repeat until you have created the tables for all of them.
<font size="4">10. Import the data from the Access database (follow carefully)</font id="size4">
a) Go to Enterprise Manager.
b) Right click on your database, select All Tasks and then Import / Data
c) Click on Next.
d) Look for Microsoft Access in the list available for Data Source.
e) click on the [...] button in File Name to look for your source Access Database.
f) Click on Next
g) Review settings for the destination database.
- In Destination, choose Microsoft OLE DB Provider for SQL Server (should be chosen for you already).
- Server: Indicate your MS SQL Server name, IP or if you are on the machine like me, choose local.
- Use Windows Authentication: Important, do not use SQL Server Authentication and the login/password you created for your connection script, or you will end up creating another set of tables.
h) Click on Next
i) Choose Copy table(s) and view(s) from the source database. (should be chosen as default for you)
j) Click on Next.
k) Select all tables: Click on Select All.
l) For each table listed, click on the [...] button in the "Transform Column" and choose "Delete rows in destination table" and "Enable identity insert".
Note that if you don't have the "Delete rows in destination table" option available but one that says, "Drop Tables and create new...", you are not doing it right and will end up having the Null error many people have mentioned elsewhere here.
If you follow the steps until here, you will notice that for the mods tables, the options "Delete rows in destination table" is not available. That's simply because the core forum tables were created with the sa user and the mods tables were created with the Login user indicated in the connection string.
You can either:
<ul><li><font color="green">Change the destination table for the mods tables you are importing so that the data is imported in the corresponding existing tables (owned by the same user as the rest).</font id="green">
<font color="brown">To do this, just select the existing corresponding table from the dropdown in the second column.</font id="brown">
</li>
<li><font color="green">Another option is to simply change the owner of the mods tables to the default dbo owner.</font id="green">
<font color="brown">To do this, in Enterprise Manager, just go to Tools/SQL Query Analyzer and type something like:
sp_changeobjectowner 'user_in_connstring.modtablename','dbo'</font id="brown">
</li></ul>
Otherwise, new tables will be created for the imported mod tables. I tried both methods above, and both work fine. Once you do either one, you will be able to select both "Delete rows in destination table" and "Enable identity insert" for them and continue...
My old Access database included a table called FORUM_GROUP, apparently a legacy of earlier versions, for which there was no equivalent in the SQL database. This is the only table for which I did not choose "Delete rows in destination table" but chose the "Create destination table" and "Enable identity insert", just in case.
I stopped documenting here, as dawn was nearing and I was also eager to complete the migration, but all you have to do is click on next and run the import and you should be all set.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><font color="brown">Another issue I encountered while migrating two of my forums was an error while copying data from the FORUM_FORUM table:</font id="brown">
<font color="red">Error at Destination for Row number ##. Errors encountered so far in this task: #.
Insert error, column 27('F_L_DELETE',DBTYPE_WSTR),status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.
Insert error, column 25('F_L_ARCHIVE',DBTYPE_WSTR),status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.
Unspecified error.</font id="red">
<font color="brown">Apparently this is caused by some old data in an invalid format or for some reason because I once archived a couple of forums and later removed their data from the database, not sure. This field (column) was empty in FORUM_FORUM table of my Access database, except for what looked like a date for two records (rows). In any case, I solved this issue by doing the following:
1) In the Column Mappings screen of the import process, click on the [...] button corresponding to FORUM_FORUM in the third column.
2) Click on the Transformations Tab.
3) Choose "Transformation information as it is copied to the destination".
4) Scroll down in the transform operations listed in the edit field and change:
DTSDestination("F_L_ARCHIVE") = DTSSource("F_L_ARCHIVE")
into
DTSDestination("F_L_ARCHIVE") = ""
and
DTSDestination("F_L_DELETE") = DTSSource("F_L_DELETE")
into
DTSDestination("F_L_DELETE") = ""
This solved the issue, eliminating the error above and a bunch of subsequent errors that were somehow triggered by it during the import process. I was able to import all tables without any problem. Of course, this worked for me because I have no archived topics in my forums, I guess you may lose your archive or whatever those fields are for if you do this.</font id="brown"><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
<font size="4">11. Update Forum Totals.</font id="size4">
This is recommended in several posts, so do it...
<font size="4">12. Check Installation.</font id="size4">
For some reason I need to do this step in order for the forum to read the config from the database (forum title, features, etc.)
Go to the Administrative Section and choose Test Installation (use after every update) -- the wording may be a little different since my forum has the admin section translated to spanish, but it's the option that links to: www.yoursite.com/forumdirectory/setup.asp" target="_blank">http://www.yoursite.com/forumdirectory/setup.asp
There, click on "Click here to go to the Forum", or whatever it says in English. You shouldn't need to upgrade database or anything...
Now, test everything and then change the connection string in your actual forum... you were not doing everything above on your live forum right? you were testing the migration in a copy of the forum just in case... right?... oh, boy, you are a wild one...
|
Edited by - Carlos Miranda Levy on 31 January 2007 17:18:26 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 January 2007 : 05:22:53
|
This is a good, detailed, clear description, thank you. Once you're happy with it, we can make it a single, no replies allowed, sticky topic.
Step 11 is unneeded. If the totals in the Acess DB were correct, they will be correct once the data transfer is done.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
MarcelG
Retired Support Moderator
Netherlands
2625 Posts |
Posted - 30 January 2007 : 06:48:58
|
| Just to make sure : there is no method of importing the existing access database without rfirst running setup and running the dbs files used for each installed mod?< |
portfolio - linkshrinker - oxle - twitter |
Edited by - MarcelG on 30 January 2007 06:49:21 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 January 2007 : 07:56:07
|
You need to take into account that an SQL Server DB is not an Access DB. So, even if there is sofware that can recreate the tables without errors (if you take proper care), there are issues that will persist if the migration is not that starting with the tables creation using setup.asp. One will be performance. An SQL Server Db without proper indexes is much slower than an Access DB...
So, again, you NEED TO run setup.asp and the mod dbs files to create the tables as the first step in upgrading from Access to SQL Server.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 30 January 2007 : 08:31:32
|
Carlos, that's beautiful - it's all down to dedicated and willing volunteers to create things like this, so it's great to see it being done so freely and well :)
Once it's 100%, as Rui says, it'll be an invaluable resource for us to refer people to.
Nice one, and on behalf of all of Snitzdom, thanks :)< |
|
|
|
JJenson
Advanced Member
USA
2121 Posts |
Posted - 30 January 2007 : 10:55:01
|
I will try to do this on my hosting server following the steps and get back to you. I am currently downloading my forum and going to set it all back up and try to make the conversion that way. Will let you know Carlos. And thanks very much I wanted to do this with one of my access databases I ahve having troubles with. So thank you will report back.  < |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 31 January 2007 : 11:35:13
|
Sorry but I have some newbie questions prior to step 1. This all looks great but it looks like you need to be logged in to the SQL management tool locally (where the site and database are located). Are you doing this from the server where your forums are located or is this over the Internet? Can SQL Enterprise Server Manager work over the internet? Is SQL Enterprise Server Manager a Microsoft product and is it a free tool that can be downloaded? Lastly, if I sent you an Access database what would you charge me to migrate it to MS SQL 2005 Express?  < |
Edited by - JohnC on 31 January 2007 15:30:14 |
|
|
|
Carlos Miranda Levy
Starting Member
26 Posts |
Posted - 31 January 2007 : 17:25:50
|
I have now migrated 4 forums, including a couple of old and large databases. I have reviewed and updated the instructions to address some issues and difficulties I have encountered.
I have 2 more to go, but I don't think there will be any more surprises or changes to the steps I'm following. Feel free to modify/edit/improve them.
Regards,
Carlos.< |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 31 January 2007 : 18:33:47
|
| Does anyone care to answer my question...?< |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 31 January 2007 : 19:49:29
|
| He partly did in the last round of edits.< |
|
|
|
Carlos Miranda Levy
Starting Member
26 Posts |
Posted - 03 February 2007 : 06:12:43
|
If you send someone your Access database to migrate it to SQL, you will still be in the same position as in migrating from Access because you will have to import the SQL database anyway into your SQL server (which is pretty much like importing from an Access database).
In any case what you may want is to give someone access to your SQL server and your forum to follow the proper steps.
quote:
Originally posted by JohnC
Sorry but I have some newbie questions prior to step 1. This all looks great but it looks like you need to be logged in to the SQL management tool locally (where the site and database are located). Are you doing this from the server where your forums are located or is this over the Internet? Can SQL Enterprise Server Manager work over the internet? Is SQL Enterprise Server Manager a Microsoft product and is it a free tool that can be downloaded? Lastly, if I sent you an Access database what would you charge me to migrate it to MS SQL 2005 Express?
< |
|
|
|
daveo
New Member
97 Posts |
Posted - 03 February 2007 : 11:25:03
|
| Actually someone could create the SQL database for them and send them the database (after detaching it. They could then attach the database to the server and be ready to go.< |
http://copdforum.portalone.us/ |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 03 February 2007 : 13:11:46
|
| daveo, I guess most likely if someone's hitting problems with SQL Server, they're not going to be in a position to reattach a db, but technically yes :)< |
|
|
|
daveo
New Member
97 Posts |
Posted - 05 February 2007 : 01:06:03
|
| Actually, that is probably the easiest if they have access to the tools. I can do it in Enterprise Manager in a few minutes. I have an advantage - I own the servers I run on and have my own Rack at Time Warner's Co-Lo facility. I would be glad to help, after I get back from my business trip later this week.< |
http://copdforum.portalone.us/ |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 05 February 2007 : 08:29:08
|
Beware you don't overcommit yourself with offers like that - you may end up with dozens of excitible folks hoping to take you up on it ;-)
Very decent of you to offer the service though< |
|
|
|
daveo
New Member
97 Posts |
Posted - 05 February 2007 : 15:11:21
|
| I am considering using my facilities top get into the forum hosting business - I currently only hosts those not for profit groups I am involved with.< |
http://copdforum.portalone.us/ |
|
|
|
Topic |
|
|
|
Topic Locked
