Brutal Migration To Windows Server 2012

I am beside myself.  Sites have been down for 18 hours now.  All files have been moved.  All data has been moved.  There seems to be some problems with my sql server connection string...it was working fine on my windows server 2008 and seems to be problematic on 2012.

Here is my 2008 connection string:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI11;Server=localhost\SQLExpress;Database=xxx;Uid=xxx;Pwd=xxx;"

The file structure on that server was:
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS

The file structure on my new server is:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER

The error highlights the conn,open line and says it can't find the instance.  I tried replacing SQLExpress with SQLSERVER but that didn't help.

Could really use some resolution here.
Bob SchneiderCo-OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prequel_serverCommented:
try just localhost alone: conn.Open "Provider=SQLNCLI11;Server=localhost;Database=xxx;Uid=xxx;Pwd=xxx;"

Are you sure you have Sql server native client 11 installed and not 10?
you can verify this in Add/Rem programs or look at the connection options in a new ODBC connection.

What is the full name and instance you see when you connect to the database in Mgmt Studio?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Looks like you were using SQL Server Express in your original setup and now you're using the  full version of SQL server. Please confirm which version you're using along with the version number
0
David Johnson, CD, MVPOwnerCommented:
Did you re-attach your databases. The actual default instance is MSSQLSERVER for the full SQL Server product
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Bob SchneiderCo-OwnerAuthor Commented:
1) yes I am using sql server 2012 on Windows Server 2012 R 2
2) Yes I attached the dbs
3) Mgmt Studio Says Sql Server 11.0.3153
4) I have MIcrosoft Sql Server 2012 Native Client
4) I will try to run it with the abbreviated connection string.

Related to all of this,
1) This is a non-db site with mostly straight html and it renders fine: http://rosemountata.com/
2) This one throws an error that I believe is database related: http://www.my-etraxc.com/
3) This one doesn't appear to be able to be found on the new server at all: www.etraxc.com/

Thanks for the help so far.  My kingdom, such as it is, for the one who can get this figured out for me.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you may also want to try connecting via IP address, meaning put the IP address as the database parameter.

here's a list of valid connection strings to connect to SQL Server 2012

I wasn't able to connect to #2 listed above, it just keeps saying "Loading", so it could be a dns issue, I'm assuming that was changed during your migration? If so, the changes may not have propagated yet. (after a few more minutes, it finally said there was an internal server error, so it's probably NOT a dns issue. it would be helful to see the exact error message though)

For #3, did you set it up as it's own application in IIS like you did for the first 2 sites? did you look in the event logs?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I made the same switch in March.  It was not as easy as expected but I ran the new server as a 2nd server for a month before actually making the dns switch and even then I had a week of issues to work out.

Are you using a panel (Plesk)?  

My connection string on sql server 2012 with windows server 2012 looks like

myConn = "Provider=SQLOLEDB; datasource=localhost; Database=db_name; Uid=db_username; Pwd=db_password"

Some things to look out for is that you are on the port you think you are.  And make sure the port you are running for sql is open in your firewall.    You may need to use sql configuration manager to see what port your instance is using.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Here is the link to the sql configuration manager. I forgot to include it  http://msdn.microsoft.com/en-us/library/ms177440.aspx  You will see on the left there are some other choices.   When I set up one of my testing servers, I have 3 versions of sql server installed and you can only use one.
0
Bob SchneiderCo-OwnerAuthor Commented:
Here's the error from the failed request logs:
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Here is the modified connection string:
conn.Open "Provider=SQLOLEDB; datasource=72.52.136.29; Database=xxx; Uid=xxx; Pwd=xxx;"

Tried to use the sql config mgr as indicated in the link but when I right click on the ip addresses I don't get a "properties" option.  Not sure what port I should be looking for....
0
Bob SchneiderCo-OwnerAuthor Commented:
On top of all of this, on my sites in IIS, at various points, I get the lock error indicated in the attachment
lock-error.docx
0
David Johnson, CD, MVPOwnerCommented:
you could make the web.config read only.  is there a reason why you put a picture into a docx file rather than just posting the image?
0
myst_blackCommented:
We upgraded to sql server 2012 from 2008 r2 and had to change our connection string to the following: (obviously changed the names, etc. we used the IP address etc., localhost\Instancename should work too,

this is in our web.config file:   <add name="DB_CONNECTION" connectionString="server=IP.IP.IP.IP;uid=username;pwd=password;database=databaseName;" providerName="System.Data.SqlClient" />
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
screen
0
Bob SchneiderCo-OwnerAuthor Commented:
myst_black, where is that in your web.config file?
0
Bob SchneiderCo-OwnerAuthor Commented:
The top line appears to be the source of many of my issues in my web.config file:

    <httpErrors errorMode="Custom" defaultPath="/500err.asp" defaultResponseMode="ExecuteURL">
      <remove statusCode="404" subStatusCode="-1" />
      <remove statusCode="500" subStatusCode="-1" />
      <error statusCode="500" prefixLanguageFilePath="" path="/500err.asp" responseMode="ExecuteURL" />
      <error statusCode="404" path="/404err.asp" responseMode="ExecuteURL" />
    </httpErrors>

Why is that?  How do I change it?  Is there a workaround?
0
Bob SchneiderCo-OwnerAuthor Commented:
I have no idea how to run this script...now I am getting max log file truncate issues.  This is supposedly how i fix that but  is there a command prompt that I need to use?

cd /d "%windir%\system32\inetsrv"
appcmd set config /section:sites -siteDefaults.traceFailedRequestsLogging.maxLogFileSizeKB:1024
0
myst_blackCommented:
hello, just got back here. The connection string is in a connection string section directly below our close appsettings tag:

  </appSettings>
  <connectionStrings>
    <clear />
    <add name="DB_CONNECTION" connectionString="server=111.111.111.111;uid=userName;pwd=Password;database=DataBaseName;" providerName="System.Data.SqlClient" />
    <add name="OTHER_DB_CONNECTION" connectionString="server=222.222.222.222;uid=UserName2;pwd=Password2;database=DatabaseName2;" providerName="System.Data.SqlClient" />
  </connectionStrings>
0
Bob SchneiderCo-OwnerAuthor Commented:
Just a quick update that we are back online now.  The problems were two-fold:
1) The connection string...thanks for all the help on that.
2) When I moved the web files from the old server to the new I left the web.config files in there.  That was causing all of my "Lock" issues.

Learned a lot from you all.  Thanks so much!
0
myst_blackCommented:
Glad you are out of the woods now!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.