Nov 9, 2009

Person-center-of-pointersXSmallWindows 7 supports SQL Server in multiple versions and edition combinations. In this article, I explain which editions and versions of both Windows and SQL Server work with each other. I also explain the uses for each SQL Server edition, a few installation and configuration tips, and how to leverage new features in Windows 7 for SQL Server.

Each time a new operating system comes out, developers and IT administrators have a new batch of things to do to ensure proper database operation and optimal performance.  SQL Server, Microsoft’s flagship database product, runs on platforms as small as Windows Mobile phones all the way to multi-processor, multi-core servers and Intel’s Itanium chipset. Depending on the edition and version, you can install anything from the database engine to the management tools.

A SQL Server installation may be installed on a workstation-level operating system like Windows 7 for three reasons. First, a database professional may use the workstation as an administration or development tool. In this case, the developer or data administrator normally wants a higher level of permissions on the local system. With Windows 7, the User Account Control (UAC) subsystem is balanced enough to allow those experienced users to employ reduced privileges to run the same tools, which is a safer practice.

Note: If a user accesses SQL Server through a Windows Group membership, make sure the reduced account is part of that group. See the Books Online topic on Authentication for more information.

If your organization is licensed for SQL Server 2008, the client and developer tools can be used to manage SQL Server 2000, SQL Server 2005, and SQL Server 2008. The SQL Server 2008 tools are also UAC-aware, which also allows developers and administrators to run with a lower privilege level on the workstation, increasing security. The combination of Windows 7 and SQL Server 2008 (and higher) creates a secure, balanced system, even when the user isn’t a local administrator.

The second use for SQL Server on Windows 7 is as a “back end” for a client application. In this case, you’ll find either SQL Server Compact Edition (CE) or SQL Server Express Edition, in versions 2005 or 2008. Usually, the IT staff doesn’t have to perform any special installation steps, since the application install contains the installation redistribution of SQL Server.  You might find that a system you’re upgrading to Windows 7 is has an older version of SQL Server that is no longer supported; that may have upgrade implications, as you’ll see in a moment.

Finally, SQL Server may be installed on Windows 7 as part of a full development environment. The SQL Server 2005 or SQL Server 2008 Developer Edition has the same features and capabilities as the Enterprise Edition of SQL Server, but is licensed only for one developer.  While this is supported, you can use the Virtual PC feature and a server-level operating system as a Guest for a better experience. This approach provides an isolated environment that closely approximates the system that runs the actual code.

Versions and Editions of SQL Server Supported on Windows 7

SQL Server comes in multiple versions (based on the time of release) and editions (based on features and capabilities). SQL Server 2000 and earlier are out of “mainline support,” which means that no service packs, security patches or feature releases are made for those versions. SQL Server 2005 and higher are supported for the tools and (depending on the edition) engine on Windows 7. The official support matrix is found in Books Online for SQL Server 2005 and higher, but as of this writing the following chart is accurate:

Windows 7 Edition SQL Server 2005 Edition (Requires Service Pack 2) SQL Server 2008 (and Higher) Edition (Service Pack 1 Required)
Ultimate, Enterprise and Professional x64, x32 Standard , Engine and Tools

x64, x32 Developer, Engine and Tools

x64, x32 Workgroup , Engine and Tools

Express Engine

CE Engine

Client Tools

x64, x32 Standard, Engine and Tools

x64, x32 Developer, Engine and Tools

x64, x32 Web, Engine and Tools

Express Engine

CE Engine

Client Tools

The x64 editions of SQL Server require the x64 edition of Windows 7. I haven’t included the editions of Windows 7 below Professional, but the client tools, CE, and Express versions work with the lower editions of Windows 7.

If you’re migrating an older workstation, you might find the Microsoft Data Engine (MSDE) installed. This is the earliest version of Microsoft’s smaller database editions, and it often replaced the Jet database engine for older Microsoft Access applications. Since this is a SQL Server 2000 version engine, it is no longer part of the mainline support for SQL Server. Contact the vendor or developer of the application to see if they have upgraded the database application to use SQL Server Express or SQL Server Compact Edition, the newer small versions of SQL Server.

To find out if a system has SQL Server installed, run the command NET START and look for the MSSQLSERVER service. If you find it, you can run the Microsoft Assessment and Planning Solution Accelerator (a free program from Microsoft) to determine the details of the SQL Server version, edition, and current database counts and sizes, along with the current resource utilization.

Installing SQL Server Components on Windows 7

SQL Server 2000 is not supported on Windows 7, but you can perform the install. You’ll have to set the services to “Local System” for installation, and then change them in the Services Control Panel Applet.

The Services Control Panel Applet

The Services Control Panel Applet

If you want to test and use Reporting Services in the Developer Edition, SQL Server 2005 Developer Edition requires Internet Information Services (IIS). Since this version is not as UAC-aware as SQL Server 2008, you can right-click any of the tools and use the Run As Administrator feature in Windows 7 if required, particularly for group pass-through for authentication if the installation only includes the “Local Administrators” group in SQL Server. Also for SQL Server 2005, to run the Reporting Services feature in Developer or Standard Edition, you need to add the IIS feature in Windows 7 from the Control Panel.

Windows Features Control Panel

Windows Features Control Panel

The SQL Server 2008 installation is aware of Windows 2008 Server and Windows 7 operating systems, and handles any pre-requisites as part of the initial installation. During the installation of the engine features (on Standard and Developer Editions) you will encounter two places to “Add Current User” to the security groups. That lets the person performing the installation log in once the installation is complete. You may also have to configure the Windows Firewall to allow remote connections to certain SQL Server engine and Reporting Services features.

Setup Support Rules in SQL Server

Setup Support Rules in SQL Server

I’ll explain this requirement further in a moment.

Post Installation Configuration Steps

Once you’ve completed the installation of the tools for SQL Server on Windows 7, the only configuration required is any networking exceptions in the Windows firewall for SQL Server on TCP/IP port 1433 and 1434 outbound. For more information on this process see this MSDN blog entry on SQL Server and the Windows Server 2008 Firewall.

If you’re installing the engine for SQL Server on Windows 7, it normally makes sense to trim down the memory requirements. You can perform this task using the graphical tools by right-clicking the name of the Instance in SQL Server Management Studio and selecting Properties, or you can use the sp_configure command in Transact-SQL. On my 2GB system, I decided to limit SQL Serve memory use to 512MB. The following example sets the memory to that limit:

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'512'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

Windows 7 Features for SQL Server

There are multiple features in Windows 7 that enhance SQL Server operation. A few of them are worth calling out.

For development tasks, the “window snap” feature of dragging a window to the right or left-hand side of the screen allows quick comparisons of data sets, copy and paste operations, and general screen optimization.

Window snap

Window snap

Virtual PC

Virtual PC

Another useful feature included with Windows 7 in the business editions is Virtual PC. This virtualization capability lets you run an entire server-level environment for each developer, with the ability to set recovery points in those machines, copy the machines to other developers, and more. Using a Virtual Machine for SQL Server basically gives you a full Server Environment, completely isolated from your regular “desktop” system, without the cost of additional hardware. (For more about Windows 7 and virtualization, see How Desktop Virtualization Eases Windows 7 Migration.)

Problem Step Recorder

Problem Step Recorder

The Problem Step Recorder, or PSR, is also useful for SQL Server. You can start this feature from the Windows Button| Run menu and then begin recording the activities on your system. This feature creates a ZIP file that contains a set of web pages that record screen activity. You can use this not only to communicate issues to Microsoft but for screen shots for training. (See Using the Problem Steps Recorder to Diagnose Problems Remotely for detailed information.)

PowerShell 2.0 is included with Windows 7, and allows you to work with SQL Server using Server Management Objects libraries and natively in SQL Server 2008. Since PowerShell works with Windows drives, Event Logs and the Registry, you can create simple scripts that interact with Windows, SQL Server, Outlook, the Web and more. You can read more about SQL Server and PowerShell at The Scripting Guy Blog.

Windows 7 and SQL Server are a powerful combination that work well together. Using these tips and configuration steps you can ensure a solid platform for your user programs, developers, and database administrators.

Want more like this? Sign up for the weekly IT Expert Voice newsletter so you don’t miss a thing!

Want more like this? Sign up for the weekly IT Expert Voice Newsletter so you don't miss a thing!

COMMENTS

  • Social comments and analytics for this post…

    This post was mentioned on Twitter by ExpertVoice: Optimizing SQL Server for Windows 7 by @buckwoody http://bit.ly/2tY0sC #sqlserver #windows7…

  • Optimizing SQL Server for Windows 7…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  • [...] The differences between these two options are worth a separate article all on its own. At a minimum, cost factors into the decision. But for this example, using SQL Azure is the “least resistance” option for a simple example. For more about databases, see Optimizing SQL Server for Windows 7. [...]

DELL
FM IT Expert Voice is a partnership between Dell and Federated Media. Privacy Statement