Find SQL Server Instances Across Your Network Using Windows PowerShell


As more and more SQL Server instances get installed in my organization by different departments in both servers and desktops, I need to find out where they are as I’ll be responsible for managing and administering these instances. How do I list out all the SQL Server instances across my entire organization without even knowing where they are?


You may have probably heard about the term SQL Server sprawl – the uncontrolled and uncoordinated deployment of different SQL Server instances of different editions. It’s easy to track Enterprise and Standard Edition instances since we regularly do audits for licensing purposes. But, then, there are third-party and line-of-business applications that use SQL Server Express as their backend database that get installed by people from different departments of your organization. Who knows whether these SQL Server instances sit on their desktops or on servers allocated by the IT department? And the worst part is, we DBAs get charged for administering them.

You may have used tools like SQL Ping and a bunch of other stuff in the past, some of which are highlighted in this SQL Server Central article, to discover SQL Server instances in your network. The good news is, your nightmare is about to end. Since Windows PowerShell runs on top of the .NET Framework, it can leverage any .NET Framework-based assemblies available on the machine that it is running on top of (this includes having SQL Server 2005 Client Tools with Server Management Object (SMO) to administer SQL Server instances). For this particular case, you don’t even need to have SMO installed on your client machine to enumerate SQL Server instances across your organization as the .NET Framework already includes ADO.NET 2.0. ADO.NET contains the System.Data.Sql namespace which contains classes that support SQL Server-specific functionality. One of these classes is the SqlDataSourceEnumerator which can be used for enumerating all available instances of SQL Server within your local network, even if the SQL Server service is stopped. The SqlDataSourceEnumerator class has the Instance property that gets an instance of the SqlDataSourceEnumerator, which can then be used to retrieve information about available SQL Server instances. A method called GetDataSources retrieves a DataTable containing information about all visible SQL Server instances in the network from SQL Server 2000 onwards.

And this really highlights the power and simplicity of Windows PowerShell. This task can be done in a single line of code, using the information mentioned above.


Command : [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Example :

PS C:\Users\vkarthikeyana> [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

ServerName        InstanceName         IsClustered              Version
———-             ————               ———–                 ——-
IN001VS0098      CONFIGMGRSEC           No                     11.0.6020.0
IN001VM0009     CITRIX_METAFRAME     No                     10.0.2531.0
IN001VS0012      CITRIX_METAFRAME     No                      10.0.2531.0
IN001VS0025      CITRIX_METAFRAME     No                      10.0.2531.0
IN001VM0023     AUTODESKVAULT         No                       12.0.2000.8
IN001VS0032       INSQL139                     No                       10.50.4000.0
IN001VS0023      CITRIX_METAFRAME      No                     10.0.2531.0





I have copied the above data for document purpose.


Source url:


Another tool :

which does the same job and in this site lot of free tools available.




Database Engine for MySQL

Today I come across an article database engine for MySQL. Its interesting and I brief some of them.

  1. What are database engines?
  • Also known as storage engines.
  • Software component of the database management. System that actually stores, retrieves, modifies and deletes data.

2. Why do we need different database engines?

  • Every applications has different requirement.
  • For some applications performance is major issue.
  • For some applications frequent read/write are more important than frequent updates.
  • For some application searching is major issue compared to the read/write/update.

3. Types of database Engines?

InnoDB,MYISAM,BerkleyDB etc., Every database engines has its different pros and cons.


  • Designed for database are frequently read not updated.
  • High performance and good for frequent read operations.
  • Locks table while updating.


  • Designed for database those are frequently updated not read.
  • InnoDB is transaction-safe.
  • Data Integrity is maintained throughout the entire query process.
  • Allow multiple updates on single table. Locks only row not table.

4. When to use what database engines?

  • More updates less read
  • More performance
  • Bank
  • Social networking website.


This storage engine is used for tables and not to database.

To set the default storage engine according to our requirement, go to /etc/mysql/my.cnf; (the path may vary to different distros. Here I am using Ubuntu)

Add a new line in [mysqld]

Default-storage-engine = innodb

Save the my.cnf file and restart MySQL service.

In Mysql replication Slave_IO_Running:No

Root cause: The error occur due to the master log file does not match in the slave when you query in mysql “show slave status\G” .


a. Check the master log file in mysql master by

mysql> show master status;


| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| mysql-bin.000154 | 107 | | |


1 row in set (0.00 sec)



b. Compare the log file in slave by running the command “show slave status\G” and try to set the same log file in slave mysql server.

c. Go to slave mysql server and run the below command.



mysql>CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000154';



This solves the problem…


Mysql Error: ERROR 2005 (HY000): Unknown MySQL server host ‘domainName:3306’ (11004)

Root cause of the problem:

When you connect the remote mysql server either through IP address or host name, you will get an error as mentioned in the subject.This is due to my.cnf bind address is not correctly entered.


Give appropriate host IP address or Host name in the bind address in my.cnf in the remote server.


How To Set Up Database Replication In MySQL

1. Configure The Master

a. First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out the line

bind-address = X.X.X.X host IP instead the default IP (

b. Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database test, so we put the following lines into /etc/mysql/my.cnf:
log-bin = /var/log/mysql/mysql-bin.log

c. Then restart Mysql service and login to the mysql prompt, give the following permission.

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘<some_password>’;



d.To identify mysql log file in master type the below command





| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| mysql-bin.000155 | 198 | test | |


1 row in set (0.00 sec)




e. Then quit.



2. Configure The Slave


a. create a database name test in mysql.


b. Go to /etc/mysql/my.cnf in the slave server and do the following changes.

Comment the line and type the host ip address instead the default.

bind-address = X.X.X.X host IP instead the default IP (

Also enable or uncomment the below line (server-id)in my.cnf

server-id=2 (here we should mention 2 instead the default 1, since the master is configured as 1)
master-host= (add a new entry)

c. Save the my.cnf file and restart mysql.

d. Go to mysql prompt in slave and type the command 
   mysql>show slave status\G;

The Slave_IO_Running: YES ; Slave_SQL_Running: Yes should be in the status yes.
If the IO running No; then the log file in master doesnot match the slave. To over come this issue



mysql>CHANGE MASTER TOMASTER_LOG_FILE = 'required bin file';


Now test the replication in mysql test DB by manipulating creating or inserting or deleting any table in the test DB.
It should work...

Database Startup fails with error ORA-16038,ORA-19809, ORA-00312

I am not a DBA expert, but I had an opportunity to work with DBA for the below problem raised in Oracle database. When I try to backup oracle server my backup fails. When I check with my DBA I come across this below error. I find the solution from the url

Error Description:

Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.

SQL> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 2019288 bytes

Variable Size 117440552 bytes

Database Buffers 41943040 bytes

Redo Buffers 6369280 bytes

Database mounted.

ORA-16038: log 3 sequence# 572 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread

1: ‘/oradata2/data1/dbase/redo03.log’

Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.

Root Cause: 

There was an attempt to archive the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can’t archive new online log.

Solution A:(Enough space on the drive)

1)One more check the alert log. (Not needed though) An extra check you can do in alert log which is in


SQL> show parameter background_dump_dest NAME TYPE VALUE

———————————— ———– ———————-



string /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump

$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log

 You may see the same entry is in the alert log.

 2)Check the archive destination.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 572

Next log sequence to archive 572

Current log sequence 580

So archived log destination is DB_RECOVERY_FILE_DEST.You can see the exact destination in OS by,


SQL> show parameter db_recover NAME TYPE VALUE

———————————— ———– ———————-



string /oradata2/flash_recovery_area db_recovery_file_dest_size big integer 10G

3) Increase the value of db_recovery_file_dest_size

As archive destination is full so increase the size.


SQL> alter system set db_recovery_file_dest_size=20G;

System altered.

4) Open the database now.

SQL> alter database open;

Database altered.

Solution B: Have not enough space on the drive. If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue

 $rman target /



 Solution C: Have not any backup. If you have not any recent backup then backup database to

another location and delete archivelogs. To do this,

$rman target /

RMAN>backup format ‘/oradata2/%U’ archivelog all delete input database;


Solution D: Have recent backup and only need archivelog. In this case back up the archive log to another location and delete archive log from flash recovery area. You can do this by,

 $rman target /

 RMAN> backup format ‘/oradata2/%U’ archivelog all delete input;