I just stumbled across this 10-step plan to help secure SQL Server 2000 and thought it might be useful to note…
I haven’t looked at structured query language (SQL) since I was at Uni’, back in the early 1990s, and don’t intended to start now; but with a major new SQL Server release due from Microsoft in November, I’ve been building up my knowledge of the product from an infrastructure perspective. Yesterday evening, I was at a Microsoft TechNet UK event, where Matthew Stephen (one of Microsoft UK’s IT Pro Evangelists) presented the tools for managing and administrating SQL Server 2005 (codenamed Yukon) along with a few of the new features for administrators.
At the centre of SQL Server 2005 administration is the new SQL Management Studio, which replaces SQL Enterprise Manager and uses a Visual Studio project metaphor, featuring a much improved user interface showing registered servers, an object explorer (with a tree view of the databases – similar to Enterprise Manager) and a summary pane. New features include:
In common with all new Microsoft products, SQL Server 2005 will be delivered “secure by default”, meaning that some configuration changes may be required in order to administer some legacy services. Now, when connecting to a server using the SQL Management Studio, the default connection method is using TCP/IP, and Windows authentication is the preferred authentication model.
The SQL Server Web Data Administrator is available today for administering SQL Server databases where no management tools are available. It runs on either Microsoft Internet Information Services (IIS) or the lightweight Cassini portable web server and allows either Windows or SQL-based authentication to connect to a server for management purposes, import and export (queries, stored procedures, etc.) and a T-SQL query editor. Because it is lightweight, the Web Data Administrator is ideal for use over slow network links.
SQL Server 2005 Express Edition replaces the Microsoft SQL Server 2000 Desktop Engine (MSDE) – Microsoft’s free (but functionally limited) version of SQL Server 2000. SQL Server Express 2005 is still limited, but the constraints are different to MSDE, now allowing use of a single CPU (although it can make use of multiple logical CPUs – i.e. with a multi-core CPU), 1Gb of RAM and a 4Gb database size, with no limit on the number of concurrent sessions, making it ideal as a database to support a web site.
The SQL Server Express Manager can be used to administer SQL Server Express or MSDE databases and is based on the Microsoft.NET Framework v2.0. With a tree view of database objects and wizards for common tasks, SQL Express Manager can be used to administer either local or remote databases and includes a T-SQL query editor.
SQLCMD uses the OLEDB interface and replaces ISQL (DB-Library) and OSQL (ODBC) tools (although OSQL still exists and SQLCMD parameters are backwards compatible with OSQL). Offering batch functionality (including command line variables and nested scripts) and script chaining, SQLCMD is a powerful command line tool for SQL Server administration and queries.
SQLiMail is a replacement for the former MAPI32-based SQLMail. Including its own SMTP client, SQLiMail makes use of the new SQL Broker provided with SQL Server 2005 and because it is no longer MAPI-based, has no reliance on Outlook. Multiple profiles can be attached to an SMTP account along with multiple host databases. SQLiMail also supports clusters and 64-bit SQL Server installations.
One of the new features of SQL Management Studio is the creation of maintenance plans (each one made up of sub-plans). The maintenance plan designer has a similar appearance to SQL Server Integration Services (SSIS) (a subject on which Jamie Thomson frequently blogs) featuring a drag and drop visual design workspace which allows tasks to be joined to build up a complex workflow. It is also possible to view the resulting T-SQL statements.
SQL Profiler also has a host of new features, not least the ability to grant trace access to developers (without requiring SA access). Also featuring a new selection user interface, SQL Profiler includes a host of new events (so that pretty much every activity can be audited), assisting with the compliance issues faced by many organisations today. SQL Service Broker activity can also be traced and traced can be redirected to an XML file for programmatic analysis. Working in real-time, SQL Profiler includes templates to assist in profiling and results can be filtered (e.g. by SQL Server internal process identifier). One particularly impressive feature (which Mat didn’t show last night but which I’ve seen him demonstrate before) is that ability to overlay SQL Profiler data on top of a performance monitor graph, allowing analysis of the effect of a SQL operation on server performance.
Finally, the features for monitoring SQL Server 2005 are enhanced with a new log file viewer (allowing SQL logs to be viewed in combination with Windows event logs), activity monitor (e.g. allowing locks to be viewed in real-time, by process, etc.) and an updated SQL Server Agent which can be used to set alerts, now with multiple proxies (so different logins can be used for different services) and new agent user roles.
In one of the SQL Server sessions at last week’s Microsoft Technical Roadshow, Michael Platt showed the first three minutes or so from an MSDN Channel 9 video. In it, we saw one of the systems at Microsoft’s labs in Redmond where ISVs and OEMs assist the SQL Server team with their performance testing and benchmarking – an HP Integrity Superdome system with 64 64-bit Intel Itanium 2 CPUs, 1Tb of RAM and a couple of thousand 18.2Gb disks. Why so many small disks? Apparently it’s about providing provide parallel reading capacity to increase the overall system throughput and hence run the CPUs at their limits.
The whole system cost in the region of $5.1m and the full details of the benchmark tests may be found on the transaction processing performance council website.
Interestingly, one of the problems encountered during the benchmarking was running out of power to spin up all of the disks and having to install a new power distribution unit at a cost of $250,000!
According to Microsoft “SQL Server Express is a version of SQL Server 2005 designed to helps developers build robust and reliable applications by providing a powerful database that is also free and easy to use.”
I know nothing about SQL Server, except that there is a free cut-down version called the Microsoft database engine (MSDE), used by many products where a full-blown SQL Server installation would be overkill.
With SQL Server 2005 Express Edition (the replacement for MSDE), Microsoft have made some changes in the packaging and promotion of the product – it’s better in some ways, but more limited in others. It also has a partner product – Microsoft SQL Server 2005 Express Manager, which can be used for database administration (including existing SQL Server 2000 and MSDE installations).
You can read more about SQL Server 2005 Express Edition, including a comparison with MSDE and links for download on Mat Stephen’s weblog (Mat is one of the Microsoft UK IT Evangelists).
I don’t know much about SQL, but I recently worked with a colleague in to produce a standardised SQL Server 2000 installation (clustered and standalone) for a client and came across some Microsoft advice which gives dos, don’ts and basic warnings for clustered SQL Server installations.
The article is basically a roll-up of information regarding running SQL on a Microsoft cluster for versions 6.5, 7.0 and 2000.