April 1, 2010 at 10:14 am
We have a production Win server 2003 SP2 server with 11 SQL 2000 SP3 databases loaded to it. We need to upgrade one db to SQL 2005 to stay current. The remaining dbs can stay at SQL 2000. I know we can do a side by side install (thanks to other posts on this forum...) of the two SQLs, but only selected logins are needed on the db that will be upgraded to 2005.
Once I run the login extract from the SQL 2000, can I "edit" the output listing to remove the dozens of SQL 2000 logins that will not be required on the SQL 2005 instance? I really do not want to end up with logins on SQL 2005 that will not have a corresponding db to attach to. Any suggestions here?
Any issues / gotchas I need to be aware of when doing this?:w00t: Thanks. JNelson
April 1, 2010 at 1:45 pm
Absolutely, remove the logins. No sense in having something there that is unnecessary.
Besides the side by side install, have you considered moving all of the databases to SQL 2005? You could leave the other databases in compatibility mode, reduce your licensing cost and administrative effort.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 2:01 pm
CirquedeSQLeil responded:
Besides the side by side install, have you considered moving all of the databases to SQL 2005?
Eventually, we will convert the remaining databases, but several are maintained by outside vendors who are a tad slow in converting their code to a higher SQL level. We could "probably" be safe to convert them, but would definitely lose vendor support by doing so. Three of them are in house developed, so once we get the environment set up, they will be converted over.
Any gotchas I need to deal with as I delete logins from the output file generated by the SQL 2000 login "dump"? Thanks.
April 1, 2010 at 2:09 pm
nelsonj-902869 (4/1/2010)
CirquedeSQLeil responded:Besides the side by side install, have you considered moving all of the databases to SQL 2005?
Eventually, we will convert the remaining databases, but several are maintained by outside vendors who are a tad slow in converting their code to a higher SQL level. We could "probably" be safe to convert them, but would definitely lose vendor support by doing so. Three of them are in house developed, so once we get the environment set up, they will be converted over.
Good luck. Often times the database will be fine on a SQL 2005 box while running under 2000 compatibility. Testing is necessary though.
Any gotchas I need to deal with as I delete logins from the output file generated by the SQL 2000 login "dump"? Thanks.
Create multiple copies of the script. Edit the script to remove unwanted logins and be meticulous. Otherwise no gotchas.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 5:24 am
CirquedeSQLeil (4/1/2010)
Often times the database will be fine on a SQL 2005 box while running under 2000 compatibility. Testing is necessary though.
Yes. Compatibility levels are not perfect. One real-life experience was of running a 2000-only application in compatibility mode, which failed because occasional batches included the *= outer join syntax.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 8:23 am
Paul White NZ (4/2/2010)
CirquedeSQLeil (4/1/2010)
Often times the database will be fine on a SQL 2005 box while running under 2000 compatibility. Testing is necessary though.Yes. Compatibility levels are not perfect. One real-life experience was of running a 2000-only application in compatibility mode, which failed because occasional batches included the *= outer join syntax.
If you do this kind of setup in dev first though, then you have a good way to test the application and flesh out those problems.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 8:26 am
CirquedeSQLeil (4/2/2010)
If you do this kind of setup in dev first though, then you have a good way to test the application and flesh out those problems.
You are assuming that the company in question had a DEV or TEST environment...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 9:24 am
Paul White NZ (4/2/2010)
One real-life experience was of running a 2000-only application in compatibility mode, which failed because occasional batches included the *= outer join syntax.
I too have had similar experinces of this.
Nelson i presume you are using the MS script to export logins from one instance to another. If so, the script notes strongly recommend you review the output and remove any unwanted logins (especially the windows account\group assignments) before applying to the new server.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 2, 2010 at 10:34 am
Paul White NZ (4/2/2010)
CirquedeSQLeil (4/2/2010)
If you do this kind of setup in dev first though, then you have a good way to test the application and flesh out those problems.You are assuming that the company in question had a DEV or TEST environment...
Yes, I know all about assumptions. But let it stand as an assertion that they should have the environment as a Best Practice.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 10:47 am
Perry Whittle (4/2/2010)
Nelson i presume you are using the MS script to export logins from one instance to another. If so, the script notes strongly recommend you review the output and remove any unwanted logins (especially the windows account\group assignments) before applying to the new server.
Yup, started that task yesterday with many lines to go through....patiently, slowly, with much checking back and forth.;-) Fortunately the 2005 environment is kinda-sorta test at this time, and there is no hurry to get the db ported over.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply