I’ve spent a fair bit of time trying to get an ecommerce package written in classic ASP to run well using a network connection to a SQL Server 2008 Express edition. These are my findings which may help someone.
Don’t settle for the standard connection string you used to use.
The ADO connection string provided worked however tweaking this gave a significant increase in through put
Initially we had a choice of
strconn= “DRIVER={SQL Native Client}; Server=” & varServerIP & “; Database=” & varDatabaseName & “; UID=” & varUserName & “; PWD=” & varPassword
or
strconn= “DRIVER={SQL Server}; Server=” & varServerIP & “; Database=” & varDatabaseName & “; UID=” & varUserName & “; PWD=” & varPassword’
The first string failed with a connection error, being SQL Server 2008 you needed to use {SQL Server Native Client 10.0} as the driver name. Using this worked quite well however if you use varchar(max), nvarchar(max), varbinary(max), xml, udt or other large objects you will not get these fields showing up in the results as SQL Native client converts the cursor to a static one making these unavailable. (This Library Article refers – opens in new window)
The second worked although response times were atrocious
The Rules to follow
Don’t mix you metaphors!
You can use OLE DB provider terms in the connection string for an ADO connection and it will work most of the time however in the background the system is deciphering what you mean which in turn impacts how quickly it responds, where possible use the correct connection term. i.e. UID and pwd will work on an ADO connection User ID and password will work better. (this Library page refers to the correct terminology)
Be Explicit
If you are using a connection to a SQL server that is not on the same server and shared memory either reorder the client protocols in SQL Server or specify the connection protocol, the previous link details this however to highlight and emphasise I’ve reproduced the relevant bit here:
The complete syntax for the Address keyword is as follows:
[protocol:]Address[,port |pipepipename]
protocol can be tcp (TCP/IP), lpc (shared memory), or np (named pipes). For more information about protocols, see Choosing a Network Protocol.
If neither protocol nor the Network keyword is specified, SQL Server Native Client will use the protocol order specified in SQL Server Configuration Manager. [My emphasis]
port is the port to connect to, on the specified server. By default, SQL Server uses port 1433.
Thus as we were using a tcp connection we preceded the ip address with ‘;tcp:’
Again if you do not specify this the server has to test what connection is going to work, thus more time is consumed identifying the connection method required.
The correct provider for an ADO connection to a SQL Server 2008 is ‘SQLNCLI10’ use it!
Don’t waste your time with Network Packet size unless you are trying to squeeze every last bit of speed out of the system (in which case rewrite the application/web site in a modern code form!)
Define DataTypeCompatibility ADO works best if set to =80
Final connection string that worked:
strconn= “Provider=SQLNCLI10; Data Source=tcp:ipaddress,post_noSQLExpress;DataTypeCompatibility=80;MARS Connection=True; Initial Catalog=databasename;User ID=SQL username; Password=SQLUserpassword;”
The instance string (i.e. sqlexpress) appeared to assist in responsiveness thus I would suggest it is included every time.
If you are using pre-existing ASP code then very little will be gained from including the MARS connection=true as your code will be designed to close the connection on a single use basis. To gain advantage you may need to alter the code to not close the connection until all the sql interaction has completed.
