Recently, I upgraded one of our database servers from SQL 2000 to SQL 2005. At the moment, all the databases are in 80 compatibility mode. I upgraded them based on the false pretenses that I could perform online reindexing right off the bat. Apparently, that isn't the case, and it requires switching to 90 compatibility. My question is if I switch to 90, will all indexes be invalidated/unusable? Or will I be able to switch to 90 and perform the online reindexing right away with no downtime?
We have a few 24/7 production VLDB's in the mix (one of which is on this newly upgraded server), and only reasonable downtime is possible. The largest window of downtime I have is about 10 PM - 5 AM with prior notice.
The upgrade was performed using SAN-based LUN's with a detach/reattach.
Recently moved a couple of dbs from SQL 2000 -> SQL 2005 (different machine). Just restored the dbs on the new server from a full backup of the old one. Everything is fine but...
Some queries are returning out-of-order results (for records added since the migration). I tracked this down to the indexes. I completely rebuilt and reorganized the indexes (online) through the manager interface. This helped provide correct ordering for existing records, but new records added since the re index are still out-of-order.
Looking for a way to permanently fix the indexes so I don't have to constantly rebuild them.
Hello, I have a project where I have to upgrade SQL 2000 to 2005. When we are upgrading we are going to move to new server so server name will change. Most of our applications have a hard coded connection string. Applications are developed with Classic ASP, .NET 1.1 and 2.0 as well. Now the question is what is the best way to do this? Changing all the connection string will be a very painful task as we have many applications. Can anyone suggest a way where we can achieve this without changing all the connection strings? Thanks in advance. Tareq
what are some of the issues that we might face while upgrading from SQl 2000 to SQl 2005? Actually I have to create upgrade plan for path from MS 2000 to MS 2005.
I would like to upgrade sql server 2000 to sql 2005. So Can I install the sql server 2005 on the same server where sql 2000 is already running and then use the database upgrade wizard. Or should I install the sql server 2005 on a different server.
I have a sql 2000/2005 side-by-side install on a single server. I right-click a sql 2000 database, select All Tasks, and the Copy Database to upgrade a sql 2000 database. In the "Copy Database Wizard" I see the message:
"To use the detach and attach method SQL Server Agent must run under an Integration Servicers Proxy account that can access the file system of both the source and destination servers."
1. What does that mean?
2. I decided to click "Finish" in the wizard, and the database copy seemed to work fine. Whaterver 1) means does, by default, the SQL Server Agent already run under this "Integration Services Proxy" account. (Rememberr the upgrade process did work).
Looking for some type of solution on this issue. I'm going from SQL2000 to SQL2005 and I run the upgrade advisory tool and all and I'm going through the procedures of getting our SQL server updated to SQL2005, when I attempt to run that analyzer to take a look at how well the migration will go. I get a timed out error message on the fallowing.
Exact error message and its always the same for each entry. "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
Any help in this are would be greatly appreciated, all other issues have been resolved I'm just stuck on this and I don't want to upgrade until the advisory tool gives me the ok to.....
I am in the beginning of upgrading a SQL 2000 DB on a Small business server to a 64 bit 2005 DB on a new box. Is my best method of upgrading to use the "copy" DB function in 2005? If so, what do I need to do to install the management tools on the 2000 box - since this is a 32 bit edition, and I have the 64 bit media?
I got a requirement to upgrade only One SQL 2000 to SQL 2005 server. i did check that there are only databases in this server along with Linked server. I am looking for a best practice document for SQL upgrade and any thoughts or experience any one has faced during upgrade.
Other question is what about if there is a DTS on the server. right now on this particular server there is no DTS but in coming weeks i may need to upgrade other server which is having DTS as well. I have worked alot on SSIS packages but want to see if DTS will be converted to SSIS packages or i will have to manually do it.
We have 2 .net 1.1 applications using Microsoft SQL Server 2000 Enterprise Edition. To connect to database we are using nHibernate 0.6 for one application and Microsoft Enterprise Library Data Blocks Ver 1.0 in another. We are planning to upgrade SQL servers of both applications to SQL 2005 Enterprise edition and retain .net 1.1 framework. Are nHibernate 0.6 and Microsoft Enterprise Library 1.0 data blocks compatible with the SQL 2005? Are any upgrades required for these as well? We ran SQL upgrade advisor report on these applications, there weren't any changes to table/ stored procs suggested. Could that mean that the existing table design and stored procs are compatible with SQL 2005?
I just ran sql upgrade advisor and got the following message: "You can use SQL Server 2005 tools to edit your existing DTS packages. However, upgrading or uninstalling the last instance of SQL Server 2000 on a computer removes the components required to support this feature. You can retain or restore these components by installing the special Web download, €œSQL Server 2000 DTS Designer Components,€? from the Microsoft Download Center before or after you upgrade or uninstall SQL Server 2000."
I do have several DTS packages in my sql server 2000 database. does that mean if I upgrade my current 2000 database to 2005, those dts will be gone. But I can download sql server 2000 dts deginer components on our new sql 2005 database server before I upgrade. Then when I restore my sql 2000 database on our new 2005 server, eveything would be OK? I am not sure if my understanding is correct.
My advisor is on my local computer and I am running analysis against a remote database(on our intranet),€œSQL Server 2000 DTS Designer Components,€? should be intalled on my local computer or remote database server? what steps I should take in order to retain my dts package or transform it into something else?
I am going through upgrading our current installation of SQL 2000 to 2005. When I install 2005 it doesn't seem to upgrade the server, but only installs the server tools. Do I need to completely remove 2000 in order to upgrade? Thanks!
Initially, the articles I read suggested that doing an "in place" upgrade is the simplest path, with the drawback that if something goes wrong, I'll have to re-install 2000. And that migrating is more difficult since I'll have to recreate all the logins , security, replication ....
Then I saw a series of posts saying migration is the best ... backup & restore (or detach, reattach) the DBs to a new instance of SQL 2005. What about the systems DBs, such as MSDB and the DTS packages ?? Also, If I migrate to a new instance of SQL, how will I re-establish the connections from other servers ? They are replicating or DTS-ing to ServerA, but now SQL2005 is on ServerASQL2005 (Same server, different instance.
In the scale of things, we have fairly simple environments, although our Production database is almost 300 Gig.
If I install a new instance of SQL 2005 on my SQL 2000 server, and migrate everything from 2000 to 2005, I now have 2 instances: ServerA and ServerASQL2005. All my other servers are referencing the original instance name of ServerA. How do I resolve that problem so that communication between the other servers will still work ??
I've installed a test instance of SQL 2000 on a test box. I want to try the In-Place upgrade to 2005. I installed 2005 on the test box also, but have been unable to see how to perform the upgrade. I thought running 2005 setup again would prompt me for an instance name .... but it just tells me 2005 is already installed. I must be missing something simple.
Good Morning! I am beginning the process of moving SQL 2000 databases to SQL 2005 cluster. Has anyone already did this and is there anything I should know? We plan on leaving the SQL 2000 database up during this process and granting read only at the time. I am nervous about this as this is a high profile database in our dept. I don't even know if I know where to begin! Any suggestions are truely welcome! Thanks, /P
hi all, i have written a small checklist for my upgrades of databases from 2000 to 2005 servers. I was hoping you can take a look and see if im missing something.
TIA
1.Create backup of 2000 Database to a file group a.Full Backup b.Overwrite Existing Media Set c.To File with Extension .BAK d.Locate backup on DEV @ X:SqlServer_DataMSSQL2K5BACKUP e.Make sure Backup File Name has _for_2k5_migration.BAK at the end 2.Create Database on SQL Server 2005 System on DEV a.MYSERVERDEVELOPMENT2K5 b.Data files go to : X:SqlServer_DataMSSQL2K5DATA c.Log files go to : X:SqlServer_DataMSSQL2K5LOG d.Set Compatibility to 80 (SQL SERVER 2000) 3.Restore Backup to 2005 From File a.Options Tab – Check Overwrite existing database b.Once Restore is completed set compatibility mode to 90 (Sql Server 2005) 4.Script all Logins to 2K5 a.Go into 2000 server Security -> Logins b.Script as CREATE TO Clipboard c.Open query on 2K5 DB d.Paste and run (Database must be installed on 2K5 first)
i have not done anything about maintenece plans - is this okay as it is or am i forgetting / not knowing anything else!
First off, thanks to all for taking the time to read and respond to this question of mine. Greatly appreciated.
Let me explain my current scenario followed with the question. We currently have our VB application written and working at various client sites on SQL 2000. We did received a number of request on upgrading to SQL 2005. We did some preliminary testing in-house on SQL 2005 (backed up the 2000 database and restored on 2005 which worked just great). It is also my hope that technically, everything should work just fine since 2005 is backward compatible to 2000.
Is anybody aware of any issues with taking a 2000 database and getting it to work on SQL 2005 without any mods?
Hi Friends, I am trying to Upgrade my Sql server 2000 Enterprise edition with SP4 to Sql server 2005 Enterprise edition (Inplace upgrade). The Sql 2000 machine was just now installed and applied with SP4 and has only system databases I want to try configuring Log Shipping in sql 2000 and then perform some testing by upgrading it to sql 2005.........so thats why i went for sql 2000.The OS is Windows 2003 server with SP1 and has sufficient hard disk space. I knew that it is possible to perform Upgrade from Sql 2000 SP3 or later (Enterprise edition) to Sql 2005 Enterprise edition. But when I run the sql 2005 Enterprise edition Setup, I provide the instance name in the dialog box! and beneath it there is "Installed instances" box is present if i click it, it shows the version and edition of my sql 2000 instance as 8.00.194 which is nothing but Sql 2000 RTM version. Hence I am unable to proceed with the installation as it says to refer the version and edition upgrades are not matching as per the requirements........But if i open the query analyser for that instance and type
Code Block
Select @@Version or Select Serverproperty('Productlevel') it displays the o/p as SP4Also in the error log it shows as 8.02039 which is Sql 2000 SP4 I am a bit confused even though sp4 is installed i am unable to perform the upgrade ? Feel free to provide your valuable suggestionsadvice if any ?RegardsDeepak
I have a few databases running a sql2000 and the time as come to move them onto sql2005. Which would be the best route to take.
If I can think back to older version it was just a simply job of doing a backup in it's current version then restore in the new version? and upgrade was taken while restoring.. I could be wrong but any help !!
I am currently using sql server 2000 and I have just installed sql server 2005 in a separate box that will be used as the production server so I will be upgrading to the new database server soon.
I was reading the upgrade options for this situation and one option is to detach the 2000 database, copy the mdf and log files to the new server and attach it to mssql 2005. Another option is to recreate the tables, views, etc. and export/import the data to the new server.
I would like to ask what the best option is for this situation because I am not sure what the advantages and disadvantages of just detaching/attaching and recreating the database in sql server 2005. If I just detach/attach the database, will there be any disadvantage in the performance because the database files were created in 2000 and is functioning because of backward compatibility in 2005? Would it be better to recreate the database in 2005 and import the data from 2000 so that the database would be running in a way that is designed for 2005?
I am not really sure of the differences of these upgrade options so any ideas would be greatly appreciated.
These are the steps i plan on taking. 1. sp_help_revlogin gets logins sql 2000 2. Backup from SQL 2000 to SQL 205 3. Change database to 90 mode 4. Change database to check sum and auto stats sync 5. Rebuild Indexes on databases 6. Jobs (i will manually move over) Anything else i should run after the rebuilds
I am upgrading a sql server 2000 with sp4 (I have double and triple checked the sp) on a windows 2003 sp1 server and I keep getting the following error:
Name: Microsoft SQL Server 2000 Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check: Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
I cannot find anything saying that I need any other service pack other than sp4 for sql serer 2000. Any one ever run into this or have a clue what might be causing the installer to think I have the wrong service pack?
My name is Elizabeth. I'm currently trying to upgrade our Reporting Server to version 2005. The SRS that's currently installed on our CRM server is version 2000, and we want to upgrade that to 2005 so that we can use VS 2005 to write new reports (since SRS 2000 is not compatible with VS 2005).
So I followed the steps as on this link http://technet.microsoft.com/en-us/library/ms144267.aspx
However, I'm stuck at step# 11. As it says on the link: "If a component has a check box that is unavailable, the component does not qualify for upgrade to SQL Server 2005." I did get something like below; the check box is unavailable.
When I click on the "Detail" button on that menu, I get a message like below:
If I click on "Next" anyway from the menu, I get this error message:
I'm just wondering why it doesn't let me upgrade to 2005. The current version of SRS that's installed in our CRM server is Microsoft SQL Server 2000 Reporting Service Standard Edition. According to Microsoft link http://technet.microsoft.com/en-us/library/ms143393.aspx that edition should be upgradable.
It just crossed my mind actually; could it be because our Window Server is only using Service Pack 2, while according to that link the server has to use SP3 version or later. Please clarify me if this is the case.
Could you please help me on this one? I really appreciate it!! Thanks in advanced!!
Hello, I have sql server 2000 personal edition on my PC and I have just got a 2005 DVD. I tried to install it but it is not working. I don't know how to manage my databases. As if there is no graphical managemnt tool. The 2005 is also Personal edition. I reinstalled the sql 2000, and now I'm trying t upgrade to sql 2005, but still don't know how to do that. Please someone help me!!!!!!!!!!
Perhaps someone here can give me a hand with this...
I have three servers running SQL Server 2000. Two are running Windows Server 2000 SP4 with SQL 2000/SP4 and one Windows Server 2003 with SQL 2000/SP?(probably not 4). I copied the installation DVD to a network drive and successfully performed an in-place upgrade of the Windows Server 2003 machine. However, when I try to upgrade either of the other servers, I get an error indicating that I have a service pack problem.
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check: Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Of course, the error doesn't indicate what the service pack problem actually is. My SQL 2005 media was probably released before SP4 was released because the Setup Help indicates that you can directly upgrade from SP3, but makes no mention of SP4. I can't imagine there are any migration problems with it since I can't find any reference to it as a problem on any SQL Server sites... So... I'm left wondering what my problem really is... :confused:
Probably this question has been asked hundreds of times and yet netsearch has not generated satisfactory enough answer, at least, to me.And OK, let's assume your organization has more than 200 employees,just one measure to indicate that it's not small and data processingneeds are quite extensive (for both OLTP and OLAP).We've heard so much about concurrency support, stability andperformance. Are there any real persuasive paper out there to talkabout it? Now, let me also put it in another perspective, say, you'rea Microsoft sql server sales guy or gal for that matter for newaccounts. What you got?Thanks.
I just installed 2005 and accepted all the defaults to upgrade the default instance of 2000 as prompted. However, when I open the SQL Server Mgnt Studio, my server shows 8.0.2039 as the version which is 2000, and I can't restore a backup from another 2005 server, because it gives an error message like it still thinks its 2000. The whole installation process went fine including sp1. I looked in Services to see if there was more than one instance of SQL Server, and there isn't. Also, all my database files are still in the old 2000 (mssqldata) directory. Any ideas, solutions? Thanks.
I've inherited 6 sql server 2000 boxes. I've upgrade 3 with no trouble. now I have one that gives the message : "your upgrade is blocked because of cross-language compatibility rules. For more information about cross-language support, see the version..."
all servers are 2003, sp2. All SQL is Enterprise edition, default language is "english". even the collation order is the same. why the cross language message? Any help would be good else I'll be forced to rebuild the DB and load application again. ugly!!
