| Author |
 Topic  |
|
wildfiction
Junior Member
167 Posts |
 Posted - 25 July 2006 : 15:04:04
|
I just read this:
http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=31144
but the topic is archived/locked so I'm replying here.
Thanks for the instructions ruirib - much appreciated.
I'm thinking about migrating this forum:
http://emini.deltat1.com/forum/
It runs on a www.1and1.com hosted server. Currently using Access.
I'm trying to do a quick risk/reward analysis. There's a chance that I might make a mess of this and so I want to measure what the benefit will be if I succeed. So my question is: What sort of improvement should I expect? OR: How can I measure the potential improvement?
If I decide to go ahead with the migration, I assume that I should "lock down" the forum while migrating.
So in the Admin section I'd check:
Prohibit New Members
and then click the lock icon on the forum and "Stop the Board"
Would this do the trick until it was up and running on the SQL Server? |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 25 July 2006 : 17:33:35
|
Which package are you using at 1and1? And more importantly, will they let you do a DTS transfer of the information?
I'm also using 1and1 for my hosting, but - and it may just be the package I have - I'm not allowed to DTS the information. This makes life so much easier that I'd make sure you have that option before you start.
You can (technically) export your tables as CSV files, and 1and1 will let you (technically) import them - but I ran into so many issues I stopped and plan on starting again when I can work out what I need to do to be able to DTS the information.
Of course, all of this may just have been me - so take it with a grain of salt.  |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 July 2006 : 23:05:07
|
I see this in a very simple manner: Without DTS, I wouldn't host with them using SQL Server DBs. No external MySQL access? Well, they can keep the hosting for themselves...
You own your data but if you can't use any decent tool to manage it, your task is all the more harder. With a well managed DB server, the security risk of allowing external access to a DB is minimum. I find a decision not to allow external DB access as very telling about the hosts own technical capabilities. Makes me remember someone I once met: to avoid security issues with a computer where valuable data was stored, the manager simply chose not to connect it to a network. Of course, this guy was fired years ago... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 26 July 2006 : 06:28:48
|
I agree, and I'm not planning on hosting with them much longer if something can't be worked out. (and I'll stop there so this doesn't become a host discussion)
I just thought I'd mention the issue so wildfiction could check before he ran into the same frustrations I did. |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 27 July 2006 : 01:58:04
|
Dealing with 1&1 support is nothing but frustrating - I too will stop there.
Thanks for all that valuable info - I will check into that first.
My main question, however, remains open. Any way that I can estimate or measure the speed improvement that I should expect? (i.e. Is it worth doing?) |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 27 July 2006 : 05:19:42
|
| A move to SQL Server will enhance your forum ability to support more simultaneous users and also to perform better with a bigger database size. The database indexing structure may need some tweaking, in the long run, though. It's impossible to estimate any improvement, since that depends on too may factors, both regarding your current setup and your new setup. If your MS SQL server has a high load, you may even experience problems you don't have now. So, as a general observation, your performance should improve on a move to SQL Server, but the actual improvement depends on your current and new setups. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 00:35:20
|
Thanks ruirib! That is a big ? in all of this. I (and probably AnonJr) have experience serious difficult with 1and1 and so the move (if possible) to SQL Server could be plagued with so many problems that it does not become worth it.
Now here's an idea. Assuming that I can move the DB to 1and1's SQL Server. Do you guys see anything wrong with me copying the forum code to another sub-domain and changing the DB so that it goes to the SQL Server so I can test both side by side? |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 00:39:35
|
quote:
Originally posted by AnonJr
Which package are you using at 1and1?
MS Business package.
I was going to use the Access import functionality and then just point the ASP code at the SQL Server. I take it that that won't work? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 July 2006 : 04:16:12
|
| Converting from Access like that can cause serious problems with the forum, later on. Also, Access won't create an indexing structure and while Access can do fairly well without it, SQL Server won't. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 12:29:15
|
I have never used DTS before and I believe that AnonJr is correct about 1&1 not providing it. I have SQL Server Management Studio Express (2005) locally and am searching the help to see if it has what I need.
My latest brain flash (read: ludicrous idea) is to download the Access DB from the server, do the transformation locally with local tools (if I can find the tools and work out how to use them). Export/backup/save the new local SQL Server DB as a .BAK file. Import that file into the 1&1 SQL Server.
What do you think? |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 12:39:41
|
Okay, I have set-up a side-by-side forum with the same forum, one on Access and the other on SQL Server. I just used the 1&1 Access import feature but I know that this is not the solution and as ruirib says I need to do this properly or problems down the line.
Here are the 2 forums:
Access: http://emini.deltat1.com/forum/
SQL Server: http://snitz.deltat1.com/
My objective here is to compare the speeds - race one against the other - but not sure what the most scientific best way to do this is?
The first anomaly that I noticed is that on the SQL Server on my first post on that server:
http://snitz.deltat1.com/topic.asp?TOPIC_ID=1111
The "Read" count does not increment confirming what ruirib said about problems if not doing it properly - but remember this is a feasibility speed test at the moment.
Any suggestions or ideas about how to test the speed of these 2 essentially identical forums? |
Edited by - wildfiction on 28 July 2006 12:41:30 |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 12:47:25
|
My first unsceintific test was to do a search for the word "emini" leaving all other options at their defaults.
Access: This page was generated in 19.61 seconds.
SQL: This page was generated in 0.62 seconds.
HOWEVER: Appears to be a problem because Access returned 18 pages of results and SQL only 2 pages.
This probably comes back to the problems that I would expect to see as mentioned by ruirib earlier. |
Edited by - wildfiction on 28 July 2006 12:50:17 |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 12:51:36
|
My first unsceintific test was to do a search for the word "emini" leaving all other options at their defaults.
Access: This page was generated in 19.61 seconds.
SQL: This page was generated in 2.57 seconds.
(In the last post I had started the search from a sub forum for SQL Server and that's why it returned the incorrect number of pages.) |
|
|
|
wildfiction
Junior Member
167 Posts |
Posted - 28 July 2006 : 12:55:29
|
The other thing that I'm doing to try and keep this "fair" and objective is to run the searches at the same time which would mean that the server load was the same at that time. My next test was to search for the word "and". These are the results:
SQL Server: This page was generated in 0.62 seconds. (91 pages found)
Access: This page was generated in 49.88 seconds. (91 pages found)
I still can't work out which is faster...  |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 July 2006 : 14:06:47
|
I always do conversions locally, so that I can iron out all wrinkles that may appear. I don't believe your strategy will work, though.
To properly do the import, you'd need to install SQL Server locally. There is a trial version that you can use, but you need to be patient, both when downloading it and when installing it. It will take some time.
You can then use SQL Server Management Studio to create a DB, a login, and assign a user associated with the login to the database (assigning him the db_owner default role. Use the data in config.asp, run setup.asp to create the tables (also run any dbs files for mods you have installed) and use SQL Server Management Studio to import the data from Access, using the settings explained in my earlier post.
Once that is done, make sure the forum is running ok. You can then use SQL Server Management Studio to generate the a script to create the database. Once it is created, you can execute in your host's server. Then, use DTS to export the data from the local SQL Server DB to the hosts DB.
Another alternative, would be to try and fix the DB generated during the Access import. It's risky, can consume quite some time to check everything, but if AnonJr was right, may be the only option you have, if they don't allow external access to DTS. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
Topic Locked
