Archive for Server Administration

Backing Up RDS Instances (Microsoft SQL Server)

sql-bak-file

Just recently we had to decommission a RDS instance where we had “upsized” an Access database into a Microsoft SQL Server hosted on Amazon’s cloud.

This decommissioning was merely a cost-saving step to eliminate resources that were used transitionally on one of our Access-application-to-full-web-app conversion projects. However, we did want to preserve a copy of the database that we could reference locally on development machines in the future.

Overall, this procedure was surprisingly complicated, although once the right components were configured in Amazon’s ecosystem, the process went smoothly.

The Amazon document Importing and Exporting SQL Server Databases was our basic guide for this process.

 

 

 

Setting up a Windows Development Server

Recently we acquired a new client and set up a new Windows development environment for their project from scratch. Here is a profile of the process we used.

We started working with a small business based in Vancouver, Washington, USA. This was a new client for us, and they desperately needed help with their business processes. (They also requested help with their website, but that is a separate topic for a different day!)

The client was using a mish-mash of common small office/home office software products, including Adobe Professional and Microsoft Office and the processes that were being used in their business were quite convoluted. For example, the order taking process, which was needed for each new order they took, had many repetitive steps across several different programs, involving cutting and pasting and lots of duplication of effort.

For our first step, we had a consultative meeting with the client. This meeting immediately produced a tons of value in the form of actionable insights. These insights showed exactly where we should help them optimize their business processes. This optimization, when complete, will save them lots of time in the order taking process and in following up on orders. This will enable them to spend less time processing the orders. This saves costs on the staff time it takes for each order, reducing the per-order overhead, as well as allowing the order taking process to scale better. This enables them to take more orders and do more business. This a great thing for growing the business!

During the initial meeting we took detailed notes and we identified the key elements in each process. We also began to outline the solutions we would implement at each step.

This is the same basic process development that we have been doing professionally for over a decade.

Since they had a Microsoft Access database in a core role in their internal processes, we outlined a path to step from Access to SQL and then to a web-based app which we would custom develop for them. This is the same basic process development that we have been doing professionally for over a decade. This client’s use case fit nicely with the profile for this kind of work.

In order to facilitate this development process, we immediately provisioned an Amazon EC2 instance with the latest and greatest Microsoft Windows Server. Going this route is much quicker to implement than provisioning local hardware and software, and it is also less expensive, more flexible, and offers better performance out of the box. The required software licenses alone for this development environment and the related production environment would easily be several thousand dollars. Amazon has low recurring fees for their Web Services that are based on usage. Those fees automatically account for the necessary licenses for the software we use.

Since we do so much development work locally, on either Macs or Linux-based machines, I was curious to see what tools we would set up on this Windows based server for our team to share. As I was the one setting this up, I brought along many of my favorites tools. Some of them seem outdated, but they still get the job done. First I installed the truly cross-platform (consistently developed for multi-platform) tools that remain the same across the various dev platforms.

In this case these were:

As far as Windows specific tools for this dev server, they included:

We connected SourceTree to our BitBucket account. With the distributed source tracking, all of the developers on our team are able to program, test, and debug on their local machines and then push and pull accepted changes to and from the central repository.

It’s been a little while since I set up a Windows Server as a Dev environment, and I guess I was a little surprised to see myself using the same old tools. However, it gets the job done, and that’s great!

 

Compiling the MSSQL.so extension for PHP 5.3.5 on MediaTemple DV 4

We just enabled the MSSQL extension via FreeTDS on a Media Temple DV 4 VPS. Here’s a line by line of how we did it, borrowing heavily from http://www.mavrick.id.au/2012/php-5-3-6-mssql-freetds/
, but adding the particulars for the MediaTemple DV and for the particular version of PHP (5.3.5) that we were using:

Make a directory to hold these sources:

cd /
mkdir /source
cd /source

Grab the source of FreeTDS:

wget http://mirrors.ibiblio.org/freetds/stable/freetds-stable.tgz

tar zxvf freetds-stable.tgz
cd freetds-0.91

./configure –enable-msdblib –prefix=/usr/local/freetds
make && make install

For some reason these files need to be copied over:

cp include/tds.h /usr/local/freetds/include
cp src/tds/.libs/libtds.a /usr/local/freetds/lib

cd /source

Adjust for your desired version of PHP (this example is 5.3.5):

wget http://museum.php.net/php5/php-5.3.5.tar.gz
tar xvfz php-5.3.5.tar.gz
cd php-5.3.5

cd ext/mssql
phpize
./configure –with-mssql=/usr/local/freetds
make

Now, when we first ran the make command we had to deal with a type redefinition in

nano /source/php-5.3.5/ext/mssql/php_mssql.h

Copy the extension to the proper directory… You can find out what directory it is with this command:

php -i | grep extension_dir

In our case it was /usr/lib64/php/modules.

cp modules/mssql.so /usr/lib64/php/modules

Edit php.ini file (this is the global one):

nano /etc/php.ini

Add:

extension=mssql.so

Then restart your web server:

/etc/init.d/httpd restart

and done!


Other PHP MSSQL Resources:

http://php.net/manual/en/mssql.setup.php

https://github.com/jamestkirk/devbox/wiki/install-mssql-php-extension

http://www.robert-gonzalez.com/2009/02/18/building-the-php-ms-sql-server-extension-from-source-on-ubuntu-810/

https://docs.moodle.org/22/en/Installing_MSSQL_for_PHP

http://howtogetitworking.com/2008/02/26/how-to-install-mssql-extension-for-php-on-unix/

http://www.linuxquestions.org/questions/linux-server-73/how-to-enable-mssql-on-linux-589956/

 

UPDATED:

(For reference:)

I compiled the pdo_dblib.so extension… but it looks like PHP is compiled with –disable-pdo. So I went back to mssql_connect as a connection method…

Logging in as the user to the bash shell, I could telnet to the database server’s ip at port 1433.

From the command line as well, I could run tsql (the diagnostic tool that comes with FreeTDS) and connect to the server, where I was able to verify that the hostname, instance, and dbname lookups are correct. I could even run T-SQL statements and query the database successfully from there.

I configured the FreeTDS conf file to have [appname] as a reference to the server, and then accessed via the following code in:

putenv("FREETDSCONF=/etc/freetds.conf");
$hostname = 'appname';
 $username = 'username';
 $password = 'password';
$databasename = 'database';
//connection to the database
 $connection = mssql_connect($hostname, $username, $password)or die("Unable to connect to $hostname");
 echo "
Connected to MSSQL
";
mssql_select_db($databasename,$connection);
$result = mssql_query("SELECT TOP 1 field from table");
$row = mssql_fetch_array($result);
echo("
Result from sample query: ".$row[0]."
");

Which outputs the expected:

Connected to MSSQL
Result from sample query: x

 

​This was the missing puzzle piece:

 putenv("FREETDSCONF=/etc/freetds.conf");

 

Apparently FreeTDS looks in /usr/local/etc by default, not /etc.