Give User Account sysadmin rights in MS SQL 2008 R2

The IT organization my new job handles all of the software installations, permissions management, etc (an unwelcome change). They failed/forgot to add my account as an administrator in the local SQL Server installation.

Here is how to fix that:

1) Stop SQL Server

Open "Sql Server Configuration Manager" from the start menu. Select "SQL Server Services" from the left window pane. Right click each service and select "Stop".

2) Start a single-user instance of SQL Server

We will be working with two command windows. This will be command window "A".

Open a new command window and navigate to

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Start SQL Server in "Single User Admin Mode" by running sqlservr with the -m flag:

sqlservr -m

3) Add the user account as an admin to the SQL Server instance

In a 2nd command window, Command window "B", navigate to the same directory

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

We will connect to the SQL Server instance we started above by using the sqlcmd tool with the -s flag to provide the server's name, in my case the "(local)" instance, and the -e flag to indicate we are connecting using windows authentication.

sqlcmd -s (local) -e

Now that we have connected, we can send commands directly to the SQL Server instance. We will add our user account to the sysadmin group as follows:

CREATE LOGIN [domain\username] FROM WINDOWS

GO

EXEC sp_addsrvrolemember @loginame='domain\username', @rolename='sysadmin'

GO

The account you substituted for domain\username will now be a sysadmin in the database.

4) Shut down your SQL Server instance and restart SQL Server

Execute the following commands:

Command Window B

shut down SQL Server:

shutdown

exit the command window:

exit

Command Window A

exit the command window:

exit

Now, switch back to Sql Server Configuration Manager and start each of the services you previously stopped. 

You should now be able to log into the SQL server instance using SQL Server Management Studio, etc.

Have a great day :)

Start using SASS / SCSS with Compass and Sublime

Here are some simple instructions to get you up and running with Compass and SASS/SCSS

First, download and install the ruby interpreter. Ruby is a programming language which the Compass application is written in. If you are on windows, the following is your easiest option:
http://rubyinstaller.org/downloads/

From the download options select
select Ruby 2.0.0-p247

during the installation process, check all the check boxes. particularly the one about adding ruby to the path variable.

Next, we will install compass:

Open up a command prompt (start, run,"cmd").
At the command prompt, type the following command to install compass:

gem install compass

Now, in the command window, navigate to the directory of your web project (the directory that has your CSS directory in it)

At the command prompt, type the following command to create a compass project template:
compass create TestProject

where "TestProject" is the name of the project you want to create.

Additional details on installing Compass can be found at:
http://compass-style.org/install/

Compass will generate a template project named "TestProject".

Open the file named config.rb in a text editor.

You can change the names of the directories in the config file to represent your environment (such as changing the name of the "stylesheets" directory to "CSS" or whatever.)

Now create a file in the same directory as your config.rb file named compassWatch.bat

in the batch file add the line

compass watch .

Notice the "." which indicate the "current" directory.

Save the file. Now, run the batch file (double click to open file).
The command in the batch file will tell compass to continually watch the compass project in the current directory and automatically recompile any SASS/SCSS files that are edited and saved into CSS files.


You are now ready to create sass files!

Bonus pro tip:
Download Sublime Text 2 text editor from
http://www.sublimetext.com/2

After installing sublime, download the SASS plugin for sublime:

https://github.com/n00ge/sublime-text-haml-sass/archive/master.zip

Unzip the contents of the zip file.
Copy the "Ruby Haml" and "SASS" directories and paste them into your sublime plugins directory (likely):

C:\Users\Matthew Evans\AppData\Roaming\Sublime Text 2\Packages

Now, open Sublime open a SCSS file (or create a new file with the .scss extension and save it in your TestProject\sass\ directory)

If sublime does not auto-detect that you are working with a scss file you can force it to use proper syntax:

Press ctrl+shift+p and type "sass" into the filter box and select SASS Syntax.
Note that you can do this for other types of files. Very handy.

Make your edits to your scss file and save the file.
Because you have the batch file running in the background, compass will detect the changes to the scss file and auto-compile it into a CSS file.

Now you are REALLY ready to create sass/scss files!

You can learn more about SASS/SCSS at
http://sass-lang.com/tutorial.html

and

http://thesassway.com/

Enjoy.

Problems with Storing Tree Data Structures in a Relational Database

Lets say we have data which lends itself to being stored as a tree. There are about nodes including the root, children and leaves or so. Not bad. When I arrived on the scene, the data was being stored as a flat data structure, which gave me the willies. Here is the table schema:

 
CREATE TABLE OrgChart(
  Business nvarchar(255),
  OperatingSegment nvarchar(255),
  ReportingSegment nvarchar(255),
  Company nvarchar(255)
)

My knee-jerk problems complaints against storing the data like this:

• No constraints! - Anything can be entered for any of the fields so we might end up with "texas" and "TX" for state.
• Data is repeated all over the place! - Why would I want to list "United States" for each record?
• No primary key!? - You are searching across nvarchars for each query?!
• What if I want to be able to persist more data about one of the properties, such as add a description field for a given reporting segment. If I were to follow the same pattern, I would just add another column to the OrgChart table named ReportingSegmentDescription. You can just use you imagination on how disgusting that would be.
• The data is LENDING ITSELF TO BEING STORED AS A TREE. It is a hierarchy for pity's sake. Fix it! Fix it!

So. Long story short, I created a much more elegant solution that I thought would solve all these problems and give us the flexibility to expand the data in every direction! Observe:

 
CREATE TABLE OrganizationType(
 Id int not null identity primary key,
 Name varchar(255) not null
 Description varchar(255) not null
)

CREATE TABLE Organization(
 Id int not null identity primary key ,
 Name varchar(255) not null,
 Description varchar(255)
 ParentId int,
 OrganizationTypeId int not null,
 CONSTRAINT FK_OrgType FOREIGN KEY OrganizationTypeId REFERENCES OrganizationType(Id)
)

As you can see this more or less solves all of our problems, doesn't it? We can do all sorts of things with the data in this tree form that would have been highly impractical before, right? This is better, right!? RIGHT!? Sadly, no. Not right. You can imagine my disappointment when I experienced how dreadfully slow reading the tree structure was. Think about it, if I want to read the entire tree using NHibernate, I have to recursively execute queries against the database for each node. Where the original less-elegant data structure was able to pull down the data and build a tree virtually instantly, the new more elegant solution took a good five seconds. FIVE SECONDS. Now, there are likely several things I could do to improve performance such as:

• Don't use NHibernate.
• Create a Stored Procedure that utilizes a CTE to recursively perform the tree traversal in SQL But.. think about it, if I do this, what data is the sproc going to return? THE FLAT DATA TABLE WE ORIGINALLY HAD...

The reality is that while trees are incredible and give us great power, relational database were NOT designed to work with or store them. Relational database are based in set theory. They deal in collections. They are designed for large flat data structures. That's the point. If you need to store hierarchical data, FLATTEN IT. That is what the database is for.

Now, we are left with a dissatisfaction in the following two options:
1) An ugly flat table that is crying out for elegance and flexibility, or An elegant, but terribly non-performant tree structure.
2) Are these our only options?

Well, yes. And no. Why not use both?

Some possible options:

• Store the data as a tree as we have discussed. Do inserts directly to the Organization table and taylor a stored procedure with a CTE to your reading-needs (such as reading the entire tree).
• Keep both schemas (with a few modifications) and use SQL triggers to sync them. Perform inserts and updates against the tree schema and perform reads against the flat structure. 

I love data structures and making this mistake was indeed a humbling experience. I hate wasting time, but sometimes that is the cost of (re)learning something. Here are some lessons learned from this experience:

• Again: Relational DBs are built for flat data structures. Work with the database, not against it.
• Just because the current solution doesn't seem elegant (to you) doesn't mean it needs to be changed. Remember the simple axiom: "If it ain't broke, don't fix it". 
• Our flat table solved the original problem of storing the data and allowing it to be read back quickly. This is 90% of what was needed. Just because something could possibly be useful or because it would be "so much better" is never a good enough reason to initiate a refactor. Don't feel the need to retro-actively over-architect. 

 This post on stack overflow was very insightful and thought provoking: http://stackoverflow.com/questions/3362669/what-is-the-best-way-to-store-a-tree-structure-in-a-relational-db

Happy Coding!