Adding a linked server is an easy task which can be done at any point of time. I was under this same assumption until I came across a strange scenario.

Here is the scenario for you all.

Use SSMS to create a New Linked Server and choose provider as ‘Oracle provider for OLE DB’

This one small step can bring the whole SQLServer to grinding halt if certain steps are not followed.

SSMS will be in frozen state during the New linked server creation step and eventually queries againt the databases will run forever.

Detailed explanation of the scenario

You wanted to create a new linked server with provider as Oracle provider for OLE DB.

Step 1 - The initial step to start of the whole process is to install the Oracle client on the SQLServer machine for which you want to add the linked server.

Step 2 – Oracle client installation will not force a reboot after the install is completed.

Step 3 – Without rebooting the server or recycling the SQLServices you will open SSMS and connect to the instance and see that the oracle provider is available.

Step 4 – You will use SSMS wizard to create a new linked server and will choose the provider as Oracle provider for OLE DB.

As soon as you select the provider you can notice that new linked server creation window will be in frozen state.

Any queries made against the databases will run indefinitely.

This situation will continue till you force a reboot of the machine. You cannot even recycle SQLServices as the service will fail to restart.

Think about a situation  -  You are doing this on a live production environment during normal business hours. As mentioned earlier this simple task can slowdown everything and can bring the server to a halt.In case the server is business critical, then rest is history(Start updating the resume !)

Right way of doing things

You will need to schedule changes like these ideally during a change window. You will need to recycle the SQL services or even bounce the box soon after the Oracle client is installed. This is a very important step. Once the server is up and running after the reboot you can either use SSMS to create the linked server or can opt to use sp_addlinkedserver system stored procedure. This time there wont be any slowness or performance related issues.

Conclusion

This simple scenario forced me to think twice and made up my mind to stick to one policy ‘Changes to business critical servers occurs only during a dedicated change window period’ no deviations what so ever.

Thanks for reading and keep watching this space for more.