Storing and sharing your database scripts online

 Make you DB scripts available via GitHub

Storing your database scripts on-line is one way to ensure that they are always available (as long as you have Internet access). One way to achieve this is to use a code repository such as GitHub.

In this post I will look at one way in which you can store your database scripts using Github.

Create a Github account if you do not already have one.

Install the appropriate git client for your OS (In this post I will be using the git shell on Windows).

1. Create a new repository in GitHub and make a note of the URL (https://github.com/youruser/myrepo.git)

2. Open a git shell on your local PC and navigate to the folder which contains your DB scripts.

3. Initialise the directory by typing the following command:

git init

3a. If you initialise the repo with a .gitignore and a README.md you should do a git pull {url from step 1} to ensure you don't commit files to source that you want to ignore.

4. Locally, add and commit what you want in your initial repo. For everything type:

git add .

git commit -m 'Initial commit comment'

5. To attach your remote repo with the name 'origin' (like cloning would do)

git remote add origin "https://github.com/youruser/myrepo.git"

6. Execute git pull origin master to pull the remote branch so that they are in sync.

git pull origin master

7. To push up your master branch (change master to something else for a different branch):

git push origin master

Once you have uploaded your scripts you will want to push any changes that you make to the local copy of the scripts to GitHub. 

Push changes to a repository

1. Open git shell and cd to source directory

2. To get the current status type

git status

3. Add the changes (i.e. new files etc.)

git add name_of_new_script.sql

4. Commit changes with comment

git commit -m “Add name_of_new_script.sql”

5. Either add the remote origin or check its already set up.

git remote add origin https://github.com/youruser/myrepo.git

And then double check to make sure it knows:

git remote -v

6. If you have made changes in the GIT account (i.e. added readme file) then you need to pull before you can push

git pull origin master

7. push change

git push origin master

Notes - 

You can read and download the entire Pro Git e-book, written by Scott Chacon and Ben Straub and published by Apress, from here.

http://www.git-scm.com/book/en/v2

There is also excellent documentation available on the GitHub site.

Six Revisions - Top 10 Git Tutorials for Beginners

Database Health Monitor

Database Health Monitor

Database Health Monitor is a performance monitoring and diagnostics solution for SQL Server databases. It can give DBA's and administrators who do not have access to enterprise tools like  Idera’s SQL Diagnostic Manager or Spotlight an overview of the health of a SQL Server database. 

It can also help identify performance or availability problems within their SQL Server environment, all from a central console. According to the Database Health website "there have been 7000 installs of Database Health Monitor world wide in the last 3 years".

The performance tool is aimed at those people responsible for managing anything from one to multiple SQL Servers.  It should allow the busy DBA to quickly check on the status of a database, find problems, and to remedy those problems.

The tool provides information about backups, disk space, duplicate indexes, index fragmentation, long running queries, one time use queries, plan cache,queries needing params, statistics, stored procs with the most logical writes, and unused indexes.

You can also quickly view reports on CPU usage, page reads by database, page writes by database, plan cache by database, queries needing params, and waits.

This tool is a great help when database tuning or just general day to day monitoring and administering.

V2.2a has just been released (5th December 2015). The latest version has a host of new features as well as bug fixes.

New features:

• Added a DBCC CheckDB dialog to run Check DB against a database. Reports status along the way and shows what is being checked as it gets scanned.

• Added a link to the checkDB report page from the QuickScan CheckDB message.

• CTRL+a for select all in the edit box on the missing indexes advisor.

• Making the ‘see more’ option stick when viewing the CPU by Hour Heatmap.

• Added a Disk Space Report into the instance level reports.

• Added color coding to the Last Known Good CheckDB report.

• Added right click copy to clipboard to the many of the charts. Now you can just copy the chart to the clipboard as an image.

• Adding filtering of the historic waits for CXPACKET.

• Adding links to the main server overview if there is a SQL Server update available.

• QuickScan report

Bug Fixes:

• Fixed the backup status report to include databases that have never had a backup. This was missed due to the join condition. Fixing button colors on the CheckDB dialog. Buttons didn’t look right on Windows Server 2012.

Some of the reports in the tool require a database compatibility level of 90 (SQL Server 2005) or higher.

Database Health was created by Steve Stedman of Stedman Solutions (twitter @sqlEmt) with the aim of helping DBAs and Developers find the performance issues or bottlenecks on SQL Server.

It is currently free as its in beta, but this could change in the future.

There is even an option on the Database Health website were you can request a new feature.

This may not have all of the features or glamour of an enterprise monitoring tool but for DBA’s on a budget this is great for monitoring and tuning.

Database Health can be downloaded from here - http://databasehealth.com/download/

Connection Pooling in SQL Server

Connection Pooling - SQL Server

Connection pooling can be really beneficial, as opening and maintaining a database connection for 

each user is costly and wastes resources. A connection pool is a set of reusable database 

connections maintained by the database server so that the connections can be reused.

However if the application code does not follow some basic principles then it can cause headaches 

for the SQL Server DBA.

Connection pooling problems are almost always caused by a connection leak. This is a condition 

where the application does not consistently close its database connections correctly. However, this 

could also be due to worker threads not being available to process the incoming login or the server

might be experiencing some CPU pressures. 

One of the biggest considerations within connection pooling is to ensure that the proper disposal or 

closure of the SqlConnection object(s) is taking place. When clients don’t close or dispose 

of the connection object properly  it will continue to consume the pooled connection, instead 

of releasing it back to the pool for reuse.This is a common pitfall for web applications.

Also, if the connection string is not an exact match to an existing pool when a new connection 

is opened, a new pool is created. Connection pooling is created for each distinct connection 

string, as such there will be as many connection pools as the number of connection 

strings used. If integrated security is used (connection string + the user identity) then you will 

get one pool per user. The user cannot take advantage of the connections made by 

other users. Also, if the application extracts data from many databases then there will be a 

separate pool of connections to each database.

As such you should ensure that developers are following the below simple steps within their code:

1. Only open a connection when you need it, not before.
2. Close your connection as soon as you are done using it.
3. Don't leave a connection open if it is not being used.
4. Be sure to drop any temporary objects before closing a connection.
5. Be sure to close any user-defined transactions before closing a connection.
6. Don't use application roles if you want to take advantage of connection pooling.

Other steps you can take to reduce potential problems are:

1. Reduce the number of integrated security accounts so accounts can share a pool.
2. Connect to the same database on the server and then switch the context 

(by executing the T-SQL USE statement) to the desired database. 

Again this will ensure that users accessing different databases can share a pool.

Some import connection string parameters that developers (and DBAs) need to be aware of are:

1. Connect Timeout - This controls the wait period in seconds when a new connection 

is requested, if this timeout expires, an exception will be thrown. The default is 15 seconds.

2. Max Pool Size - This specifies the maximum size of your connection pool. The default is 100.

The below query can be useful when investigating connection pool issues.

it shows the number of concurrent connection by user, for those users who have more

than 2 sessions connected to the DB. 

SELECT 
des.program_name,
des.login_name, 
des.host_name, 
COUNT(des.session_id) [Connections] 
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC 
ON des.session_id = DEC.session_id 
WHERE des.is_user_process = 1 
AND des.status != 'running' 
GROUP BY des.program_name, des.login_name, des.host_name 
HAVING COUNT(des.session_id) > 2 
ORDER BY COUNT(des.session_id) DESC;

If you need to clear your connection pool programmatically then you can do this using .NET 2.0.

ADO.NET 2.0 provides two static methods for doing this.

    SqlConnection.ClearPool( SqlConnectionObject ).
    SqlConnection.ClearAllPools().

The ClearAllPools method empties the connection pool.

If there are connections in use at the time of the call, they are marked appropriately 

and will be discarded when Close method is called on them. 

There is also a small application called connection pool cleaner created by uma075 

which has been specifically built for this purpose.
It can be downloaded from sourceforge.

This application could help if application users start to see timeout expired messages due 

to the max number of pooled connections being reached.

Timeout expired.  
The timeout period elapsed prior to obtaining a connection from the pool.  
This may have occurred because all pooled connections were in use and max pool size was reached.

Here are some useful related links:

SQL Server Central - connection pool limit exceeds error 

Idera - SQL Server connection pooling 

SQL Server Performance - connection pooling myths

Pythian - SQL Server understanding and controlling connection 

Always Run Crontab on Primary Node

How to ensure an shell script always runs on the primary node in an active / passive cluster only

We have two Oracle database servers in an active / passive cluster.I have a shell script which needs to be run by the oracle user and scheduled via crontab. However I only want this script to run on which ever node is the primary. I want to automate this task so I can leave both running crons active and concentrate on another tasks. When the nodes failover the new primary will continue to run the script, the new passive node will not. There is a virtual IP (VIP) address which is set up to always point to the primary node.

A sysadmin showed me this really cool trick which does this very task:

Example crontab entry

• The inconfig command checks whether the virtual IP address is configured on the server (i.e the server is primary).

Note - change the above IP address (127.0.0.1)to match your VIP

• If it is then the command returns true and the remainder of the statement is executed (i.e the my_script.sh is executed).
• If the first part of the statement returns false then it exits and the script is not run.

This cron entry can then be added on both sides safe in the knowledge that it will only execute on the primary. There are certainly other ways to perform this tasks such as using a heartbeat or pacemaker, but this is a quick, simple solution which appears to work well.

Hope this helps.

Fedora 23 Workstation

Fedora 23 Work Station Review

I recently upgraded from Fedora 22 to 23. 

Here is a quick review of the latest version.

Fedora Workstation is a reliable, user-friendly, and powerful operating system aimed at home users, hobbyists, students, and software developers

At first glance there does not appear to be any major changes between this and the previous version. It features the latest version of GNOME (3.18) and also includes updates to the file manager, a new Calendar and Todo application. The release includes Firefox 40, Thunderbird 38 & LibreOffice 5 which is is the latest version of the productivity suite which has a variety of new features and improvements.

There is also improved dynamic backlight support for devices with brightness sensors. This means that on computers with integrated light sensors, GNOME can now automatically adjust screen brightness.

The file manager (Nautilus) now gives more detailed feedback when you are copying or moving large files. If you have a Google drive account set up you can now see your drive contents in the files window.

The Linux Vendor Firmware Service is now integrated into the Software application. The Software app will notify you if there are firmware updates available for your hardware, such as BIOS updates. You can then install them directly from the Software window.

The version of Perl in Fedora 23 is now updated to 5.2.2.

There is also Unicode 8.0 support, DNF System Upgrades and refreshed support for Google APIs to provide access to user data through GNOME apps.

Fedora now hardens as many packages as possible with position-independent code (ASLR) and FULL RELRO to reduce the impact of potential security vulnerabilities.

The Software app will now notify you if there are firmware updates available for your hardware, such as BIOS updates. You can then install them directly from Software.

This release continues the march towards Python 3 as the default python interpreter. Python 2.7 is currently in maintenance mode only, which means upstream only accepts bug fixes and security fixes, The python executable (/usr/bin/python) will not be installed by default on many Fedora 23 systems.
As such you need to either use the python3 exe (/usr/bin/python3) instead or install python2.

The Eclipse IDE has also had been updated to the Mars (4.5) release

There is additional Wayland support included in this release (which is to be expected as Wayland will probably be the default display server in Fedora 24).

There are other spins of Fedora 23 available featuring desktops such as: Mate, Cinnamon and KDE.

Overall this is a really stable, secure distro which would be ideal for those people who have never tried Linux before and are suitably curious.
This distro should have everything you need and would expect from a modern operating system.

Why not give it a try? You can download it from - get Fedora.

Fedora Magazine - Fedora 23 released

The Change Management Paradox

Agile Change Management

According to ITIL the goal of the change management function is to ensure that ..

"all the changes that need to be made to IT infrastructure and services to be performed and implemented correctly by ensuring standard procedures are followed."

Having a robust Change Management process ensures that changes are:

•     justified.

•     carried out without jeopardizing IT service quality.

•     properly recorded, classified and documented.

•     have been carefully tested in a test environment.

•     are recorded in the CMDB.

•     can be undone by running back-out plans / procedures.

               

However implementing an appropriate change management policy can also run into difficulties:

For instance, if excessively restrictive procedures are adopted or the change process is trivialized then this could potentially result in insufficient stability or lead to poor quality service to users / customers.

If this approach is taken by the change management team it could be viewed by the wider IT team as excessively restrictive and overtly bureaucratic. This in turn could potentially lead to the process not been taken seriously, which would undoubtedly result in poor compliance.

All organizations deal with the change process in different ways, some have restrictive and limiting change management, while others use comparatively permissive methodologies. The keys is in determining what is the appropriate risk that should be tolerated.

As more development teams embrace the Agile methodology for the Software Development Life Cycle, this is of little use, in terms of speed of delivery, unless the change management process also adapts and employs a more suitable agile approach.

If the change team do not themselves embrace change then there will be a widening gap between the amount of change the developers need to stay Agile versus the change management's ability to conform to the standards for documentation, quality assurance and risk assessment needed to adequately protect a production environment.

Change management is one of the most important processes within the Technology team. Any organization will experience a large number of changes in order to accommodate new business requirements, to correct faults, or for other various reasons. All changes have the potential to disrupt a business, hence controlling the release of changes is critical. However it is also one of the most difficult areas to find the right balance between protecting the business, whilst also ensuring that there is the appropriate flexibility to allow the business to efficiently develop a continuous stream of new software capabilities.

There is a fine line between securing the production environment while also ensuring that changes can take place in line with policy - quickly, efficiently, but most importantly successfully and with the least amount of bureaucracy and unnecessary barriers.

  

Wikipedia - Change management (ITSM)

Andrew Simon - Striving for 'Agile' Change Management'

Level 3 - Can ITIL be Agile?

Hacking Your Career Part 2 - Building a Brand

Building a Brand

I read recently that developing a personal brand is essential for the advancement of your career.

In a recent post (The Basics of Personal Branding) Brent Oznar highlighted the importance of building your own personal brand as a way to successfully market your skills.

In the same post he also detailed some of the first steps you can take to work towards this goal.

He states that " when most people enter the job market, they only have a product (their skills) – but no brand or marketing. As a result, their sales effort is hard as hell...."

I thought that the themes highlighted in this post really tie in nicely with the ideas I detailed in a previous post (Hacking your career).

In fact some of the points, such as: building really good material that people actually want to consume and material that shows you know what you’re talking about (blog posts, white papers, presentations, webcasts, podcasts, and books) are very similar ideas.

On-line there are many similar posts from such sites as Mashable and Forbes which re-enforce the points in Brent's post.

It seems to be a popular idea at present and I certainly think that there is value to be gained from following some of this advice.

I will leave it yourself to determine whether this approach could work for your circumstances.

I would be interested in hearing whether you have already started to build your own brand and what actions you have taken to market your skills and knowledge.

Thanks for reading. Good luck.

Mashable - Personal Branding

Forbes - Things you can do to build an awesome personal brand

Inc - 5 steps to build a personal brand

Me - Hacking your career - Part 1

Oracle SQL Loader - Insert filename into table

 How to insert the name of your data file into the load table

I was tasked with a problem today on a project where one of the requirements was to load data from a flat file into a DB table, but at the same time also load the name of the file. The file name is unique and contains information about country of origin, sequence number and date.
My first thought was does SQL Loader accept parameters! It doesn't. After a little web searching I came across this eloquent solution for this very problem.

To allow SQL Loader to insert the input file name into output table you first have to update your control file (myControlFile.ctl) with a placeholder for the file name (:FILE).


Load data
truncate
into table MY_TABLE
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
UNIQUEREF,
ID,
QTY,
ETA DATE"DD/MM/YYYY",
FILENAME CONSTANT ":FILE"
)


Then update shell script with the following.  This will Loop through files in current directory and load each one. The cat / sed command will create a new control file and replace the placeholder (:FILE) with the name of your csv file.


FILES=`ls *.csv`
CTL=myControlFile.ctl
for f in $FILES
do
   cat $CTL| sed "s/:FILE/$f/g" > $f.ctl
   sqlldr usr/pass control=$f.ctl data=$f bad=mybad.bad discard=mydsc.dsc log=mylog.log ERRORS=1000000
done 

The file name should now be successfully loaded into the MY_TABLE.

Hacking Your Career

Hacking Your Career

Last weekend I attended Oggcamp, an annual 2 day un-conference held in the UK and based around the free software and open hardware community. There were 4 tracks giving a wide range of really interesing talks such as: The New Space Race... Mars, An expression of elegance, exploring Base 3 as an alternative to binary, Ubuntu Pi flavour Maker and O.C.D, anxiety and me.

One of the talks that inspired me to write this blog was Hacking your own Career by Stuart Coulson (@spcoulson).

I will try and summarise (as best I can remember as I have been unable to locate the slide deck from the talk) the main points:

• CV's are becoming less relevant in helping you find that next (or first) step within your technology career.
• The HR team spend little time reading it and will probablly not understanding the technical babble.
  So what steps can you take to improve your emploibility prospects?

1. Stay relevant

• Keep your skill upto date. Get certified, if appropriate.
• There are plenty of (free) on-line courses from providers such as: Udacity, Coursera, EDX and even MongoDB university, to name a few. There are plently of oppurtunties online to grow your skills and knowledge and hopefully your career
• Keep your LinkedIn profile upto date with details of completed courses, certifications, etc.

2. Stay professional

• Create seperate social media accounts for your professional output. Have an account(s) relevant to your profession, follow people in this area, post relevant content
• Why not save your cat posts for your personal account(s)

3. Share your knowledge

• Finds way to share your knowledge to the wider world
• Start a blog and share the knowledge you already have. This is a great way to show to potential employeers the breadth and depth of your knowledge
• Code club, go to hackathons
• If you are a coder then set up a GITHUB account(other repository hosting service are available) and share your projects
• Try to attend tech conferences, barcamps and the like as they are an ideal oppurtunity to network, learn and share your knowledge. Why not give a talk!

I have probablly missed some (or all) of his points but I hope that I have not mis-represented the centeral ideas.

I hope this helps – good luck out there!