[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1017
  • Last Modified:

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.
0
Bob Schneider
Asked:
Bob Schneider
  • 7
  • 4
  • 3
  • +3
11 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bob SchneiderAuthor 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 MVEDeveloperCommented:
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 MVEDeveloperCommented:
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 SchneiderAuthor 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 SchneiderAuthor 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 MVEDeveloperCommented:
screen
0
 
Bob SchneiderAuthor Commented:
myst_black, where is that in your web.config file?
0
 
Bob SchneiderAuthor 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 SchneiderAuthor 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 SchneiderAuthor 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now