SQL Server on a Netbook – Part 2

In Part 1 I described my search for a lightweight and long battery life netbook to take with me on SQLCruise and to serve as a replacement for my aging and heavy 15″ Thinkpad afterward.  In this post I’ll describe what I did once I received the netbook.

The netbook arrived with Windows 7 Starter and 1GB of RAM installed.  After I had fired up the netbook and played around with it for a while to make sure all was well, it was time to install a memory upgrade.  The Asus Eee PC 1005PE supports a maximum of 2GB of RAM, so I got a 2GB module for it from Crucial – I’m yet to get bad memory from them and the 3-step tool to choose memory for your machine makes ordering RAM very easy.  The memory module is accessed via a removable door on the bottom of the netbook and installation was simple enough, although I was a bit worried about accidentally breaking the retention tabs on the plastic door.  Fortunately this should be a one-time only task.

With the memory upgrade done I started up the machine and played around with it once again just to be sure that all was well before going to the next step, upgrading the operating system.  Windows 7 Starter edition would not be adequate for my needs and it’s not listed as a compatible operating system for use with SQL Server 2008 R2 Developer.  I wanted Windows 7 Professional edition (I don’t need the additional features of Ultimate) and could have just done an upgrade.  However, I decided to do a fresh install, and the next decision was 32-bit or 64-bit.  After doing some web searches I went with 32-bit because it seemed like quite a few people had driver compatibility issues with the 64-bit operating system on my netbook model.

Installing Windows 7 Professional was very smooth, but I’ll mention something about disk partitioning.  The netbook has two small partitions (10GB and 17MB) with system utilities, one of which restores the netbook to factory default (including the Windows 7 Starter edition operating system).  I made sure to leave those partitions alone because I don’t want to lose this feature since a restore disk was not included with the system.  The other thing I’ll mention is that, as usual, I created a separate partition for database files (I usually mount this as an E:\ drive).  This way if any database or log files autogrow to a huge size, there’s no risk they’ll fill up the C:\ drive and crash the system.

As I mentioned above, I didn’t receive a recovery disk with this netbook, which meant that I also didn’t have the drivers on a DVD.  Fortunately the manufacturer’s support website has the drivers available for download.  Additionally, there are some useful websites available that lists the drivers for your machine and which are necessary, which are bloatware, etc.  So installing the drivers was time consuming and boring, but in the end I was quite pleased with the system.  I ran the Windows Experience Rating  utility and got the results below.

It was not a surprise that the setup is CPU bound because I was willing to trade off processing power for longer battery life and less heat when choosing this netbook.

After putting my favorite free anti-virus software on the netbook, it was time to begin installing SQL Server 2008R2 Developer Edition.  Installation was flawless with no warnings or errors during the install or when running the configuration checker beforehand.  Since this is going to be a test and learning environment, I installed everything, Analysis Services, Integration Services, Reporting Services, etc. and not just the client tools and database engine.  I didn’t have to do anything special because I was installing on a netbook.  As I usually do, I created a service account for running the SQL Server services, and during installation I specified a default location for database and log files under the E:\ drive that I mentioned above.  After installation I enabled network protocols, made firewall adjustments, increased the size of the tempdb data and log files, and SQL Server was happily running on my netbook.  Of course this is just a quick run-through, so check out Brent Ozar’s SQL Server Setup Checklist for a more complete list of install tasks and adhere to the relevant best-practices there.

Having SQL Server installed is nice, but that alone isn’t very interesting, so I installed the Adventureworks sample databases.  Once again there were no problems doing this installation when following the website’s instructions.  The AdventureWorks2008R2 database provides a good amount of data to play with and gave me some idea of what working with SQL Server on a netbook would be like.  Results come back slower than working with a typical workstation and/or server environment, but not unreasonable.  Creating a table and inserting just over 120,000 rows from another table took 5 seconds.  Updating a column for all the rows in that table took 2 seconds.  Below there is a screenshot of a join on three tables with an ORDER BY that returns over 120,000 rows and took 40 seconds to complete.

In general, I found that most queries ran over twice as long on this netbook as they did when running SQL Server Management Studio (SSMS) on my Windows Vista workstation (2.4 GHz with 4GB RAM) connected to a dual Xeon server (Windows 2008 64-bit with 8GB of RAM)… in other words, a development environment similar to what many of us use at work.  The netbook’s 10 inch screen can be pretty small for working with SSMS, which is something Chuck Rummel (@crummel4) commented on in Part 1.  Below is a screenshot similar to the one shown above but with the Template Explorer popped out.  There’s not much room remaining for analyzing the execution plan.

At this point I wanted to rough-up the netbook a bit by throwing a nasty, misguided, mean-spirited query at it.  The kind of query that a user with “knows enough SQL to be dangerous” credentials comes up with.  The kind of query that they launch before going to lunch with hopes that the result set will be waiting when they return… even though such queries usually return useless results, if they don’t time out.  The AdventureWorks2008R2 database has two large tables in it with a column that appears in both tables but is the primary key in neither.  Below is the nasty query I executed.

SELECT * FROM Sales.SalesOrderDetail s
INNER JOIN Production.TransactionHistory h
ON s.ProductID = h.ProductID

It stopped with an error after 1 hour 6 minutes and 15 seconds, returning 33,554,432 useless rows.  The error message was “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.”  No real surprise here and I was pleased that the netbook was quite graceful and much better behaved than the query it was processing.

SQL Server doesn’t need to be running all the time and consuming resources on my netbook, so it’s nice to be able to easily start it up and shut it down on demand.  I already had a solution for this on my old Thinkpad running Windows XP , and with a slight modification this works under Windows 7 as well.  All I do is create two batch files, one to start up all the machine’s SQL Server services and the second to shut them down.  Then create a shortcut on the desktop pointing to these batch files.  On Windows XP this was enough, and you could start up SQL Server or shut it down just by double-clicking the appropriate shortcut.  However, under Windows 7 there is an extra step needed to deal with Windows User Account Control.  I’ll get to that in a moment, but first here’s the startup script that I simply save as “SQLServer_Startup.bat” in some folder.

NET START "SQL Server (MSSQLSERVER)"
NET START "SQL Server Agent (MSSQLSERVER)"
NET START "SQL Server Integration Services 10.0"
NET START "SQL Server Analysis Services (MSSQLSERVER)"
NET START "SQL Server Reporting Services (MSSQLSERVER)"

Once I’ve saved “SQLServer_Startup.bat” I create a desktop shortcut to the file.  The double-quoted service names in the NET START calls were obtained by opening the Sql Server Configuration Manager utility, clicking on SQL Server Services in the left hand pane, and the service names will appear in the Name column.  The shutdown script that I save as “SQLServer_Shutdown.bat” is below, a desktop shortcut is linked to it as well.

NET STOP "SQL Server Reporting Services (MSSQLSERVER)"
NET STOP "SQL Server Analysis Services (MSSQLSERVER)"
NET STOP "SQL Server Integration Services 10.0"
NET STOP "SQL Server Agent (MSSQLSERVER)"
NET STOP "SQL Full-text Filter Daemon Launcher (MSSQLSERVER)"
NET STOP "SQL Server (MSSQLSERVER)"

Under Windows XP, I was done at this point and could just double-click on these desktop shortcuts to startup or shutdown SQL Server.  Windows 7 needs one more step for the Windows User Account Control security mechanism.  I modified these two desktop shortcuts’ properties to run as administrator.  To do this, right-click on the shortcut, choose Properties, then under the Shortcut tab choose the “Advanced…” button, check the “Run as administrator” checkbox, then OK your way out.  After this when you double-click on a startup or shutdown shortcut, the User Account Control popup will appear for you to confirm with a “Yes” that you intended to run this script.

With SQL Server established on the machine, I installed some other applications that will come in handy.  Candidates came from Bob Pusateri’s (@SQLBob) blog post on Seven Free Applications You Should Try.  I hope to try more of these, but my immediate needs are for protecting sensitive data and editing text, so I installed Truecrypt and Notepad++ without any issues.  With this, my netbook was deemed worthy of a SQLCruise sticker.  This last step may have been unwise because it seems to have attracted some unwanted denizens of the deep.

Update 2010/10/18: A few people have asked about the carrying case I use for this netbook.  I’ve been using a Case Logic XNTM-1 Slim Line eSling (Black) since July and it works great.  The netbook fits inside perfectly with enough room for some extra items such as pens, thumbdrives, iPod and headphones, etc.  The case is small enough that I can slide it into my carry-on during flights.  I carry it to the office every day and it’s holding up well so far.

About Noel McKinney

Noel McKinney is the administrator of this website.
This entry was posted in Hardware and tagged , . Bookmark the permalink.

11 Responses to SQL Server on a Netbook – Part 2

  1. Awesome post. Love the details. I am mid process of doing the same on my netbook. I think you are Cruise worthy.

    • Thanks Cameron, I’ve been trying to obtain Cruise worthiness! Hitting the right level of detail was tricky, I didn’t want to go overboard (pardon the pun) but also didn’t want to leave out anything that might be useful to someone.

  2. Pingback: Tweets that mention SQL Server on a Netbook – Part 2 | Noel NOT NULL; -- Topsy.com

  3. Pingback: SQL Server on a Netbook – Part 1 | Noel NOT NULL;

  4. Solly Ong says:

    That’s fantastic note…. me planning to get a netbook to do the simple SQL and programming….

  5. Karen Lopez says:

    Thanks for this post. I’m setting up my netbook now, too. Need to order that memory, too.

    • Congratulations on winning your SQLCruise netbook from Quest Software, you left me in the dust during the Quest Through Your Laptop Bag game! This afternoon I kept recalling that a week ago we were all cruising in the Shangri-La Lounge.

  6. Erin Stellato says:

    Great info, thank you for this post! Looking into memory and would like to use your .bat files for starting and stopping SQL Server. I need to get a case for mine, too…

    E

    • Congratulations Erin on winning your SQLCruise netbook from MSSQLTips.com, people are very impressed when I tell them that you beat us all by knowing the SQL Server 825 error code! Please feel free to make use of those .bat files… that reminds me that I need to put a code use and disclaimer statement on my site. As far as the case, I am quite pleased with my caselogic.com E-Sling Case (XNTM-1). It’s designed for the iPad but my 10″ netbook fits perfectly and the case held up well on SQLCruise.

  7. Ditto what everyone else said! :-) I really like the idea of the bat files. I’ve used net start and stop before but I hadn’t thought about creating bat files to stop/start SQL Server. I just may have to try it out. :-)

    • Congratulations on winning your SQLCruise netbook from SQL Sentry and thanks for your comment! Yes, I am a big fan of quick-and-dirty batch files, I’m probably one of the few people who got upset that searching for “dir” in Windows Vista Help returns “0 results for dir, check your spelling”.

Comments are closed.