Currently we use SQL 2K SP4 and snapshot replication with a Central Publisher with Remote Distributor toplogy.
I am looking to upgrade or migrate our SQL servers to SQL 2005 and was wondering what is the best way to do this for our replicated architecture?
Is the best way to run the SQL 2005 Upgrade on all 3 servers (publisher, distributor, subscriber) and should it automatically upgrade the servers including the replication components? Is there anything i should consider/watch out for when doing the upgrade and it involves replication (namely snapshot replication)?
I am not sure if I should be posting this question in the setup forum. I apologise if this is the wrong forum. My question is simple and straightforward.
1). What are the things that I should consider while upgrading Replication from SQL 2000 to SQL 2005 Assuming
Case A : My SQL Server 2000 is the Publisher.
CASE B: My SQL Server 2000 is the Distributor
CASE C : My SQL Server 2000 is the Subscriber.
Any documents, whitepapers or KBs or related posts would be helpful. MS SMEs please share your thoughts.
We have a server instance on SQL Server 2005 SP2 build 3042. We have a 32 bit x86 server. We attempted to upgrade to SP2 build 3054 KB934458. And we got the following error as stated in the Summary.txt file.
Code Snippet ********************************************************************************** Product Installation Status Product : SQL Server Database Services 2005 (MSSQLSERVER) Product Version (Previous): 3042 Product Version (Final) : Status : Failure Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log Error Number : 29538 Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again. ----------------------------------------------------------------------------------
The client tools and workstation components were successfully installed. The server is still reporting build 3042.
Here is the portions of the HotFix.log file.
Code Snippet 05/12/2008 09:19:09.041 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log 05/12/2008 09:19:09.072 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller" 05/12/2008 09:19:09.103 Registry: Cannot read registry key value "Debug" 05/12/2008 09:21:29.382 MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again. 05/12/2008 09:22:33.678 MSP returned 1603: A fatal error occurred during installation. 05/12/2008 09:22:33.724 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(
1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:
Error messages: The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965) Get help: http://help/MSSQL_REPL-2147200965 New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989) Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.
Please help otherwise I'm forced to step back to MSDE.
I'm try to upgrade SQL Server 2005 Evaluation with tuned up and worked merge replication.
- I'm insert disk with licensed SQL Server 2005 Enterprise, start installation - in process, indicate instance for installing, in list of options appears I see option "ugrade" - All passes fine and go on:
After upgarde I'm get completely broken merge replication!
Moreover, such feeling that certain strange troubles with SQL Agent: all points context menu in Replication Monotor - disabled and if I try to start replication manually through jobs - too failed - with some error "agent shutdown" !
I'm try to build "clear" situation: install in Virtual PC Windows 2003 Ent SP1, SQL 2005 Eval and setup my test replication and upgrade SQL Server to Enterprise version - and get same trouble too!!!
What the best way to uprade my evaluations sql servers with replication to licensed version SQL Ent?
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.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
We are performing a SQL 2005 SP1 upgrade from SQL 2000 on our Windows 2003 SP1 Standard Edition.
When we run the upgrade, we got a error "Upgrade Advisor Return -1" as a pop-up window.
When we run the Upgrade Advisor separately, we get this error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------ Program Location:
at System.Xml.Schema.SchemaInfo..ctor() at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable) at System.Xml.XmlReaderSettings.get_Schemas() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh() at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
We are in a lost for solutions. We do hope to hear from anyone asap...
We are running a test upgrade form sql 2000 standard edition 32 bit to sql 2005 developer edition 32bit. Followed through all the steps and specified the account(SA priveleges and currently used by the 2000 version) and is the local admin on the box for the services and the setup seemed to move fine, except for when it got to the point of installing database services - This is the error message that I got:
MSSQLServer could not be started. Verify you have sufficient priveleges to start system services. The was no choice but to hit cancel and then the set up progressed with a final message of 'SEtup failed'.
Here is a portion of the error log: Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. Database Mirroring Transport is disabled in the endpoint configuration. Starting up database 'master'. Converting database 'master' from version 589 to the current version 611. Database 'master' running the upgrade step from version 589 to version 590. Error: 946, Severity: 14, State: 1. Cannot open database 'master' version 589. Upgrade the database to the latest version. System Task Aggregate global counters produced an error that was not handled. Major: 9, Minor: 46, Severity:14, State:1 Error: 928, Severity: 20, State: 1. During upgrade, database raised exception 15151, severity 16, state 1, address 01C4C50B. Use the exception number to determine the cause. Error: 15151, Severity: 16, State: 1. Cannot find the endpoint 'TSQL Local Machine', because it does not exist or you do not have permission.
I have a client whose DBA upgraded their SQL 2000 database to SQL 2005. They still have it running in 8.0 compatibility mode, for various reasons, and would like me to help them develop a migration strategy to get them to full 9.0 compatibility.
My question is: can I run the Upgrade Advisor on a SQL 2005 engine database running in 8.0 compatibility mode and get anything useful? I already know there will be some issues with existing DTS packages but it would be nice to catch any other issues in advance.
I know that the Upgrade Advisor was run before the upgrade took place but to my knowledge the report was not saved and none of the recommendations were followed.
I tried creating an alias to the server to get it to connect to analyze the server but it will not recognize the SQL 2000 server as a valid server to analyze. I can use the alias to connect in EM or SSMS. Any ideas? The server is not clustered and is at SP4. I've connected to several others in my environment but this one is causing me grief!
I upgraded my SQL 2005 Workgroup 2005 to SP2 and subsequently could not get the SQL Server service running. Turns out this was a problem because I had created separate folders for my data and log files. The distmdl.mdf and mssqlsystemresource.mdf files were in my data folder and distmdl.ldf and mssqlsystemresource.ldf were in my log folder. During SP2 upgrade a new distmdl.ldf and mssqlsystemresource.ldf were created in my data folder. I deleted the distmdl.ldf and mssqlsystemresource.ldf files in my log folder and then moved the newly created ones in my data folder into the log folder and all is well now.
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
I am currently in the process of upgrading a server from 6.5 to 7.0 However I am using the sp_dbcmptlevel stored procedure to leave the database in 6.5 compatability mode.
I have a few urgent questions on this:
- Would leaving a database in 6.5 affect database performance,ability to use web based development, datawarehousing?
- If I wanted to upgrade to SQL Server 2000, would I first have to switch compatability to 7.0 and then upgrade to 2000?
I would really appreaciate anyone with knowledge on these issues.
Currently running SQL Server 6.0 with NT4.0 1]Is it possible to upgrade from 6.0 directly to 7.0? How. 2]I have one database with SQL Server 6.0 on my Production Box & I have another Development box running SQL server 7.0. I want to upgrade my 6.0 database(ON ONE SERVER A) to 7.0 Database(ON DIFFERENT SERVER B) runnung on development box.(I donot want to run Object transfer as The database are quite big & object transfer takes for ever) I want to transfer database and also NT Logins & SQL Server Logins/users.
Hi, I am a Sql server DBA. My new company planning to UPGRADE from AS/400 (DB2) to Sql Server 2000/sql 7.0. Can any one suggest me how i have to plan the upgradation from as/400 to sql server 2000/sql 7.0. suggest me some books , white papers or technical papers. Any suggestions how to convert as400 files to sql server tables.
Hi at here i have a question regarding upgrade SQL server 6.5 To 7.0. Currently , i have a production system running in VB 6.0 with 6.5 SQL server. If i plan for running in 7.0 , is it any impact with my VB coding ? Can i move directly ? Thank !
We currently have a stand alone production server SQL 2005 STD + SP2 and plan to upgrade to clustered SQL 2005 Ent + SP2. What's the proper way to upgrade ? I want the existing server instance use Ent engine.
Hi, I have question about orphan users - I currently have all my database files in the SAN and I am going to replace my server with a new server. My question is if I switch the databases to the new server and keep the same server name as the old one would I get orphan user in my database? Below is what I am planning to do 1) Detached all databases from SQL server 2) Shut down the old server 3) Rename the new server with the same name as the old server 4) Attached all the databases to the new server. I am assuming since all my database users are in sysusers (I am still using SQL 2000) then I should be ok. Is this true? - I can't verify on my own, because I don;t have hardware to try out, :-(, but could some one let me know if you done things like this before? Thanks in advance
I am currently running a the Free version of SQL Server - however I know that in the future I will need to upgrade to the full SQL Server 2005 Licenced verision.
Is it possible to upgrade from one to the other?
Are there any known issues? For example will it keep any current databases as they are etc...
We have 3 MSDE databases (3 seperate servers) which we need to make into one Sql 2000 server (new server). How is this done? Can't find anything clear about this.
We are going to upgrade one of our SQL Server 2000/Win200 instance to SQL Server 2005/Win2003. Currently, the SQL Server 2000 which contains about 30 database. This is my plan:
Install SQL Server 2005/Win2003 on a new instance Backup SQL Server 2000 database instance (how do I back all 30 database at the same time?)
Then do a Restore from the SQL Server 2005 instanceIs this a good method or using the "Copy Database Wizard" is better method?
Upgrade Advisor could not detect SQL Server components on the server.
===================================
The network path was not found. (mscorlib)
------------------------------ Program Location:
at Microsoft.Win32.RegistryKey.Win32ErrorStatic(Int32 errorCode, String str) at Microsoft.Win32.RegistryKey.OpenRemoteBaseKey(RegistryHive hKey, String machineName) at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetClusterInfo() at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.DetectComponents() at Microsoft.SqlServer.UpgradeAdvisor.FormDetectComponents.Detect()
A client of mine was using SQL Express and soon realised this doesn't meet his needs performance or database size wise. He also realised that SQL Server 2005 Workgroup came free with his R2 version of SBS.
So in essence, I have been trying to find the Microsoft recommended way to upgrade from Express to Workgroup. I have not been able to find a step-by-step guide and to add to my confusion, when I run the work-group installer it tells me to run the installer with a switch which I have gone ahead and done but when selecting components it has all of them defaulted to blanked out.
Surely the process for an in-place upgrade should not be so technical as I am finding it ? To my mind, the natural progression would be to upgrade express to a better capable version of 2005 and should be as easy as pie.
Anyway, I am basically looking for a step-by-step guide on how to upgrade to get it done right the first time as opposed to upgrading and than realised some remenants of Express are still larking about causing trouble.
We are currently using VWD and Sql Server Express 2005. There is now SSE SP1 available (as well as another version with additional software). So, I have some questions: 1) Is the only difference between SSE and SSE SP1 just the addition of Management Studio Express? 2) Can I just upgrade to SSE SP1 separately, (It installed automatically when I installed VWD), and will VWD still work correctly with all the databases I have already created? 3) We are considering upgrading to full Sql Server in the near future. I have heard that there are problems if we have Management Studio Express installed and then try to install full Sql Server. Is this true?
I'm currently running SQL Server 6.5 on an old NT box. I just got a shiny new server with SQL Server 7 on it, so I'm itching to switch over to the new server, but I'm going to need to shut down my website while the upgrade occurs, and I need to know how long it's going to take. I have about 40GB of data in 200 tables in 15 databases. Can anyone estimate how long the upgrade wizard will take to complete its operations?
