Blog ala Vidar

SQL, AppFrame and other cool technologies

Who are YOU?

After reading Adam Machanic’s blog post with the title “Who Are You?” I though I’d do the same. So, who are you? I know many of my readers are Omega employees, but I don’t know who’s actually reading my blog. If you don’t mind, please leave a comment about yourself. Doesn’t matter if you’re an Omega employee or not. I’d like to know more about you, so please tell me a bit about yourself. This can include your name, company, where you live, what you do, your URL(s), twitter name or anything else you’d like to share. Please also let me know what you’d like to read (more) about at my blog.

If you want to know more about me, you can take a look at my about-page. Thanks for sharing 🙂

Renaming users

One of our customers are moving from one hosting provider to another, and because of this we have to migrate their databases and stuff. While doing this, they’re also changing their domain name (in Active Directory), and since that wasn’t enough change, all users will have a new naming convention in their usernames. With this in mind I’ve created a SQL script that will take “OldUser” and “NewUser”, look for all columns that have NVARCHAR(128), which is not computed, and update all of them. To make it easier, I’ve disabled the triggers and then enabled them when the query is finished.

SET NOCOUNT ON

DECLARE @OldUser AS NVARCHAR(128)
DECLARE @NewUser AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128)
DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  @OldUser = 'OldDomain\NordnesVidar',
  @NewUser = 'NewDomain\NordVida'

SELECT
  O.name AS TableName, C.name AS ColumnName
    INTO #columns
    FROM sys.sysobjects AS O
    INNER JOIN sys.syscolumns AS C ON O.id = C.id
    WHERE O.xtype = 'U'
      AND O.name LIKE '[as]tbl%' -- Naming convention in Omega for tables
      AND C.xusertype = 231 --NVARCHAR
      AND C.length = 256 -- 128 (unicode)
      AND C.iscomputed = 0
      AND C.name NOT IN ('Domain')
    ORDER BY O.name, C.name

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @TableName = TableName,
      @ColumnName = ColumnName
        FROM #columns

    PRINT @TableName + ' - ' + @ColumnName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    SET @SQL = 'UPDATE [' + @TableName + '] '
    SET @SQL = @SQL + 'SET [' + @ColumnName + '] = ''' + @NewUser + ''' '
    SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] = ''' + @OldUser + ''''
    EXEC (@SQL)

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    DELETE
        FROM #columns
        WHERE TableName = @TableName
          AND ColumnName = @ColumnName
END

DROP TABLE #columns

Windows Small Business Server Code Name Aurora

You might have noticed that Microsoft released a new preview of its new version of Home Server code named “Vail” yesterday. I haven’t had a chance to have a look it yet, but I have installed the new version of SBS (Small Business Server) which is Home Server’s big brother. It’s very similar to Home Server, which is not a big surprise since it’s basically the same product, but SBS contains a couple of features you won’t see in Home Server. The SBS product focuses on small businesses (which might have something to do with the naming?) with less than 25 employees/users.

When installing, the only thing I was required to type was server name, company name, username, password and product key. Other than this, changing regional settings and time zone was the only things I had to do before everything was up and running. And by everything I mean a server with Active Directory, DNS, File Services and IIS installed. In addition to this, it’s ready for you to add (up to 25) users, (up to 25) computers and manage shared folders. Since this is using AD you of course have the option of creating group policies, logging on with the same account on several computers, but since this is also based on Home Server, you Aurora will make backups of all your computers and monitor core services, low disk space, check that anti-virus is up to date etc. If you’re interested, you can set up email notifications for these types of alerts.

By default Aurora creates a couple of shared folders; one for each user and one shared folder which everyone can access. You can of course create new shared, change them or delete whatever you want there.

I think this is a very nice product for small businesses but also branch offices. A feature you can install is “Branch Cache”, so I guess you can add the SBS as a domain in your big company’s forest in AD. When looking at the screenshots in the product overview you can see a much more detailed dashboard than the one included in this preview. I’m really looking forward to the release, and I’m 99.9% sure I’ll migrate my Home Server over to this product when it’s available!

5 minutes interview #25–Petri Tapio Wilhelmsen

Petri has just taken over for Rune Grothaug at Microsoft, being NNUG’s contact inside of Microsoft. He’s been a developer for 3 years, and an MVP for 1 year.

What did you do before you started at Microsoft?
I was a consultant at Avanade Nordics where I worked with technologies like Silverlight, WPF, Microsoft Surface and SharePoint for three years, but my passion for technology started long before that. When I was 10 years old I got a Christmas gift from my father, a book named “Du og Dino lærer Basic”, basically a children book on how to program with Basic. Later I started doing graphics- and game programming using C++, and participating in communities. I was also in 2009 awarded the Microsoft MVP award on graphics programming.

What will be your main everyday tasks at Microsoft?
Well, right now I’m trying to learn what I should do. But, it will be mostly about helping- and building communities, MSDN Live and MSDN Flash. But, my main task will be to reach out to Norwegian developers, and be their contact-person in Microsoft.

Do you have any plans on how to make the community even stronger?
Right now, my focus has been to learn my new role. As I earlier mentioned, I haven’t been able to think too much about this yet, but I do have some plans. I will have to learn what communities we have and work from there.

Other than computers, do you have any hobbies?
During the winters I usually go snowboarding with my friends, and even snow kiting when I have that possibility. During the summers I’m kite surfing around Oslo and sometimes out of the country. Other than that I like to play games on my Xbox360 and my computer.

New design

Our blogs at http://blogs.appframe.com got a new design yesterday. I’m very pleased with it. There are a couple of things not working at the moment (search for instance), but they’ll probably be up and running on Monday.

The reason for the new design is that we’ve moved over to WebSiteCMS, which is a part of AppFrame. Now we can use either Live Writer or AppFrame to post to our blogs. We’ve changed the commenting (and vote-system) to use Disqus, which in my opinion is pretty sexy.

Any comments?

Columns that are not in use

I’m working on upgrading a client to our new framework. While doing this, we’re creating new, clean and sexy, data models. Changing column names that are partly Norwegian and partly English into only English.

While I was doing this, I found a table which were heavily used, and had A LOT of fields. 173 to be exact. So, how do I check which columns that are not in use? Fastest way is to check all fields if they’ve got any values at all. This is of course not that accurate, since a field could have been used once, 10 years ago, and never later, but we’ll start with this. Here’s a script to find all fields in one table, that does not have any values:

SET NOCOUNT ON

DECLARE @TableID AS NVARCHAR(128)
SET @TableID = 'your_table'

DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  name AS ColumnName
    INTO #columns
    FROM sys.syscolumns
    WHERE id = OBJECT_ID(@TableID)

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @ColumnName = ColumnName
        FROM #columns

    SET @SQL = 'IF NOT EXISTS (SELECT * '
    SET @SQL = @SQL + ' FROM ' + @TableID
    SET @SQL = @SQL + ' WHERE ' + @ColumnName + ' IS NOT NULL) '
    SET @SQL = @SQL + 'BEGIN '
    SET @SQL = @SQL + '    PRINT '''+ @ColumnName + ''''
    SET @SQL = @SQL + 'END'
    EXEC(@SQL)

    DELETE
        FROM #columns
        WHERE ColumnName = @ColumnName
END

DROP TABLE #columns

Home Server

I was asked by one of my co-workers, Jan-Børge, what I recommended for a home server. This lead me to give a little talk about what I’ve done in my home-network yesterday. I’m guessing Jan-Børge isn’t the only one wanting to have a server at home to play with, so here’s my recommendations. I’m recommending two servers; one for plain home-server (backup all your PCs, have one place to put your music, videos and pictures etc) and one virtual-hosting server where you can create several virtual servers to test what ever you want. I’ve done the last one myself, where I’ve now got 5 virtual servers; Active Directory, Exchange, Home Server, Web Server and SQL Server.

First of, you of course need legal Windows licenses. The cheapest way to do this is buying your own TechNet Standard subscription. This costs $199 the first year and $149 the next years. With this you can download and install Windows 7, Office 2010, Windows Server 2008 R2, SQL Server, Exchange +++. You got 10 licenses for each OS. Now, the reason why Microsoft has this offer is for IT pro’s (and developers) to get to know Microsoft technology for testing-purposes, before buying it in the company they’re working in. Most of what I’ve tested is either already in use in Omega, or we’re planning on supporting it in some way.

Home Server Vail

If you’re going to buy a server and install Home Server, buy a 64 bit machine, and install the beta of VAIL (Home Server 2.0). It’s being released later this year, but I’ve used the beta a while now and I haven’t run into any issues yet. For hardware I’d recommend a 64 bit processor. Doesn’t matter how fast it is. The minimum requirements for RAM is 1 GB, but I’d put in 2 GB. Make sure the main board has graphics and network integrated. Other than this, just buy the cheapest you can get. I’ve got 2 x 2TB disks, but how much disk space you want is of course up to you. If you suddenly run low on disk space, just order more and plug it in. You need to click ONE button after installing the new disk(s), and it adds it to both backup-space and shared folders. Pretty neat!

After you’ve installed the server you need to connect all your computers to the server. This is done by visiting http://name-of-your-home-server/connect. After this is done, you’ll get three icons on the desktop, and the server will take backup of your computer every night. It will also check all computers if anti virus is installed and updated, anti-malware protection is on and firewall is on.

By double-clicking the Dashboard, you get to administer your home server. Here you can create new shared folders (defaults are Documents, Music, Pictures, Recorded TV, Users and Videos), do manual backups of your computers, find deleted files from your computers, administer the home server web-site and much more.

Virtual Hosting-Server

If you are going to run several virtual servers on one physical server, you need much RAM and CPU cores. The speed of each core isn’t really that important, but I’d recommend 8 GB RAM and Quad core if you’re having 4-5 virtual servers. I would also recommend big disks, and a powerful PSU, so you can add many disks etc. Other than this, buy the cheapest you get.

When it comes to software, I really recommend Windows Server 2008 R2 with Hyper-V. Managing virtual servers with Hyper-V is like eating ice-cream. It’s very easy, and everyone loves it!

Here you see my virtual servers. If I for some reason needed more RAM for the SQL Server, or maybe more CPUs, I just shut it down, right click, properties, and change memory from 1024 to 4096, and change a dropdown box from 1 CPU to 4 CPUs. Then I just click save and turn it on again. Same goes for hard disks. I can create a new file on the physical server (with the .vhd file extension) and add it to the properties of the server. VERY easy.

My Vacation

This weekend is the last in my vacation which basically means that I’m coming to Ølen again after 3 weeks of vacation. So, what have I been doing the last weeks? A lot of things! First on the priority list was the World Cup of soccer and Tour de France, but there’s a lot of “free time” from these events, especially after the World Cup was finished. I’ve of course used my vacation to be the perfect uncle, babysitting my soon to be 1 year old nephew, visiting family etc., but I’ve also used A LOT of time in front of the computer. First I reinstalled my server, playing some more with Exchange, but when that was done I had to find something else to keep me busy; Mentor

Mentor is a website that focuses on connecting people who want to learn something with people who want to teach something. We’re first focusing on .NET, SQL and related Microsoft technologies, but it would be cool if oracle and java guys were using this too! Basically what we’re trying to do is to make a site where people can register which topics (“tags”) they’re interested in and then connecting them with other people of these topics. This has been (and will be) a great opportunity for me to learn ASP.NET MVC. WAIT! Did you read wrong? Vidar doing web-development? Has hell frozen over? Not yet. I’ve just found out that MVC actually makes web-development pretty easy. If you’re interested in this topic I really recommend you watching this great session about ASP.NET MVC2 Basics Introduction by Scott Hanselman. Actually all sessions with this guy is worth recommending, because he’s a real code ninja, and he’s hilarious.

I registered Mentor at CodePlex, and got joined by Jan Leon and Arild. But with CodePlex (which is GREAT for open source projects) you don’t have full access to TFS (Team Foundation Server) and you have to publish your project within 30 days. I don’t want to publish this project as an open source project, since I don’t see the big win on this, although if anyone wants the source code I won’t mind sending it. I’ve been participating on several open source projects, and I really like the idea, but I don’t see why I should publish this. Anyhow, this is another discussion. If you REALLY want me to, contact me and we’ll discuss it. Using TFS on CodePlex whet my appetite for it, so I installed it on a server internally in Omega. I would really recommend using this instead of SVN (for the core parts of AppFrame, custom projects etc.). You can also connect to TFS using SVN with the help of SvnBridge.

Other than this I’ve created PowerPoint slides for my sessions at AppEd, and done other AppEd-planning things. I’m currently thinking of a great way of recording each session, without the session being interrupted because of the recording (software failure, people having to talk into a microphone etc.). Thinking of using Camtasia, but not sure yet. Oh, did I mention we’ve got several surprises for those attending AppEd? One clue for one of these surprises is already in this blog post; “Code Ninja”.

Apprentice

A couple of months back I signed up on a Norwegian forum to be a teacher for people who wanted to start learning programming. Last week I got my first “apprentice” which got me to start thinking about what a great idea this actually is. Why isn’t there some site out there where you can find someone to help you start programming? Or maybe get better at something you already know.

I’ve got a lot of spare time, but what we’re talking about here is a couple of mails and/or MSN-chats a week. Everyone got time for that! I’m don’t know everything about SQL Server, but I’m sure I could teach most of you a couple of things. I’ve also got experience with .NET, PHP, MySQL and many other things, but that’s a bit rusty, so I’d love to get better at least with .NET. Having someone you can just shoot questions to would be awesome. I know, there are a lot of Q&A  sites out there. I’ve started being active on StackOverflow and ServerFault.com lately, and before I’ve been on MSDN forums.

So, what I’ve been thinking of lately is, what about making a site to connect teachers and students? Maybe integrate it with StackOverflow using their API to do the Q&A-part, but have somewhat of a social site where you can find interesting articles (or videos?) about your favorite subjects, and connect with someone who’s willing to answer a couple of questions a week, to get you started with programming (or maybe a new topic like LINQ, WCF or similar).

Would you be interested in participating on such a site?
As a “teacher” you’d get a profile, adding all your feeds (blogs, youtube videos or similar), where people can vote on your feed-items, and the top articles would show on the front page (maybe just links to the original blog-post, not to “steal” any content). You’d also get to help the community, and of course kill as much time as you wanted by rating articles and being contacted by “students”.
As a “student” you’d browse through teacher’s profiles, articles and of course get in contact with someone willing to help you on your way with either starting programming, a new language or just a new feature.
As a “developer” you’d get to help me build this site

So, bottom line. You got any thoughts about this? Would you use the site? Contact me either you’ve got ideas, comments or you want to participate!

Missing Indexes

In SQL Server 2005, Microsoft introduced Dynamic Management Views and Functions (DMV/DMFs). These provides real-time data about indexes, hardware, query usage and many many other things. Using this, you can actually get suggested indexes from the SQL Server itself, that in it’s mind it would gain performance if you implemented them. So, why isn’t these generated automatically? The simple answer is disk-usage. If you implemented all these, you’d end up with A LOT of indexes, and therefore a much bigger database. So, which should you implement?

SELECT
  DENSE_RANK() OVER (ORDER BY MIGS.avg_user_impact DESC, MIG.index_handle) AS IndexRank,
  RIGHT(MID.statement, CHARINDEX('[', REVERSE(MID.statement))) AS table_name,
  MIC.column_id, MIC.column_name, MIC.column_usage,
  MIGS.unique_compiles, MIGS.user_seeks,
  MIGS.avg_total_user_cost, MIGS.avg_user_impact
    FROM sys.dm_db_missing_index_details AS MID
    CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC
    INNER JOIN sys.dm_db_missing_index_groups AS MIG
        ON MIG.index_handle = MID.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS
        ON MIG.index_group_handle = MIGS.group_handle
    WHERE statement LIKE '%atbl%'
      AND MIGS.unique_compiles > 10
    ORDER BY
      MIGS.avg_user_impact DESC,
      MIG.index_handle,
      CASE
        WHEN column_usage = 'EQUALITY' THEN 1
        WHEN column_usage = 'INEQUALITY' THEN 2
        ELSE 3
      END,
      column_id

Using this query, you get indexes, with it’s columns, ordered by the average user impact. You should consider those that have a relatively high avg_user_impact and/or avg_total_user_cost.