Question

Best Practices using SQL Aliases when performing Package & Deployment from DEV->TEST->PROD

  • 3 February 2016
  • 1 reply
  • 446 views

Over the years we learn that Sharepoint, K2 and other systems save their configurations in Databases. however sometimes perform a change in the Configuration Database is not as easy as we would imagine. Mutliple components from a complex solution got buried in multiple places, especially when a cluster or multi-farm server solution gets involved.

 

For those cases I would said is a "Best Practice" the usage of SQL Alias in the servers that will connect to the Database. allowing always reference to the main Database Instance via the SQL Alias. The main benefit start with the simple DB upgrade which changes would just mean change the SQL Alias entry from the original DB to the new upgraded Database Instance or sometimes even moving Databases to a new hardware without have to go to all the trouble of affecting the configuration of our software.

 

Our case is basically 3 K2 Servers, [ DEV ] , [ TEST ] and [ PROD ]. each one has his own SQL Alias to their respective Database [ K2DEVDB ], [ K2TESTDB ] and [ K2PRODDB].

 

So after a couple months getting familiar with K2 Technology we are ready to perform a  "Package & Deployment" from DEV to TEST. following the K2 Documentation run the configuration wizard.... and ooopss a SQL Error appear in the second line of the deployment process. SQL Error 40

 

Turns out that "Package & Deployment" wizard try to look for the same DB Reference... but since we are in a totally new environment there is no such SQL Alias with the same name. so as a workaround we add another SQL Alias entry, with the same name used in the DEV environment but  in this case in the TEST server pointing to the TESTDB instead of the DEVDB. and voala. it worked beautiful. 

 

I'm not sure how will turn out now if I perform a "Package & Deployment" now from the TEST server prior to move to PROD. need to figure that out, surely another SQL Alias entry will be needed in PROD to perform the Deployment what I'm not sure is if this is from TEST or again from DEV. in case is form DEV. 

 

As a conclusion is good idea the usage of SQL Alias for your installations considering the flexibility that this brings in moments of migrations, upgrades, and "Pacakge & Deployment". just keep in mind that SQL Alias must be created for both 32 adn 64bit clients, this way every single piece of software will recognize and work properly.

 

Dino.

 

 


1 reply

Maybe this is old post but i will like to add some valuable info about SQL Error 40. Other members can find this usefully.

First of all error 40 is related with connection error. This error appears mostly when SQL Server does not allow remote connections, Firewall Settings or TCP/IP is not enabled.

 

How to fix error 40 on SQL Server?

  1. Check SQL Server is running
  2. Make sure that Allow Remote Connections is enabled on sql server properties
  3. Check if TCP/IP is enabled
  4. Configure to allow SQL Server in Firewall Settings
  5. Check for working fine SQL Server Browser
  6. Make sure that you can ping the SQL Server

The explained above steps here can help the people to troubleshoot this sql error.

 

Thnx

Reply