March 14, 2008 at 3:50 am
I am in the process of upgrading from SQL Server 2000 to SQL Server 2005. I'm also changing from Enterprise edition to Standard edition. (Standard edition is sufficient for our needs). I'm planning to back up the current db's, uninstall SQL Server 2000, and then install SQL Server 2005 with the same instance name on the same server.
We have hundreds of users who use Access applications, which are connected to SQL db's via odbc connections.
What I'm not sure about is whether the odbc connections will still function after I install SQL Server 2005.
Any thoughts or suggestions would be greatly appreciated.
March 14, 2008 at 3:58 am
Yes they will work fine i have tried and tested this and migrated system accross. One thing to note is ODBC client config setting. Providing it uses 'dynamically determine port' - you may find when you install 2005 that the port is not the default and it is set to be dynamic. You dont even have to change the dll used to access the database.
Oraculum
March 14, 2008 at 4:02 am
Btw - backing up dbs and restoring to the new instance is fine. Downtime increased though. What about DTS packages, SQL Agent Jobs and Maintenance plans? these will all have to be re-created if you choose this method of migration (dts packages you could save a storage files and import i guess)...but its worthing checking...
Oraculum
March 14, 2008 at 4:08 am
Thanks for the quick response!
We are a very small and simple operation at this point. No DTS packages and only a couple of scheduled backup jobs to recreate. Someone here on SQLServerCentral had previously posted a link to a script which recreates all logins and roles, which I have tested and plan to use.
Fortunately, a reasonable amount of downtime is not a problem here. What a luxury!
March 14, 2008 at 4:14 am
With regards to login scripting, if they are sql logins they may be 'orphaned users' when you restore the databases as SID's different. Most of our logins are NT Auth so not had to much of a problem, but i know the few sql accounts we have migrated have been orphaned.
This script may help... may need tweaking for 2005!
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @lc_name sysname
SET @lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @lc_name
else
PRINT '*** not fixing ' + @lc_name
SET @lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@lc_name )
END
go
Oraculum
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply