Category Archives: SQL and Database Management

Posts related to databases, SQL code and database management.

Creating a Hyper-V Failover Cluster on Domain where you have Limited Rights (NetID)

 

We are using Windows Server 2012.

Question: “Datacenter or Standard?”

It’s a licensing question.  To my knowledge (I’m sure this can change at any time) Standard and Datacenter editions of Windows Server 2012 are the same software, with the same features.  The difference is that Datacenter (which costs more) will let you install an unlimited number of virtual machines, all running Windows Server, without requiring you to purchase windows licenses for any of those Virtual Machines.  Standard Edition allows you to create two Windows Server virtual machines without any additional cost. Virtualizing server you already have the license for, or linux computers, doesn’t count against your two.

My understanding is that there also a free version of Windows Server, which contains neither any kind of graphical user interface, nor any Windows licenses for Virtual Machines.  This could be well suited for Linux Virtualization, if you don’t need the GUI.

Prerequisites:

– To create a Hyper-V Failover Cluster, all hosts are running Windows Server 2012.

– They need to be joined to a domain.  I used netid.washington.edu, where I have a designated OU which I can join computers to already.

– They need proper, resolvable DNS names.

– Special Admin Access:

By my IT Department, and possibly in your own limited/restricted access domain setup, I am instructed to use a special Domain Admin user to run the Active Directory Users and Computers program (by using right-click, Run as a Different User) and pre-creating the computer in the domain, in my down designated OU.  Running the Create Cluster Wizard actually adds a Failover Cluster “computer” to the domain—you can’t pre-create this “computer” in the domain like I’m instructed to do normally.  The solution is to right-click, Run as a Different User the Create Cluster Wizard, but that Domain Admin user also needs to be Administrator on the machine for this to work.  We’ll go over how to do this in the instructions.

Join the Servers to the Domain

Follow the instructions in this post to join your computers to the NetID Domain:

Add a Computer to the Netid Domain using a Designated OU from UW-IT

Add SADM_ netid to the administrators group.

Right-click Start, click Computer Management

Click Local Users and Groups

Click Groups

Right-click Administrators

Click Properties

Click Add…

Put your special domain joiner user here.  For style points, I chose to use a special UW Group that UW-IT created called netid\u_windowsinfrastructure_fmdata_ouadmins (fmdata is my designed OU)

Click Check Names to make sure you spelled it right, then Click OK, OK

Install the Hyper-V and Failover Software/Roles

In the Server Manager, click Manage->Add Roles and Features

Click Next

Select Role-based or feature-based installation and click Next

Make sure the server is highlighted and click next.

Check Hyper-V if this cluster is going to run Virtual Machines.  If we’re just creating a clustered storage device, leave this unchecked.

Click Next

Check Failover Clustering

Click Add Features

Click Next

Click Install

Create the Failover Cluster

Remember, we can’t just click Server Manager -> Tools -> Failover Cluster Manager because we need to run as our special user that can edit the Domain/Designated OU which we got from UW-IT (sadm_yournetid).  SO:

Click Start

Start Typing “Failover Cluster Manager” when you see that windows finds it, right-click and click Run As a Different User (if this doesn’t appear, try holding left-shift while you right right).

Username: netid\sadm_yournetid

Password: your special sadm password

Click OK

In the Failover Cluster Manager click Validate Configuration…

Click Next

Enter the name of the first computer and press enter.  For example: fmdata-vmnas1.  Windows populates the box below with the complete dns name.  Run an nslookup on this name, if it doesn’t work, you may as well abort the cluster creation now and get that sorted out, because the cluster manager WILL fail.

Enter the name of the second computer you want in the Cluster and press enter (do another nslookup, add more hosts, etc,etc)

Click Next

Select Run all tests (Recommended) and click Next

Click Next, be prepared to wait a while – coffee run is advised.

Click View Report and review this.

Make sure that Create the cluster now using the validated nodes… is checked k4cjqhe.

Click Finish (Create Cluster Wizard Opens)

Create Cluster Wizard

Click Next

The INTERESTING PART!  You might be tempted to simply enter a computer name here and hit next.  Nope!  Enter something like this:

CN=fmdata-testclus,OU=fmdata,OU=Delegated,DC=netid,DC=washington,DC=edu

CN= is the computer name that will be added to the domain.  All of ours start with our designed OU name- (eg fmdata-)

OU= is here twice, notice.  The first is the name of our designated OU, the second one… just says Delegated and should be there for some reason.

DC= is here three times!  Our Domain Controller is netid.washington.edu, for some reason instead of periods you need to separate each URL part with another ,DC= if your domain controller was simple mydc.com, I think you would simply have two DC=’s, as in: DC=mydc, DC=com.  Exciting stuff, right?

Click Next, click Next

Click Finish

Click the Arrow to expand the item which is the dns name of your new cluster.

Click Nodes

Whoa, there are your two computers, each having the status “Up” …Great job!

 

SQL Diff Query Comparing Two Tables

In this example, we’re comparing the dataset on our local machine to one on Azure, so we need to use a linked server to connect Azure to the SQL install on our local machine.  You could easily adapt this technique to two similar tables in the same database however.

The Components

  1. We need a linked server, because the queries are all run locally but will need to both the remote Azure server, and the local server.
  2. We use a query to load a copy of the data we want to compare from Azure into a local temp table.
  3. We also use a query to load a copy of the data we want to compare from the local server into a local temp table.
  4. The queries only include data fields we want to compare. We’re stripping out things like id’s because we just want to see which rows have changed data. If we include id fields, then adding one row would cause hundreds to change.
  5. Finally is the actual “diff” query, which actually looks pretty simple.

Part 1: Drop If Exists

We want to drop the temp tables if they exist, so that we are sure we are comparing the most recent copies of data.

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like ‘#remote_temp%’) BEGIN
DROP TABLE #remote_temp;
END;
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like ‘#local_temp%’) BEGIN
DROP TABLE #local_temp;
END;

Part 2: Create Temp Tables

To make a temp table you need to define the data types for each of the columns.  Only make columns for the data fields you are comparing and selecting in the next part, not id columns.

CREATE TABLE #local_temp
(
Report_Name NVARCHAR(255),
Report_Label NVARCHAR(255),
…etc…
);

CREATE TABLE #remote_temp
(
Report_Name NVARCHAR(255),
Report_Label NVARCHAR(255),
…etc…
);

Part 3: Populate the Temp Tables

I’m not going to reproduce the queries here, since they are long and specific to my dataset.

The interesting bit here is that the queries are the same, except the FROM statement. When selecting from Azure, we are using our linked server code (Creating Linked Azure Server) so that we just need to refer to the table name with “azure.” in front, and we’ll select the data from Azure instead.

INSERT INTO #local_temp
SELECT
l_report.report_name AS Report_Name,
l_report.report_label AS Report_Label,
…etc…
FROM isnap.dbo.report AS l_report
WHERE blahblah = ‘blah’;

INSERT INTO #remote_temp
SELECT
r_report.report_name AS Report_Name,
r_report.report_label AS Report_Label,
…etc…
FROM azure.isnap.dbo.report AS r_report
WHERE blahblah = ‘blah’;

Part 4: The Diff Query

The query is simple, we just take:

  • The local data set minus the remote data set, and name it local (rows that are only on the local data set)
  • The remote data set minus the local data set, and name it remote (rows that are only on the remote data set)
  • and union those two things together so when we look at the results of the diff query:
    • If a record is deleted, a copy of the record will show up with Stage = Remote only, no row for Local
    • If a record is being added, a copy of the record will show up with Stage = Local only, no row for Remote
    • If a record is being updated, there will be two rows, one with Local (the new version) and one with Remove (the old version)
(SELECT
     'Local' as Stage, *
     FROM (
          (SELECT * FROM #local_temp)
          EXCEPT
          (SELECT * FROM #remote_temp)
     ) OnLocal)
UNION
(SELECT
     'Remote' as Stage, *
     FROM (
          (SELECT * FROM #remote_temp)
          EXCEPT
          (SELECT * FROM #local_temp)
     ) OnRemote)
ORDER BY Report_Name, Category_Name, Subcategory_Name, Description_Name, Subdescription_Name, Year_Name, Stage

Creating Linked Azure Server in Visual Studio SQL Server Express

I use Visual Studio to load data onto a SQL Server Express instance on my local PC. I believe that this SQL server installed with Visual Studio.

First, I used Microsoft SQL Server Management Studio to log in to my local instance with the following credentials. You should be able to run this SQL inside Visual Studio too, however.

Server name: (localdb)\Projects
Credentials: Windows authentication

Then execute the next two statements:

EXEC sp_addlinkedserver
@server=’Azure’,
@srvproduct=”,
@provider=’sqlncli’,
@datasrc='<remote db url>’,
@location=”,
@provstr=”,
@catalog='<a good name here, say: bob>’

and:

EXEC sp_addlinkedsrvlogin
@rmtsrvname=’Azure’,
@useself=’false’,
@rmtuser='<remote db username>@<remote db url>’,
@rmtpassword='<password>’
EXEC sp_serveroption ‘Azure’, ‘Collation Compatible’, true;

Now, if you have the isnap database selected on your local pc, you can issue this query:

select * from localtablename;

to query the table on the local pc.

and this query:

select * from azure.bob.dbo.remotetablename;

(where “bob” is the good name you assigned in the first statement you executed)  to query the table on azure!

Convert a tab-delimited file to SQL inserts

This is useful when I’m migrating data from one system to another. It uses the quotesplit code I talked about here: Parsing CSV data files with PHP, using quotesplit.

You want your source file to be tab delimited, with the header row containing the database field names that you are going to load each piece of data into. I use Excel to prepare the file. Then you run this file using the PHP interpreter at the console, and redirect the output to a .sql file. In the Mysql console, source your new .sql file. For convienience, I’ve attached the file tabdelim-to-sqlinsert.zip

You can use this for comma-separated or pipe-separated or whatever as well, if you change t in your call to quotesplit.

< ?php
  ini_set('display_errors',1); 
  error_reporting(E_ALL);

  # User config variables:
  $Filename = 'sourcedata-tabdelim-file.txt';
  $dbname = 'databasename';


  ####################################################
  function format($in)
  {
    $out = trim($in);
    if (($out == '') || ($out == 'NULL')) {
      return 'NULL';
    } elseif (preg_match ('/bd{1,2}/d{1,2}/d{4}b/', $out)) {
      # incorrectly formatted date detected (ie 10/31/2012 or 12/31/9999)

      $datetimeparts = explode(' ', $out);
      $dateparts = explode('/', $datetimeparts[0]); # consider date part only
      $out = $dateparts[2] . '-' . $dateparts[0] . '-' . $dateparts[1];
    }
    return "'" . $out . "'";
  }
  #######################################
  function RemoveArrayElement($array, $removeKey)
  {
    unset($array[$removeKey]);
      foreach ($array as $value)
        $return[] = $value;
    return ($return);
  }
  #######################################
  function DealWithMultipleSurroundingQuotes($splitter, &$getstrings)
  {
   for($x = 0; $x < count($getstrings); $x += 2) //foreach even key
   {
      if (!stristr($getstrings[$x], $splitter)) //if splitter is not in row
      {
         if (trim($getstrings[$x-1]) == '') //if previous row is empty
            //remove previous row
            $getstrings = RemoveArrayElement($getstrings, $x-1);
         else
            //remove current row
            $getstrings = RemoveArrayElement($getstrings, $x);

         return false;
      }
   }
   return true; //Function finished successfully!
  }
  #######################################
  function quotesplit( $splitter=',', $s, $restore_quotes=false )
  {
   # First step is to split it up into the bits that are surrounded by quotes
   # and the bits that aren't. Adding the delimiter to the ends simplifies
   # the logic further down

   $getstrings = explode('"', $splitter . $s . $splitter);

   while(!DealWithMultipleSurroundingQuotes($splitter, $getstrings));

   # $instring toggles so we know if we are in a quoted string or not
   $delimlen = strlen($splitter);
   $instring = 0;

   while (list($arg, $val) = each($getstrings))
   {
      if ($instring == 1)
      {
         if($restore_quotes)
         {
            # Add string with quotes to the previous value in the array
            $result[count($result)-1] = $result[count($result)-1]. '"' . addslashes(trim($val)) . '"';
         } else {
            # Add the whole string, untouched to the array
            $result[count($result)-1] = addslashes(trim($val));
         }
         $instring = 0;
      } else {
         # Break up the string according to the delimiter character
         # Each string has extraneous delimiters around it (inc the ones
         #  we added above), so they need to be stripped off
         $temparray = explode($splitter, substr($val, $delimlen, strlen($val)-$delimlen-$delimlen+1 ) );
         while(list($iarg, $ival) = each($temparray))
            $result[] = addslashes(trim($ival));
         $instring = 1;
      }
   }
   return $result;
  }

  ####################################################
  $file = fopen($Filename, 'r');
  if($file == false)
  {
    print 'Error in opening file';
    exit();
  }
  $Filesize = filesize($Filename);
  $filerow = fgets($file, $Filesize);
  $headerrow = quotesplit("t", $filerow);
  $numcols = 0;

  while($filerow = fgets($file, $Filesize))
  {
    $row = quotesplit("t", $filerow);
    print 'INSERT INTO `' . $dbname . '` (';
    $first = true;
    foreach ($headerrow as $field)
    {
      if ($field == '') {
        # Do nothing
      } elseif ($first) {
        print '`' . $field . '`';
        $first = false;
        $numcols+=1;
      } else {
        print ', `' . $field . '`';
        $numcols+=1;
      }
    }
    print ') VALUES (';
    for ($i=0; $i

Set MySQL @variable for hostname user grants

The user grant syntax in mysql is problematic if you want to use a variable to set the hostname when you are creating users in a mysql script. The @ notation means the start of a variable normally, but it’s also used in the mysql grants… so if you try to do this:

SET @hostname='localhost';
GRANT SELECT, SHOW VIEW ON *.* TO 'username'@'@hostname' IDENTIFIED BY 'pass';

You get the error:

Lookup Error - MySQL Database Error: Malformed hostname (illegal symbol: '@')

Or if you take the single quotes out, you just get a syntax error.

The solution is to use a placeholder when you do the create user grants, then later replace all instances of the placeholder with the variable.

/* uncomment to set appropriate environment */
SET @hostname='localhost'; /* development */
/* SET @hostname='testing.hostname.com'; */
/* SET @hostname='production.hostname.com'; */

GRANT SELECT ON db_name.table_name TO 'username'@'env' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT ON db_name.* TO 'username2'@'env' IDENTIFIED BY 'pass';
GRANT SELECT ON *.* TO 'username3'@'env' IDENTIFIED BY 'pass';
/* ... */

UPDATE mysql.user SET host = @hostname WHERE host = 'env';
UPDATE mysql.db SET host = @hostname WHERE host = 'env';
UPDATE mysql.tables_priv SET host = @hostname WHERE host = 'env';
FLUSH PRIVILEGES;

Errors while trying to connect to Microsoft SQL Server using FreeTDS on Apache/PHP

If you get one of the following errors:


odbc_connect(): SQL error: [iODBC][Driver Manager]Specified driver could not be loaded, SQL state IM003 in SQLConnect

Ensure the path to the driver is correct. We are pointing to a file called libtdsodbc.so.


odbc_connect(): SQL error: [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist, SQL state 08S01 in SQLConnect

Ensure that the Port number is correct.


odbc_connect(): SQL error: [FreeTDS][SQL Server]Unknown host machine name., SQL state 01000 in SQLConnect

Ensure that the Address is correct. Use only dns name, no /database name at the end.


odbc_connect(): SQL error: [FreeTDS][SQL Server]Login failed for user 'username'., SQL state 42000 in SQLConnect

Ensure that the username and password is correct. If you have a domain user, make sure to enter the username as 'domainusername'. (If the username is in double-quotes, be sure to escape the slash).


Understanding Views in MySQL and MS SQL

What is a View?

A View looks and behaves just like a table, but it is generated. Most commonly, you create a view for a person or application to have access to a part of your database. You might simplify the data to just what people need to do adhoc queries, or you might provide an application the data it needs in a read-only view.

What’s in a View?

Would a view by any other name show the same data? It might, depending on the query that is used to dynamically construct the view. How do we see that query?

MySQL

SHOW CREATE VIEW databasename.viewname;

Microsoft SQL

EXEC sp_helptext 'databasename.viewname'

Migrating from MySQL to PostgreSQL

First, create the tables in your new Postgres database. I used a program called Navicat. You should create the tables by hand because the field types are different in MySQL and Postgres.

Use the following command to get mysqldump to create a dump file of the database that you can upload. You can also use Navicat to run the script.

mysqldump databasename tablename -v --compatible=ansi,postgresql --complete-insert=TRUE --extended-insert=FALSE --compact --default-character-set=UTF8 -u username -p -r "c:sharespostgres.sql"

Setting up PostgreSQL on Red Hat Enterprise Linux

As a regular user, or as root if you leave off the sudo bits:

sudo yum -y install postgresql postgresql-server php-pgsql
sudo /sbin/chkconfig postgresql on
sudo /sbin/service postgresql start

This is setting up the postgres user. Enter a password you'll remember when prompted.

sudo passwd postgres

After this command, enter that password again. Now you'll be logged in as the new postgres user.

su - postgres
psql
create user gpsdata;
create database gpsdata owner gpsdata;
control+d

The following makes it possible to access the database from another computer.

vi /var/lib/pgsql/data/postgresql.conf 
add the line:
listen_addresses='*'

vi /var/lib/pgsql/data/pg_hba.conf
add the line:
host    all         all         192.168.3.0/24        trust

control+d
sudo /etc/init.d/postgresql restart

Now you've just got to open port 5432 on your firewall.

sudo vi /etc/sysconfig/iptables
add the line:
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

sudo /etc/init.d/iptables restart

Order By with Union’s in MySQL

I’ve you’re just starting to play with a Union, then you might have gotten the error message:

#1221 - Incorrect usage of UNION and ORDER BY

You might be thinking: Whoa, hang on there! Do you mean MySQL can't order a Union?! Oh, but it can! First little issue I had was in realizing that you can put parenthesis around the queries. So phrase your query thusly:

"(SELECT ...) UNION (SELECT ...) ORDER BY ..."

Just be sure that your Order By includes only the column name. Table.Column is going to get you another error!