Will Warren

The blog of an engineer from Canada who specializes in SaaS, HA, Cloud and Product Development. I work in the Internet.


SQL Server: Enabling Read Committed Snapshot Isolation


When using Microsoft SQL Server, enabling Read Committed Snapshot Isolation (RCSI) is one way to prevent reads (SELECT statements) from escalating into full table locks. Depending on your application this can either be a good or a bad thing. I’m not going to get into the why’s and why-nots of each strategy - this is a good article to read if you’re having a hard time deciding which strategy to choose and why.

So let’s say you want to enable RCSI on a fictional database MyDB. This can be achieved by simply issuing the following T-SQL:

ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON
GO

To check that it was successfully enabled, you can check the System View sys.databases:

SELECT is_read_committed_snapshot_on
FROM sys.databases
WHERE [name] = 'MyDB'

If it returns 1 then RCSI was successfully applied, you’re done! Unless…

Read the rest →

Tags: sql database sysadmin

Adding firewall rules for Oracle Database using iptables


To connect to a box on your network that is running Oracle Database, you will first need to allow connections to Oracle through your firewall.

If you’re running CentOS, RHEL, Fedora or any other Linux variant that uses iptables, use the following commands to create a firewall exception (Assuming you’re running your listener on port 1521 - check with sudo lsnrctl status):

sudo iptables -I INPUT -p tcp --dport 1521 -j ACCEPT

Read the rest →

Tags: sysadmin database oracle

Awesome Database Design Tool: wwwsqldesigner


I stumbled across this software while trawling the internet looking for good design tools. It’s super simple to use and looks great. Because it’s web based (written almost entirely in Javascript), you can design without having to have any gigantic software packages installed. Also it’s free and open-source which I’m always a fan of!

Screenshot of WWW SQL Designer

Example of WWW SQL Designer

Read the rest →

Tags: database discoveries

SQL Script for Provinces and Territories of Canada


I made this simple SQL script to insert all the provinces of Canada. I’m posting it on here in case I need it in the future, and so anyone else that might find it useful can download it.

CREATE TABLE IF NOT EXISTS provinces (
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` VARCHAR(100),
	`abbrev` CHAR(2)
);

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Alberta', 'AB');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'British Columbia', 'BC');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Manitoba', 'MB');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'New Brunswick', 'NB');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Newfoundland and Labrador', 'NL');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Northwest Territories', 'NT');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Nova Scotia', 'NS');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Nunavut', 'NU');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Ontario', 'ON');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Prince Edward Island', 'PE');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Quebec', 'QC');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Saskatchewan', 'SK');

INSERT INTO provinces(`id`, `name`, `abbrev`)
VALUES(NULL, 'Yukon', 'YT');

Or download it as a SQL script here: canada_provinces.sql

Read the rest →

Tags: database projects

An ASCII needle in an Extended ASCII haystack


I was tasked with writing some code to pull all the research project data that we’d collected over the past 10-15 years into our new J2EE-based product, Kuali Coeus. The legacy system ran off SQL Server which is a lot more forgiving of character encodings and string data in general than the new system (which runs off MySQL).

It had taken me a while to figure out a way to map all the old data onto the new data structures, but I felt like I had done a pretty awesome job. The few batches I had tested it with all passed its tests with no problems. However when I unleashed it on a full dataset (some 6000 rows), about 60% (roughly 2 hours) of the way through, it crashed, and rolled the ENTIRE thing back.

Read the rest →

Tags: java coding database

Read on...