SQL Server Admin 2014 :: Ensuring Upgrade Advisor Is Running Properly?

Oct 30, 2015

I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).

I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?

The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.

If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc. DB is running in Compatibility mode 90.

View 4 Replies


Getting Error While Running Upgrade Advisor On Remote Virtual Server

Jul 31, 2007


While connecting remote SQL server through upgrade advisor I am getting the below errors:

Error: Unable to connect to server. Reason: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Pls let me know if anyone has faced this issue and know how to fix.

Thanks in advance


View 1 Replies View Related

Running Upgrade Advisor Against Production

Nov 30, 2006

Is it safe to run the Upgrade Advisor against production servers?

Thanks in advance!

View 1 Replies View Related

SQL Server Admin 2014 :: CheckDB Running For Two Days

Jun 3, 2015

We carried out an in-place upgrade on our production server on Saturday - going from 2008 R2 to 2014.

We had tested this method out in dev/test and pre-production with only minor post issues to fix.

However, on production we had an issue whereby checkdb was hitting 100% CPU and caused overnight processes to hang. The checkdb statement was terminated and disabled by a colleague at 1 am.

Since then we have restored this database to a dev server and ran checkdb against it with no_infomsgs and all_errormsgs but it still hasn't finished since Monday morning!

The database is just over 800 GB and whilst checkdb was crippling the cpu, logical reads are less than one. However, sp_whoisactive is showing that it has done 56 million reads so far and this number increases periodically so it looks like it keeps going back to re-check the database with a deep dive.

Also, on a different environment, we ran check table statements and one of them took over 9 hours for a single table but came back clean (see attachment).

We need to wait for the output but the database is still in use in production and the mess will just get worse if it is indeed corrupted.

View 5 Replies View Related

SQL Server Admin 2014 :: Upgrade Adviser Report Identifies Proxy As Deprecated

Apr 16, 2015

I ran the Upgrade Adviser Report on a Server and it identifies the Proxy's as being deprecated.

What action is needed?

Object Type: ProxyObject Name: sa-apro-cms
Object Type: ProxyObject Name: sa-apro-payroll
Object Type: ProxyObject Name: sa-pi-sql-agent

View 0 Replies View Related

SQL Server Admin 2014 :: Unattended Upgrade Fails On Missing Registry Entry?

Jun 23, 2015

I'm trying to do an unattended upgrade of 2014 RTM to 2014 SP1.

It's my first attempt at an upgrade configuration file, and its failing with missing registry entry for database engine service and replication service.

Error in summary.txt is:

The registry key SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.MSSQLSERVER2495Setup is missing

That's a valid error, as the registry only has an entry for:


Am I missing something obvious here, or how can this be resolved?

View 9 Replies View Related

SQL Server Admin 2014 :: Running Multiple Instances On 2 Nodes

Apr 12, 2015

I'm looking at installing 2008R2 and 2014 side by side, then using Mirroring to provide HA for the 2008R2 instance and AoHA for the 2014 instance. I'd be using the same two physical servers for both the Mirroring pair and the AoHA pair.

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Open Data File When Running Agent Job

Apr 30, 2015

I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.

We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).

Here what I have done so far:

I created the database

Created the file and successfully imported data into it using the Import Data feature.

I saved the SSIS package

Scheduled an Agent Job for this package to run at certain time,daily

At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.

Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.

View 9 Replies View Related

SQL Server Admin 2014 :: Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!

Note: I cannot use "go" in my query!

/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[Code] .....

View 1 Replies View Related

SQL Server Admin 2014 :: Running Frequently Transaction Log Backup During Integrity Check DB

Jul 6, 2014

Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...

View 2 Replies View Related

SQL Server Admin 2014 :: Multiple Database-collation Running On One Server?

Mar 30, 2015

We are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.

Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS

Our new server (2014) has the standard collation Latin1_General_CI_AS

Both collations have CI and AS

From experience I know different databases can reside next to eachother on the same Instance.

The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....

The application the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).

using different databases using a different collation running on the same instance?

View 5 Replies View Related

SQL Server 2005 Upgrade Advisor

May 8, 2008

Hi All,

Is there a way to print out reports of the errors and warnings created by the
SQL Server 2005 upgrade advisor?

View 1 Replies View Related

Microsoft SQL Server Upgrade Advisor

Feb 5, 2008

Hi there

Is there any issues to point the Microsoft SQL Server Upgrade Advisor to the SQL2K production environment during normal hours? Any issues that you know of? Or anything that I should to know about this tools?


View 2 Replies View Related

Upgrade Advisor Against A SQL 7 Cluster Server

Aug 17, 2006

Hi all,
i am in the process of upgrading my clustered SQL 7 to a new cluster with SQL 2005.

I tried to run Upgrade advisor against the SQL 7 cluster but i am not able to make it work.
I checked prerequisites on my client machine, checked if my account has administrator privileges on thew cluster and everything seem fine.
I tried to run UA using node name, virtual server name (as suggested in the help), IP address (both node and virtual server ones) and even SQL 7 instance name (!) with no result.

Are there any issue about upgrade advisor against a SQL 7 cluster ? or am I missing something about Upgrade advisor??

thanks for your help

View 1 Replies View Related

SQL SErver 2005 Upgrade Advisor

Oct 31, 2007


Got a question about one of the results on the upgrade advisor report.

When to Fix: Before
Description: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

It is looking like some type of permission problem but I cannot find anything about this specific issue.

I am running the advisor from my local machine, on which I am a local administrator, against a remote server where I am just a typical user.

Is this an error that would be corrected if I were to run the upgrade advisor logged into the server as a local administrator? I'm trying to determine whether there is anything that needs to be done with this issue.


View 3 Replies View Related

Error Installing SQL Server 2005 Upgrade Advisor

Dec 30, 2005

I'm getting a Windows Installer error when attempting to run the Upgrade Advisor setup package (SQLUASetup.exe). The error message reads:

"This installation package cannot be installed by the Windows Installer service. You must install a Windows service pack that contains a newer version of the Windows Installer service."

The machine is running Windows 2000 sp4. Both the .net framework v2 and Windows Installer 3.0 have been installed.

Any suggestions?


View 4 Replies View Related

Minimum Permissions Required By The SQL Server 2005 Upgrade Advisor

Feb 15, 2008

Hi all,

What are the minimum permissions required by the SQL Server 2005 Upgrade Advisor (UA)? I could not find it in the documentation.

Obviously being a local Administrators Windows group and a member of sysadmin SQL Server role will do the trick.

But will being a member of only the sysadmin SQL Server role be enough? I know that the UA does want to read the registry.

Running it under just sysadmin generates the following type of errors:

Database Server
Requested registry access is not allowed.

Database Server
Requested registry access is not allowed.

Database Server
Requested registry access is not allowed.

Database Server
Requested registry access is not allowed.

Database Server
Requested registry access is not allowed.

Database Server
Requested registry access is not allowed.

Database Server
Requested registry access is not allowed.

The issue then is whether these are significant or not. If the UA is only reading the registry to determine if SSAS, DTS, etc is installed then that is not important. But if it is affecting the end result because it cannot read critical information from the registry that is another matter.


View 1 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

SQL 2005 SP1 Upgrade Error For Upgrade Advisor

Jan 19, 2007


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...

Greatly appreciated for any kind assistance.


View 1 Replies View Related

Can Not Run SQL Server 2005 Upgrade Advisor On Server That Does Not Use The Standard Port

Jun 7, 2007

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!



View 1 Replies View Related

Upgrade Advisor Could Not Detect SQL Server Components On The Server

Oct 6, 2006

Hi I run Upgrade Advisor and i get this error


Upgrade Advisor could not detect SQL Server components on the server.


The network path was not found.

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()


Windows 2000 SP4

SQL 2000 Standard

.Net 1 & .Net 2 installed

All windows updated installed

Anyone with any ideas

Thanks in advanced


View 3 Replies View Related

Upgrade Advisor Post-upgrade

Dec 6, 2006


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.

Any help would be greatly appreciated.



View 1 Replies View Related

Error On Upgrade Advisor

Jun 8, 2006

Hi there,

I tried to run Upgrade Advisor on two SQL 2000 servers, but the wizard keeps failing when it reaches 22% while analyzing the Database Engine.

I downloaded the latest version, but still receive the same error...

Error: "SQL BPA command line has encountered a problem and needs to close"

Both servers are Windows 2003 Enterprise Edition SP1 with SQL 2000 Enterprise Edition SP4.

Does anyone have an idea what I can do to fix this problem.


View 38 Replies View Related

Upgrade Advisor Issue

Oct 12, 2007


I have a SQL server 2000 in development. it is running as an instance (servernameinstancename) with a TCP port configured. I can not connect to this server thru upgrade advisor wizard as well as by using command line utility. Can some help me understand how can I connect to sql server 2000 name instance and run upgrade advisor on it.

Here are the steps I have taken:

created config.xml as below:

It does not allow me to put port number and can not connect to server.

I get following error:

Unable to connect to server. Reason: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

But it is not a sql server 2005 system and I can connect to this server via SSMS or SQL EM.


View 2 Replies View Related

Can't Connect With Upgrade Advisor

Sep 13, 2006

I'm having trouble connecting the Upgrade Advisor to a SQL 2000 box at a client. Here's the setup:

1> I'm running UA on a Virtual PC image

2> I can run UA against other SQL machines on the same domain

3> I can register and administer the SQL server I'm having trouble with in Enterprise Manager

4> I have SA rights on the SQL box I'm having trouble with.

5> The problem is when entering the server name and clicking "detect," it errors out with "The network path not found (mscorlib)."

6> I've tried server name, FQDN and IP address.

This is a fairly convoluted layout, so there could be a firewall issue that I'm not aware of (but I don't think so).

Any help would be appreciated!


View 2 Replies View Related

SS2005 Upgrade Advisor

May 29, 2008

I'm trying to migrate/upgrade some databases from 2000 to 2005 and am having a problem. Apparently, the Upgrade Advisor can't analyze a SS2000 database if it is in a named instance. (see below)

This problem occurs because the SQL Server 2005 Upgrade Advisor cannot connect to the named instance of SQL Server 2000.

The SQL Server 2005 Upgrade Advisor uses information that the SQL Server Browser service returns when the SQL Server 2005 Upgrade Advisor tries to connect to an instance of SQL Server 2000. However, the SQL Server Browser service cannot return the correct information about the connection request. Therefore, the connection fails.


Doesn't this make the UA tool useless for named instances on SS2000? Are there any plans to correct this issue? Or, is there a workaround available?


View 7 Replies View Related

Upgrade Advisor Report

May 20, 2008

I ran Upgrade Advisor on a SQL 2000 server and I received warnings and errors for DTS packages and MetaData Services. Since there are no DTS packages and I don't see anything when I click on Meta Data Services Package, but I get an error when I click on Meta Data...

"An error occurred while trying to access the database information. The msdb database could not be opened."

Why is this error occurring when there are no DTS packages?

View 5 Replies View Related

Upgrade Advisor Issue Not Clear

Jun 15, 2006

I'm testing the upgrade from SQL Server 2000 to SQL Server 2005 and
first of all I ran the Upgrade Advisor.
I received one issue that I cannot understand. The desctiption is:

Unexpected szVal value from parser - expected sp:name but received

There is also indicated FTCATALOGNAMERESTRICTION and I suppose this is
the "affected object" even if I cannot find it to the DB!

Do you have any hint for me?
There is really few documentation on the net about using the Upgrade

Thanks in advance,

View 1 Replies View Related

SQL 2005 Upgrade Advisor Report

Dec 27, 2007

In my SQL 2005 Upgrade advisor report I am getting following description and its importance is critical. I am unable to figure out this.

Item has already been added. Key in dictionary:'mydatabase.LightQueryTool' key being added 'mydatabase.LightQueryTool'

View 1 Replies View Related

Upgrade Advisor Error I Don't Understand

Jan 11, 2007

I'm carrying out an investigation into how much effort will be involved in upgrading our database from SQL Server 7.0 to SQL Server 2005. I've downloaded the Upgrade Advisor and run in against a copy of our production database. Most of the issues are trivial and I understand how to resolve them apart form one (to me) meaningless one:

When to Fix : Before

Description : Class Not Registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))


That's all the information it gives, except for a "This issue is resolved" checkbox. Anyone got any idea what the problem is, or how I go about tracking down the cause?

I'm running the analysis tool from a W2K Professional Client, connecting to SQL Server 7.0 running on an NT4 Server using Windows authentication. The account I'm connecting with is a member of the System Administrator group in SQL Server and is a local admin on the client, and a member of the Users group on the server.

Any pointers appreciated.



View 2 Replies View Related

Access Is Denied In Upgrade Advisor

May 24, 2006

Hi all,

I have run the SQL Server 2005 Upgrade Advisor against a default instance of SQL Server 2000 server running on Windows Server 2003.

One thing I need to fix before I upgrade is:

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)


I'm not sure what this means or what I need to do before I can upgrade.

Does anyone have a clue on this one.



View 8 Replies View Related

How To Slove Upgrade Advisor Issues

Nov 18, 2007

Hi All,
when i am migrating pckg dts to ssis before that i am checking that pckg with upgrad advisor. upgrad advisor report shows me following things i need to upgrade.
1. SQL Server 2000 Meta Data Services Packages are not support::Existing meta data services packages should be moved to sql server storage or structured storage files before you upgrade sql server 2005 does not install support for sql server 2000 meta data services therfore package migratio wizar willbe unable to access meta data services packages after upgrade in the absence of the SQL server 2000 client tool.

2.SQL server 2000 DTS designer components are required to edit DTS Packages

pl.provide solution for above error msg


View 2 Replies View Related

Upgrade Advisor + DTS + Named Instances

Jun 4, 2007

I am after a bit of advice about running the Upgrade Advisor tool on a server that has more than 1 SQL Server instance. My particular focus is DTS on each server.
I tried running the tool, setting up an XML file per SQL Server (including named instances). An example of the XML is I used is included below):
<Configuration> <Server>SQL01</Server> <Instance>INSTANCE01</Instance> <Components> <DataTransformationServices /> </Components></Configuration>

When the tool runs, folders get created for the relevant named instances, but the DTS runs against the default instance only. In other words, the default instance DTS.xml file gets overwritten several times.
Does anyone know if the Upgrade Advisor tool can handle multiple SQL instances on the same server, particularly in realtion to DTS?
I had thought this might have been a limitation as the options in the Report Viewer are "Server" then "Instance or Component", when I am really after "Server", "Instance" AND "Component".

Any suggestions welcome.

Thanks in advance, Paul

View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved