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