August 1, 2007 at 11:31 pm
Hi all,
We are plannig to migrate our sql 2000 databases to sql 2005. as a trial, I have set up a sql 2005 machine and restored one of user databases from sql 2000 to this sql 2005 machine. before I go further to follow the instruction on migration, I have the following questions that you may help me:
1. Shall we migrate all the system databases?
2. I was told there were customized templates in sql 2000, the model database has to be migrated. is this same as user databases migration?
3. since there were databases for web application open to the public, we were considering change all windows authentication to sql authentication, should we still need migrate the logins? or modify then recreate them?
Thanks for advice in advance,
Sherry
August 2, 2007 at 10:22 am
We haven't done our migration yet, from 7 to 2005, so I can't give a lot of advice, but I would encourage installing ss2k5 in MIXED mode, not relying solely on windows authentication. Even if you end up using Windows authentication for all users, having the SA login as a 'back door' while setting everything up is quite useful, and is a bit of an insurance policy.
August 2, 2007 at 5:17 pm
Hi Steve,
Thanks for the advice. we are using and will keep Mixed authentication mode for the instance. however the web machine will be moved to another network without DNS etc., so we are considering change user logins in the databases to sql authentication. I would like to take your advice and try to imply security policy as strict as possible. any further details on the security policies that you can help with?
Sherry
August 3, 2007 at 10:40 am
Hi Sherry -
Most of the security is managed within the application. We use SQL Server validation rather than incorporating the validation within Windows - in part because we have users connecting remotely via Citrix Server due to limitations where they connect from. By defining groups within the Server environment, we can limit access. By only allowing the users to access the content within the application framework (thick client), we have further limited what can be done with the data, by whom, and when.
Since we are storing sensitive personal information, the decision was made to take this approach long before I appeared on the scene.
Much depends on the design of the whole environment and what happens when within the environment. Specific questions will probably give you more satisfying answers than this is - without knowing what your application(s) are, it's very hard to give more specific advice.
August 5, 2007 at 5:02 pm
Hi Steve,
Thanks again for all the information and your time. I am sure it's helpful with our plan.
Sherry
August 7, 2007 at 8:08 am
You might check out the post "The Steps Before Migration"
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=359&messageid=378376
Steve
August 7, 2007 at 4:58 pm
Thanks again Steve. all the links and advices will help me to collect information and prepare the plan.
Sherry
August 9, 2007 at 8:42 am
I would strongly advise that you do NOT try to restore system databases from SQL 2000 or SQL 7 to SQL 2005. There are a number of changes made to the objects in these databases in SQL 2005. If you do get SQL 2005 to appear to work with down-level system databases, you may find yourself on your own if they cause a support issue at some point in the future.
If you do an upgrade in place, then any changes needed are made to your system databases. If you do a clean install (recommended by most people!) then you need to script out anything you have added to the old system databases and script them into the new ones.
If you script out or script in DTS packages, be aware that the internal layout of these packages in DTS Designer will be lost. The only way to retain the DTS Designer layout is to use EM and save to a structured storage file, and then open each package in DTS Designer in SQL 2005. If you do loose the DTS Designer layout this does not affect how the package works, it just affects how it looks in DTS Designer.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 9, 2007 at 5:55 pm
Hi EdVassie,
Your advice on DTS packages are very useful, which I will take fully into account when migrating the databases.
I won't migrate the system databases as I also realize the system databases upgrade from different builds or different versions are not supported by SQL server. I will check if there were customized settings in the model database on SS2K. However the SS2k machine/databases were setup and administered by someone else years ago. Except the "database options" setting in model database, what else should I look for to script out from the system databases? I would like someone to have advice on this further.
I am planning to do a user database migration (side-by-side upgrade).
Thanks,
Sherry
August 10, 2007 at 2:01 am
Sherry Hu,
Look in all the system databases for any objects labelled as User. Anything labelled as System will be replaced by SQL Server 2005 system objects.
In msdb there are some tables associated with replication that are labelled User but are really System. If you compare these names with other SQL Server 2000 instances (or even do a special install of SQL 2000) you will see what you can ignore.
All remaining User objects need to be scripted, so they can be loaded back into SQL Server 2005. Also script logins, SQL Agent Operators, Alerts and Jobs.
If you send me your email, I can send you a copy of our SQL Server 2005 Build documentation and install scripts. These may help in your upgrade process.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 12, 2007 at 5:39 pm
EdVassie,
a private message with my email has been sent to you. please find it.
Thanks,
Sherry
August 13, 2007 at 12:31 am
Hi EdVassie,
Thanks for reminding me about user type objects in system databases. I did find lots of tables labled as user type. in SQL Server 2005 master database, I can see with Object Explorer, there is "System Tables" under folder "Tables".
what should I do when migrating to SS2k5? scripting all these user type tables in master of SS2k and run the script in master database? this SS2k5 server is currently a test machine only.
I am not sure whether I can find the original SS2k CD but I might be able to get an image of the SS2k server on VM to work on.
any suggestion?
welcome comments from any people who can help.
Thanks,
Sherry
August 15, 2007 at 6:35 pm
Hi EdVassie,
I am not sure if you've got the private message and I don't know how it works. if not yet, my email is sherryh@phm.gov.au
Thanks,
Sherry
August 16, 2007 at 1:38 am
Any insight on why it is recommended to do a clean install??
August 16, 2007 at 2:56 am
There are a few threads in this forum about upgrading to SQL Server 2005. It is worth searching for them as there are a lot of posts about the issues of upgrading in place compared to a clean install.
There are people who have got an upgrade in place to work OK, but there are a number of posts from people who have had problems. Often if the upgrade fails part way through, it is not possible to start SQL Server and the only way forward is uninstall followed by a clean install. If this happens and you have not prepared for a clean install, then you risk loosing logins, DTS packages, etc, etc.
Even if an upgrade in place works, not all of the tools will work. The folder names created by the install include MSSQL.n (e.g. MSSQL.1, MSSQL.2) in the path name. Some of the tools fail on startup with a message about an index being out of range - I suspect they cannot cope with the standard SQL 2000 path that does not have the .n portion.
Finally, you end up with an install that uses a combination of SQL 2000 and SQL 2005 standard folder names and registry entries. My experience with SQL 2005 service packs is they are not very tolerant of file locations that are different to what you get with a clean install. Taking this and the problems with the tools into account, there is a risk that a future service pack or hotfix may not apply properly to a system that has been upgraded in place.
All of these problems do not occurr if you do a clean install. OK it is a hassle to script out everything you need to load back into SQL Server 2005 (logins, DTS, Jobs, etc, etc), but the end result is more stable.
Each shop needs to assess these issues and risks and decide if a clean install or upgrade in place is best for them. My personal preference is to always do a clean install.
Finally, if anyone wants a copy of our SQL 2005 Build guide and scripted install, send me your email.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply