How to find on which node SQL Server instance was running before restart?

 

In situation, where SQL Server instance running on a node is restarted usually after Windows Operating System patching or for some other reason, there may be need to find to which node the SQL Server instance was running on before.

Here is the solution for this;

By default whenever SQL Server instance restarted on Clustered Environment, on SQL Server Error Log it will post the message below;

The NETBIOS name of the local node that is running the server is 'ClusterNode1'.

So, making use of the xp_ReadErrorLog we can query the ErrorLog and find the details.

-- To know the list available Error log files exec xp_enumerrorlogs 1 -- Search the available error log files exec xp_ReadErrorLog 0, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 1, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 2, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 3, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 4, 1 , 'NETBIOS name of the local node' exec xp_ReadErrorLog 5, 1 , 'NETBIOS name of the local node'

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

 

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

Linked Server is the solution for this.
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

A linked server definition specifies the following objects:

· An OLE DB provider : is a DLL that manages and interacts with a specific data source

· An OLE DB data source : identifies the specific database that can be accessed through OLE DB

Basics of Linked Server Configuration:

Advantages of Linked Server:

· The ability to access data from outside of SQL Server.

· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

· The ability to address diverse data sources similarly.

Pre-requisite

· Install Oracle Client

· Configure tnsnames.ora

Example

STG21.SIVA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000)) ) (CONNECT_DATA = (SERVICE_NAME = stg01.siva) )

Validate tnsnames.ora entry

On Command Prompt Tnsping STG21.SIVA

Notes:

Here we are going to use Microsoft ODBC for Oracle as the OLE DB Provider.
The STG21.SIVA is going to be OLE DB Data Source pointing to the Oracle Database STG01.SIVA

Open up ODBC

Select System tab

Click Add

Select Microsoft ODBC for Oracle

Click Finish

Click OK, OK

Connect to SQL Server Management Studio

Right click select new Linked Server

Click OK

Test Connection

Right click select Test connection

Validation:

Just run the sample query

exec ('select user, sysdate from dual') at STG21_SQLSVR54

Output look like;

USER SYSDATE SQLSVR07 2012-09-25 01:37:02.000