Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Managing your active SQL Server instance list using PHP


By:   |   Last Updated: 2018-08-14   |   Comments   |   Related Tips: More > Database Administration

Problem

What SQL Server instances do you manage?  What environment is each one in? Can you add or remove instances from your master list easily?  In this tip, we will look at a simple way to manage your list of SQL Server instances using PHP and SQL Server.

Solution

In this tip some PHP code is discussed to help you manage a list of SQL Server instances in a SQL Server database table. We will show how to sanitize your input and user parameters to insert data safely into your database.

Scope

This is not designed to be an internet facing solution. Keep this on your internal network and we recommend you turn off the web site when it is not in use.

Design

I chose a simple, one page, design that has three sections. First, add an instance, second display the list with the ability to archive an instance and third recover an archived instance. They are the three main tasks when managing a list of instance names.

manage sql server instance list

Database Design - Tables

Three tables will do for this project. After a few design changes I found a separate Environment table helped.

USE [Admin]
GO

CREATE SCHEMA [Instances]
GO

CREATE TABLE [Instances].[Active](
   [Instance] [nvarchar](2048) NULL
)
CREATE CLUSTERED INDEX [ClusteredIndex_Instance] ON [Instances].[Active]([Instance] ASC)
GO

CREATE TABLE [Instances].[Environment](
   [Instance] [nvarchar](2048) NULL,
   [Environment] [nvarchar](2048) NULL,
   [Comments] [nvarchar](max) NULL
)
CREATE CLUSTERED INDEX [ClusteredIndex_Instance] ON [Instances].[Environment]([Instance] ASC)
GO

CREATE TABLE [Instances].[Archived](
   [Date_Stamp] [datetime] NULL,
   [Instance] [nvarchar](2048) NULL,
   [Comments] [nvarchar](max) NULL
)
CREATE CLUSTERED INDEX [ClusteredIndex_Instance] ON [Instances].[Archived]([Instance] ASC)
GO

Why PHP?

When studying for my degree in Information Technology I studied C, C#, C++, PERL, Java and PHP. I concluded that when it came to simple administrative purposes PHP was easy to use. C and C++ was too low level, PERL too cryptic, Java while easy to use was not built with Web pages in mind, so that left PHP.

Understand the activity of each layer

The PHP generates the HTML. The HTML builds FORMS that have ACTIONS associated to them. The ACTIONS cause T-SQL to be applied to the database engine which pushes or pulls data from the underlying database.

Step 1 - Install PHP

Place PHP on your own laptop or desktop. That way you know when it is shut down. Download PHP7 to any folder.

Step 2 - Install the PHP Drivers

Microsoft guide on SQL Server PHP drivers.

Step 3 - PHP7 Has a built-in simple IIS web server

As PHP has a simple IIS web server. Keep it simple and use that. These commands get it going:

REM Go to the folder that holds the PHP executables
cd c:\YourFolder
cd PHP7
cls
php -S localhost:8000

Step 4 - Security

This project it not designed to go on the internet. It is designed for DBA use alone. Even in that case we will still sanitize all input. To do that we will use this simple method to escape strings in SQL Server from stackoverflow.

Here is an explanation of the sanitize function from StackOverFlow.

function sanitize_all_input( $RawData ){
# Idea taken from 
# https://stackoverflow.com/questions/574805/how-to-escape-strings-in-sql-server-using-php

// Check there is data to process 
// Note this test is a SIMPLE test only. Suitable for inhouse systems.
        if ( !isset($RawData) or empty($RawData) ) return '';

        // Purely numeric data is not a concern.
        if ( is_numeric($RawData) ) return $RawData;

        // Remove these control characters that will break things. 
        // They do not display in browsers.
        $non_displayables = array(
            '/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
            '/%1[0-9a-f]/',             // url encoded 16-31
            '/[\x00-\x08]/',            // 00-08
            '/\x0b/',                   // 11
            '/\x0c/',                   // 12
            '/[\x0e-\x1f]/'             // 14-31
        );

       // Loop through the expressions and perform a regular expression search and replace. 
       // http://php.net/manual/en/function.preg-replace.php
       foreach ( $non_displayables as $regex )
                    $RawData = preg_replace( $regex , '' , $RawData ); 
       // Don't think too long about how that line works! 				
       // Finally add a single quote to any existing single quotes in the input.
       $CleanData = str_replace("'", "''", $RawData );
			
       return $CleanData;
}		

Parameters

It is possible to use parameters when inserting data into the database.

// Track the instance and its environment
$SQL_String_Instance = "insert into [Admin].[Survey].[Environment] select ? , ? , ? ";
$Parameters_All = array($Instance_To_Process, $Environment, $Comments);
$stmt = sqlsrv_query( $Connection, $SQL_String_Instance, $Parameters_All);

However, I find that makes things less easy to read. The code below is easier to read and debug, but does mean you need to sanitize all input and that is a good idea all the time.

// Track the instance and its environment
$SQL_String_Instance = "insert into [Admin].[Survey].[Environment] 
select '$Instance_To_Process', '$Environment', '$Comments' ";
$stmt = sqlsrv_query( $Connection, $SQL_String_Instance );

But where should you sanitize the input? It is a good idea to do that as soon as you can. So that means in the script processing for the form.

<form action ="Example_Recover_Instance.php" method="POST">

In this case inside the file titled: "Example_Recover_Instance.php". Here is the PHP script inside that file to do that.

// Initialize the variable
$Instance_To_Recover = '';
// Check if we have data
if ( ! empty( $_POST['Instance_To_Recover'] ) ){
// Sanitize and use that data
$Instance_To_Recover = sanitize_all_input($_POST['Instance_To_Recover']);
}

Step 5 - Functions

I place all functions into a file titled: Example_Common_Functions.php.

It is possible to build object oriented classes in PHP, however that is out of scope for this tip. When I title functions, I place a purpose as a comment as well. That allows me to scan the file and display the lines beginning with ‘function’ and get the function name and purpose displayed.

function connect_to_survey(){ // Purpose: return the connection resource to the survey database.

Step 6 - Format T-SQL

This is a simple formatter for T-SQL. I like to see the T-SQL running in the background. It allows me to copy the T-SQL out to SSMS and test it.

function format_tsql( $SQL_String ){ // Purpose: 
    // Commence a new line on all these keywords
    $BreakOn=array(
            "SELECT","Select","select",
            "PRINT ","Print ","print ",
            "END","End",
            "BEGIN","Begin",
            "FROM ","From ","from ",
            "INSERT ","Insert ",
            "IF ","if ","IFF(","IFf(","iff(",
            "GROUP BY","Group by ","group by ",
            "LEFT JOIN ","Left Join ","left join ",
            "RIGHT","Right","right",
            "WHERE ","Where ","where ",
            "ORDER BY ","Order by ","order by ",
            "DECLARE ","Declare " ,"--" );
   foreach ( $BreakOn as $term ) 
            { $SQL_String=str_replace($term,strtoupper('</br><font color="blue">'.$term.'</font>'), $SQL_String); }

    // Build the final formatted SQL string.
    $Formatted='<code>';
    // Sanitize TSQL just in case!
    $Formatted.=sanitize_all_input($SQL_String);
    $Formatted.='</code><hr>';
    return $Formatted;
}

Step 7 - List of files

This is a list of files and their purposes. Click on the link to download them. Try to read and understand them prior to using them.

File name Purpose
Example_Add_Instance.php Add an instance script. Without using parameters.
Example_Add_Instance_Using_Parms.php Add an instance script. Using parameters. (This is not used by the main form.)
Example_Common_Functions.php All the functions that help.
Example_Delete_Instance.php Remove an instance from the Archived table.
Example_Manage_Instances.php The main page with the active list.
Example_Recover_Instance.php Move the instance from the Archive table to the Active table.
Example_Remove_Instance.php Archive an instance name.

Using the list of instances

This how you would typically use that list of instances that we manage?

Here is an example for PowerShell:

# File name : Loop Instances Example.ps1
# Reference : https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

# Clear the display
Clear

# Select some instances to process
$SQL_String     = "Select [Environment] , [ACTIVE].[Instance]
                   FROM [Instances].[Active] [ACTIVE]
		            left join [Instances].[Environment] [ENVIRONMENT]
		            on [ACTIVE].Instance= ENVIRONMENT.Instance
                   Where Environment='3 Development'
                   Order by Environment, [ACTIVE].Instance"

write-host "Running this query: $SQL_String"

$InstanceList = Invoke-Sqlcmd -ServerInstance "DBA_ADMIN_2017" -Query $SQL_String -Database "Admin"

$Count=0
foreach ( $Instance in $InstanceList ){
    $Instance.Environment  + ' ' +   $Instance.Instance 
    $Count=$Count+1
     # Add your own commands here
     # See the Next Steps links below for ideas.

    }
write-host "Processed $Count instances" 

Conclusion

This tip is to help the DBA manage an instance list. Resist the temptation to publish your web pages over the internet or even the intranet.

Next Steps
  • Check out tip 3549 by Jeffrey Yao for ideas on how to use your list of Instances.
  • See tip 2660 by Diana Moldovan on iterating through SQL Servers.
  • Check out how to locate your SQL Server instance in tip 2013 by Edwin Sarmiento.
  • Check out why listing all your SQL Server does not work in tip 2057 by K. Brian Kelley.
  • Check out more PHP commands available to you.
  • Download the scripts for this tip.


Last Updated: 2018-08-14


get scripts

next tip button



About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools