Category Archives: Project Management

Browser Screen Icon with a large X at the center

IT Project Management Failure: 3 Proposed Causes

IT, Project Management, System, , , , , ,

Introduction

This article first highlights the misuse of the Project Management Triangle as a metric of success. Recognising that the very term “success”, and “failure”, can be subjective the author instead proposes generalised, objective and unambiguous examples of failure as a starting reference point. With these examples of failure serving as a foundation, three general deficits in project management are proposed as potential root causes, for IT project failure.

Project Management Triangle Misuse

The Project Management Triangle (also called the triple constraint, iron triangle and project triangle) consists of three points; cost, time and scope (or features). These points are argued to have proportionate relationships with each other. For example, a project can be completed faster by increasing budget and/or cutting scope. Similarly, increasing scope may require increasing the budget and/or schedule. Lowering the budget available will impact on schedule and/or scope. These trade-offs between the cost, time and scope create constraints which are said to dictate the quality of the produce. However stakeholders often misconstrue staying within the constraints of the triangle, while delivering a project, as a measure of success instead of, as intended, a determinant of quality.

As a demonstration of the unsuitability of the triangle as a metric of success consider the following. Would a self-build home delivered over budget, behind schedule and outside the original specifications be considered a failure? No, not for those who took on such a daunting endeavour, and survived the process, having brought into existence the home of their dreams. This is an example of a project where Atkinson (1999) might suggest the criteria for success existed outside of cost, time and scope.

So to define three significant causes of project failure it is first necessary to settle on unarguable features of project failure. It is important to note at this point that a project must have navigable obstacles and manageable risks. For instance an IT project cannot be considered a failure if an unnavigable obstacle was introduced, an example being new laws that prohibit online gambling that scuttle an online gambling platform that was in development. Similarly an IT project cannot be considered a failure if unmanageable risks were encountered such as the parent company collapsing due to financial irregularities not connected to the project.  

With those points in mind the following statements are proposed as clear examples of project failure:

  1. The project exhausted necessary resources with no or unfinished deliverables.
  2. Delivery was too late and the deliverables are no longer needed or soon to be obsolete.
  3. Deliverables are not fit for purpose or of relative value.
  4. The costs exceeded the relative value generated by the deliverables.
  5. The project killed the parent organisation.

With examples of failure defined above the following section proposes management level causes of IT project failures.

IT Project Failure: Management Level Causes

Poor Project Visibility

There is a recognised need to have an information system in place to report on progress, cost, schedule etc. (Larson and Gray, 2010) In the built environment progress can be apparent even to the eye of a lay person but visibility of progress and consumption of resources can be far more difficult for projects in other industries some of which have intangible deliverables. In the IT industry back end infrastructure projects for example may have no visible deliverables and with cloud based deployments no visible supporting hardware.

This is why project management styles like SCRUM and visualisation tools like Kanban boards and burn down charts have been adopted. Without these visualisation aids Project Managers could be blind to progress and resource consumption. Therefore a lack of visibility is proposed as a potential cause, or contributor, to any of the failure examples defined above.

Inadequate Domain Knowledge

Domain Knowledge Is vital in steering stakeholder specifications, knowing what the relevant mile stones are and establishing what is feasible given the budget, time and scope. The case is made by (Larson and Gray, 2010) that the key to managing scope creep, which can be beneficial, is change management. It is questioned however without adequate domain knowledge how can the project manager know what the knock-on effects of a change will be, the derived value of a change or even if a change is possible without putting the project at risk? It is also questioned if a lack of domain knowledge is often misunderstood as poor leadership?

 Lack of Accountability

Accountability is seen by (Kerzner and Kerzner, 2017) as the combination of authority and responsibility that rests at an individual level and is necessary for work to move forward. It is argued that if team members are not assigned tasks with consequences for under performance or failure the project has no drive for completion. This was particularly evident in the PPARS project (“PPARS- a comedy of errors,” n.d.). Due to questionable contract arrangements there were strong financial incentives to not finish the project and without accountability driving the project forward that end result was a complete failure.

Conclusion

An IT Project Manager needs to utilise the project management triangle as intended i.e. a means to keep the desired level of quality of the deliverable in focus. It there are fluctuations in cost, time or scope the IT Project Manager needs to be cognizant of what the knock-on effects will be. In addition an IT Project Manager needs to know who the right person to assign specific tasks to is. That person needs to have the proper motivation to get the work done, with the IT Project Manager having visibility of the work being done and the knowledge and experience to be able to assess if the work is being done properly.  This is achieved through individual accountability, project visibility and domain knowledge. Without these three elements it is proposed a project has little chance of success.

References:

Atkinson, R., 1999. Project management: cost, time and quality, two best guesses and a phenomenon, its time to accept other success criteria. International Journal of Project Management 17, 337–342. https://doi.org/10.1016/S0263-7863(98)00069-6

Kerzner, H., Kerzner, H.R., 2017. Project Management: A Systems Approach to Planning, Scheduling, and Controlling. John Wiley & Sons.

Larson, E.W., Gray, C.F., 2010. Project Management: The Managerial Process. McGraw-Hill Irwin.

NoClip, 2017. FINAL FANTASY XIV Documentary Part #1 – “One Point O” – YouTube [WWW Document]. URL https://www.youtube.com/watch?v=Xs0yQKI7Yw4 (accessed 10.7.20).

Pinto, J.K., Mantel, S.J., 1990. The causes of project failure. IEEE Transactions on Engineering Management 37, 269–276. https://doi.org/10.1109/17.62322

PPARS- a comedy of errors [WWW Document], n.d. URL http://www.irishhealth.com/article.html?id=8661 (accessed 10.13.18).

How to rename and/or remove tables in SQL Server with T-Sql generated by Excel formulas

Database Administrator, dba, Excel, Form, How To, Project Management, Reporting, SQL Server, tsql, , , , , , , , , , , , , , ,

This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)

Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.

With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.

To do this you can just download the Excel file template here. Download

Rename And Drop Script Generator

The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)

To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.

You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.

Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.

You can create the Excel file manually yourself without downloading it.

To do so open a new Excel file and in an empty sheet name the first 9 columns as below:

A1: DatabaseName
B1: SchemaName
C1: TableName
D1: RenameForDeletion
E1: RestoreOriginalName
F1: DropTable
G1: RenameDate
H1: RestoreDate
I1: DropDate

For D2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For E2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For F2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”

To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.

CREATE DATABASE [TidBytez];
GO

USE [TidBytez]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]
GO

 

How to assess a SQL Server instance for GDPR compliance by writing every table and column to Excel

Data, Database Administrator, Excel, How To, Project Management, Reporting, SQL Server, T-SQL, , , , , , , , , , , , ,

This post uses the script I had written before here LINK modified slightly to include a count of each table. If you are a DBA you’re likely assisting compliance offers to find personal identifiable data within the databases at this time. This script will allow you to provide them with a record of every database, table and column on an entire instance. The compliance offer can then sieve through all the columns and highlight any columns that look like they contain personal data for further investigation.

Below is a SQL query that will return the following metadata about each table from each database located on a SQL server database server:

  • ServerName
  • DatabaseName
  • SchemaName
  • TableName
  • CountOfRows
  • ColumnName
  • KeyType

The output is provided in a table format with these additional formatted lines of text which can be used as queries or as part of queries.

  • A Select table query
  • A Count table row columns
  • A Select column query
  • Each column bracketed
  • Each table and column bracketed

The table returned by the query can be exported to excel. Using excels filter option applied to the columns of the table makes finding and selecting specific tables and columns very easy.

This process can be repeated for every SQL Server instance used by the business to generate a single mapped servers master excel file allowing the user to find any table or column available to the organization quickly.

Applying some colour coding like below adds to the ease of use.

Image of excel file with mapped database server structure

How to use:

Simply open SQL Server Management Studio and from object explorer right click on the server name and select new query. This will open a window set to the master database of the server. Copy and paste the SQL below into this SQL Server window and execute. When the query is finished you will have created the table above.

/*
SCRIPT UPDATED
20180316
*/

USE [master]
GO

/*DROP TEMP TABLES IF THEY EXIST*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;
	
IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

DECLARE @ServerName AS SYSNAME
DECLARE @Count INT

SET @ServerName = @@SERVERNAME

CREATE TABLE #DatabaseList (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,ServerName SYSNAME
	,DbName SYSNAME
	);

CREATE TABLE [#TableStructure] (
	[DbName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[ColumnName] SYSNAME
	,[KeyType] CHAR(7)
	) ON [PRIMARY];
	
CREATE TABLE [#TableCount] (
	[Id_TableCount] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,[ServerName] SYSNAME
	,[DatabaseName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[SQLCommand] VARCHAR(MAX)
	,[TableCount] INT
	);

CREATE TABLE #Count (ReturnedCount INT);

/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*
A LIST OF DISTINCT DATABASE NAMES IS CREATED
THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE
THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME
*/
INSERT INTO #DatabaseList (
	ServerName
	,DbName
	)
SELECT @ServerName
	,NAME AS DbName
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

/*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/
DECLARE @sqlCommand AS VARCHAR(MAX)
DECLARE @DbName AS SYSNAME
DECLARE @i AS INT
DECLARE @z AS INT

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #DatabaseList
		)

/*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/
WHILE @i < @z
BEGIN
	/*GET NEW DATABASE NAME*/
	SET @DbName = (
			SELECT [DbName]
			FROM #DatabaseList
			WHERE Id = @i
			)
	/*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/
	SET @sqlCommand = 'USE [' + @DbName + '];' + '

INSERT INTO [#TableStructure]
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,T.NAME AS TableName	
	,C.NAME AS ColumnName
	,CASE 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 
			THEN ''Primary'' 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1 
			THEN ''Foreign''
		ELSE NULL 
		END AS ''KeyType''
FROM SYS.TABLES AS t WITH (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK) 
ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA 
	AND T.NAME = iskcu.TABLE_NAME
	AND C.NAME = iskcu.COLUMN_NAME
ORDER BY SchemaName ASC
	,TableName ASC
	,ColumnName ASC;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @i = @i + 1
END

/*
JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER
ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER
*/
SELECT DISTINCT @@SERVERNAME AS ServerName
	,DL.DbName
	,TS.SchemaName
	,TS.TableName
	,TS.ColumnName
	,TS.[KeyType]
	,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
	,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
	,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
	,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
	,'SELECT COUNT(*) FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + 'WITH (NOLOCK)' AS [PerformTableCount]
INTO #MappedServer
FROM [#DatabaseList] AS DL
INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName
ORDER BY DL.DbName ASC
	,TS.SchemaName ASC
	,TS.TableName ASC
	,TS.ColumnName ASC

/*
HOUSE KEEPING
*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;
	
INSERT INTO #TableCount (
	[ServerName]
	,[DatabaseName]
	,[SchemaName]
	,[TableName]
	,[SQLCommand]
	)
SELECT DISTINCT [ServerName]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[PerformTableCount]
FROM #MappedServer
ORDER BY [ServerName] ASC
	,[DbName] ASC
	,[SchemaName] ASC
	,[TableName] ASC

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #TableCount
		)
	
WHILE @i < @z
BEGIN
	SET @SQLCommand = (
			SELECT SQLCommand
			FROM #TableCount
			WHERE Id_TableCount = @i
			)

	--ERROR HANDLING
	BEGIN TRY
		INSERT INTO #Count
		EXEC (@SqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @Count = (
			SELECT ReturnedCount
			FROM #Count
			)

	TRUNCATE TABLE #Count

	UPDATE #TableCount
	SET TableCount = @Count
	WHERE Id_TableCount = @i;

	SET @i = @i + 1
END

SELECT *
FROM #ErrorTable;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

/*
THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL
USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
*/
SELECT DISTINCT ms.ServerName
	,ms.DbName
	,ms.SchemaName
	,ms.TableName
	,ts.TableCount AS CountOfRows
	,ms.ColumnName
	,ms.KeyType
	,ms.BracketedColumn
	,ms.BracketedTableAndColumn
	,ms.SelectColumn
	,ms.SelectTable
FROM #MappedServer AS ms
LEFT JOIN #TableCount AS ts ON ms.ServerName = ts.ServerName
AND ms.DbName = ts.DatabaseName
AND ms.SchemaName = ts.SchemaName
AND ms.TableName = ts.TableName
ORDER BY ms.DbName ASC
	,ms.SchemaName ASC
	,ms.TableName ASC
	,ms.ColumnName ASC;
	
IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;

IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

 

How to determine where a new database should be deployed

Data, Database Administrator, dba, Project Management, SQL Server, , , , , , , , , , , , , , , , , , ,

Below I’ve listed 22 questions to consider when deciding where a new SQL Server database should be deployed given the four main options of:

  1. Add Database to existing Instance
  2. Create separate Instance for Database
  3. Create separate VM for Instance and Database
  4. New physical machine needed

There’s no hard and fast rule for determining where a database should go however there are often a lot of limitations dictating your choices. I’ve started the list with a big limitation, money.

If you want to use the list like a score card I’ve prepared one called “New Database Hosting Environment Assessment Form”, I know not the snappiest of titles.

New Database Hosting Environment Assessment Form

Use: I’d recommend stepping through the questions with the requester while gathering feedback. You can then fill in the option scoring with weighted figures or simply use the boxes to record notes. The main deliverable of the form is you will have some documentation that demonstrates the location for the database was assessed and the logic and reasoning for the location chosen has been recorded.

Of course consider adding additional questions relevant to your case to the form. If you think your additional questions would be relevant to everyone deploying a new database free to forward your questions on to me and I’ll include them in the form.

The form can be downloaded here link.

Question 1: Can the business afford a new physical machine?

Considerations: If the financial resources are not available option four
can be eliminated.

Question 2: Are there OS or SQL Server Licensing restrictions?

Considerations: If there are no restrictions to a new VM with it’s own instance this will likely offer the most scalable solution and produce the best performance. If restrictions exist options are limited to 1 & 2.

Question 3: Is the Database the back end for a stand-alone application?

Considerations: The back end for a stand-alone application should be isolated as much as possible from other impacting factors. The higher the number of the option chosen the better.

Question 4: What is the primary purpose of the Database?

Considerations: What is the business use\s and in what capacity will the database function i.e. Transactional Db, Reporting Db, Datastore?

Question 5: Do you want\need to be able to limit access to hardware resources?

Considerations: Access to resources, ram etc. can only be limited at an instance level. Option 1 cannot be used.

Question 6: Are there any SQL Server Service Pack dependencies?

Considerations: SQL Server can have different SPs on different instances but cannot have different SPs for different databases within an instance.

Question 7: What is the current excess capacity of the hardware?

Considerations: Can the Hardware handle the additional workload? If not either reduce resource consumption of resources on the hardware, add RAM and/or cores or choose option 4.

Question 8: Is there a VM capacity restraint on the hardware?

Considerations: Can the hardware actually host a new VM without a trade off in resources or a decrease in performance? If restrained option 3 cannot be used.

Question 9: What is the VM capacity?

Considerations: Is the OS already maxed out on resources? Can the VM handle the additional workload?

Question 10: Is there an expected increase in size and usage in the Database over time?

Considerations: If known and minimal option 1 can be used. If unknown or unlimited the higher the number of the option chosen the better.

Question 11: Is the resource usage of the new Database known?

Considerations: Benchmarking RAM, CPU and bandwidth usage should be carried out prior to installation.

Question 12: What are the disaster recovery requirements?

Considerations: Should the databases that share common dependencies be hosted on
the same server?

Question 13: What is the required operational up time? 24/7 etc.

Considerations: Does this operational run time tie in with the rest of the databases
on the instance, or the instances hosted on a VM or physical server?

Question 14: What are the Maintenance requirements?

Considerations: Will new index rebuilds etc. take a long time and effect the schedule of the instance overall?

Question 15: What are the Backup requirements?

Considerations: Will the backup schedule of the other databases be impacted
by the addition?

Question 16: Is the Database functionally similar or supporting databases currently on the instance?

Considerations: Does the new Database logically fit in with the database/s currently running on an instance?

Question 17: Have server security concerns been addressed?

Considerations: Will people with administrative access to the server have access to a Database and data they should not be able to see?

Question 18: Does hosting particular databases together compound the potential
damage If an unauthorised person was to gain access to the server?

Considerations: Will an unauthorised person have access to data that represents a major security breach if the data is combined from different databases?

Question 19: Does a vendor have control over the instance or server?

Considerations: Will putting a Database on a particular instance leave you at the mercy of a vendor?

Question 20: Will stacking instances make tracking down performance issues across
the VM difficult?

Considerations: Will this create a server level noisy neighbour problem?

Question 21: Will packing databases too densely make tracking down performance issues across the Instance difficult?

Considerations: Will this create an instance level noisy neighbour problem?

Question 22: Will moving the Database to be hosted somewhere else in the
future be possible?

Considerations: Does this decision need to be gotten right the first time?

How to install Ubuntu server on a Dell thin client to host Kanboard

Project Management, , , , , , , , , , , , , , , , , , , , , , , , , ,

Well your first question might be “Why install Ubuntu server on a Dell thin client to host Kanboard?”. Good question, the answer is I wanted to trial kanboard and this was the only hardware resource made available to me by the IT infrastructure department, but hey it’s working. So if you’ve ever wondered can Ubuntu Server be installed on a thin client the answer is yes, but there won’t be enough space left to install a GUI so you might want to brush up on your terminal commands prior. And using a thin client isn’t as mad as it sounds because they’re designed to run 24/7 too unlike laptops or conventional desktops.

What is kanboard?

Kanboard is an opensource web hosted project management software. So far I think it’s pretty damn cool. It has all the features of the top paid project management solutions out there with the little added bonus of it being free.

But there’s no such thing as a free meal I hear you cry and you’re right.

The “cost” for me has been the setup time. If you’ve tried this before, or are working you’re way through it now, you likely encountered the same stumbling blocks I did so below are the solutions I used to get the installation up and running. Hopefully this will reduce the “cost” for you.

The hardware I’m using is a OptiPlex FX170 Thin Client. That comes with a measly atom processor, 2 Gb of ram and 4 Gb of flash memory. Shockingly though that’s enough to run 32 bit Ubuntu Server 16.04 but like I said you won’t have enough space left to install a GUI. I’m not going to walk through the installation process because it’s pretty standard. The only thing to remember is you’ll need to choose the LAMP option in the Software Selection screen. Tab to that option and hit space to select it and then hit return to continue on with the installation.

SoftwareSelection

To check if the LAMP installed correctly on the server you can jump onto another machine on the network and try log onto the server’s host IP via the web browser. To get the IP address to use type the below into the server’s linux terminal.

ifconfig -a

If LAMP is up and running on the server you should see the screen below from your web browser.

DefaultPage

Once you’ve confirmed you have LAMP installed successfully head over to the Kanboard site and follow the latest installation instructions for the OS on your server. The instructions I used are below:

Ubuntu Xenial 16.04 LTS

Install Apache and PHP:

sudo apt-get update
sudo apt-get install -y apache2 libapache2-mod-php7.0 php7.0-cli php7.0-mbstring php7.0-sqlite3 \
    php7.0-opcache php7.0-json php7.0-mysql php7.0-pgsql php7.0-ldap php7.0-gd

Install Kanboard:

cd /var/www/html
sudo wget https://kanboard.net/kanboard-latest.zip
sudo unzip kanboard-latest.zip
sudo chown -R www-data:www-data kanboard/data
sudo rm kanboard-latest.zip

Now downloading and installing using the script above just wouldn’t work for me. If it worked for you skip down to Internal Errors encountered.

I don’t know whether it was down to web access firewalls or the download link and file names not corresponding but I couldn’t get the script working. Instead I downloaded the latest kanboard zip file to a usb key using my laptop, unzipped it, and then mounted the key on the server. Without a GUI that was a pain. I was able to mount the file using the steps below:

Enter sudo mkdir /media/usb to create a mount point called usb.

Enter sudo fdisk -l to look for the USB drive already plugged in, let’s say the drive you want to mount is /dev/sdb1.

To mount a USB drive formatted with FAT16 or FAT32 system, enter:

sudo mount -t vfat /dev/sdb1 /media/usb -o uid=1000,gid=100,utf8,dmask=027,fmask=137

OR, To mount a USB drive formatted with NTFS system, enter:

sudo mount -t ntfs-3g /dev/sdb1 /media/usb

To unmount it, just enter sudo umount /media/usb in the Terminal.

Next I copied the files from the key across with the following command:

cp -r /media/usb/kanboard /var/www/html/kanboard

When the folder was across I could then run:

sudo chown -R www-data:www-data kanboard/data

So at that point it was up and running right, Yay!

Hah! Only joking!

After installing I ran into three errors the fixes for which are below.

Internal Errors encountered:

Internal Error: PHP extension required: “pdo_sqlite”

Solution: Run the following in the terminal window

sudo apt-get install php7.0-sqlite

Internal Error: PHP extension required: “gd”

Solution: Run the following in the terminal window

sudo apt-get install -y php7.0 php7.0-sqlite php7.0-gd unzip

Internal Error: PHP extension required: “mbstring”

Solution: Run the following in the terminal window

sudo apt-get install php7.0-mbstring

If you’ve read this article with an eagle eye you’ll have noticed that I ran two of those scripts at the start of the installation, for some reason they just needed to be run again for me, maybe that won’t be the case for you.

For the first interal error, PHP extension required: “pdo_sqlite” you may also need to run through the process below.

Review and edit the PHP Configuration

First we create a file which will display the PHP configuration.

To create a file first change into the directory that contains your website files. For example, the default directory for webpage files for Apache on Ubuntu is /var/www/html/.

Change Directory:

cd /var/www/html

Create File:

sudo nano /var/www/html/info.php

Enter the following text and then save it (Press Crtl+o to save the file)

<?php
phpinfo();
?>

Find where the php.ini file is locationed:

Use you web browser to open the file you just created for example,

http://www.example.com/info.php

This will bring up a page like below.
You are looking for the “Loaded Configuration File” entry.

Loadedfile

Warning: Since the info.php file displays version details of the OS, Web Server, and PHP, this file should be removed when it is not needed to keep the server as secure as possible.

Modifying the PHP Configuration:

sudo nano /etc/php7.0/apache2/php.ini

You want to remove the leading semi-colons from the following line

;extension=php_pdo_sqlite.dll

Hint: Press Ctrl+w and enter the string “extension=php_pdo_sqlite.dll” to find the line in the file.

Press Crtl+o to save the file.

Restart Apache server:

sudo service apache2 restart

That’s it, if you’re as mad as me to run servers on thin clients so you can host Kanboard it should be up and running for you now.

Update:

If anyone is trying to get email working via smtp and a microsoft exchange server Kanboard has provided instructions for this.  LINK

I had to comment out the two bottom lines to make it work.

// Credentials for authentication on the SMTP server (not mandatory)
define('MAIL_SMTP_USERNAME', 'username');
define('MAIL_SMTP_PASSWORD', 'super password');