SQL 2000 to 2005

Pete · 06-02-2007, 01:00 AM

I need to migrate our current NS / Helpdesk Databases From SQL 2000 to SQL 2005. Has anyone done this and if so what has been the best way?

Thanks in advance.

tendickt · 08-20-2007, 03:49 PM

I am also looking to do this very soon. Any advice?

Pete · 08-20-2007, 04:54 PM

Hey, Well i went ahead and did it. Here was what i found.
Step 1 Back up your SQL 2000 DB
Step 2 Detach your Database
Step 3 Manually copy your Database (altiris and altiris incidents) to the default save path of your new SQL 2005 Server
Step 4 Attach the Altiris and Altiris Incidents DB's to the 2005 Server
Step 5 On your NS Server re run NSSetup and Specify you new 2005 Server

One thing that didnt transfer over correctly were my NT Logins, i ended up having to make a New DB Admin login for both databases in order for nssetup to finish properly.

The DB also grew a few gigs after running nssetup on it.

Hope that helps.

-Pete

Brandon · 08-20-2007, 05:11 PM

I am going to upgrade to NS SP3, DS 6.8SP2, and go from SQL2000 to 2005. Basically I should be able to move the DB like you specified, then do the fresh NSSP3 install to upgrade the newly migrated 2005 DB? There wasn't a MS utility you had to run against the DB first? Thanks.

Pete · 08-20-2007, 09:48 PM

I used the SQL management tools which are free and get installed with sql (if you specify to)

TRYDL · 08-20-2007, 10:19 PM

Pete certainly has the correct process. An alternative to point out would be the use of the Copy Database Wizard. The wizard will allow you to create an SSIS package so you can schedule the database move and/or copy during off hours.

I would also ensure that all indices are rebuilt and statistics are updated after the move.

My $0.02

xmdennis1 · 12-16-2008, 10:37 AM

When we update Altiris, we didn't use the old incident database, but after 2 two weeks, we decide to merge the old altiris incidents database to new altiris incidents database.

I used import/export to import all the tables of
altiris incident database of SQL 2000 from SQL 2005

But it can't work properly,

There some error about NT use login, always have an error said your NT account already exist in the database.

Any one have the successful experience of merge altiris incidents database?

jperry933 · 04-15-2009, 04:20 PM

I know this post is a bit old... but, have some questions on the steps posted...

Step 1 Back up your SQL 2000 DB
Step 2 Detach your Database
Step 3 Manually copy your Database (altiris and altiris incidents) to the default save path of your new SQL 2005 Server
Step 4 Attach the Altiris and Altiris Incidents DB's to the 2005 Server
Step 5 On your NS Server re run NSSetup and Specify you new 2005 Server

We will be installing 2005 on the same server that has 2000, which is the same server as our NS. Is an in place upgrade better or a side by side install? The above steps sound like a side by side upgrade was done or a new install was done and databases copied over for that instance. We also have a bunch of DTS jobs, I undestand these do not come over very well with the upgrade and is recommended to do a fresh install of 2005 instead of upgrade and manually create SSIS jobs. Any advice on this?

I also read that 2005 required .Net 2.0, but that NS6 has a dependency for 1.1 and you have to re-register 1.1.

We only have one server to do everything on, and it is a very beefy server and the database is HUUUUGE. We have a test server we can mess around on first, so at least we are going straight for the PRD box when we do it the first time!

Thanks for any additional suggestions.

TRYDL · 04-16-2009, 10:25 AM

Quote:

We will be installing 2005 on the same server that has 2000, which is the same server as our NS. Is an in place upgrade better or a side by side install? The above steps sound like a side by side upgrade was done or a new install was done and databases copied over for that instance. We also have a bunch of DTS jobs, I undestand these do not come over very well with the upgrade and is recommended to do a fresh install of 2005 instead of upgrade and manually create SSIS jobs. Any advice on this?

Well havent met anyone in IT who enjoys performing direct upgrades since you bring over far too much legacy .... guess it depends on the number and complexity of your DTS packages. If they dont contain numerous custom actions and most functions map to SSIS then a direct upgrade is feasible. Otherwise I would backup the databases, remove 2000, install 2005, restore the databases.

Migrating Data Transformation Services Packages

Quote:

I also read that 2005 required .Net 2.0, but that NS6 has a dependency for 1.1 and you have to re-register 1.1.

Sorta, it doesnt require it to run the rdbms engine, however all the admin tools require .net 2.0. Since NS6.0 SP3 SR7(?) they fixed the asp.net postback issue that confused IIS in which version of asp.net to use even if 1.1 was registered. And yes you will have to re-register 1.1 -- asp_reg_iis.exe -i (I may have gotten a piece of the EXE name wrong)

Honestly I would test the SQL upgrade repeatedly to ensure that not only your DBs come back online but you can migrate all necessary DTS packages.

My $0.02

jperry933 · 04-16-2009, 05:04 PM

Thanks for the tip, yes, uninstalling 2000 and installing 2005 will probably be the best route to take. Hopefully it will go smooth. Starting off with our test environment and then likely doing our smaller NSs first... then moving on to our primary NS, which is gigantic... Should be fun.