At the heart of the system is the database engine, which has undergone a redesign so it can be used to store both structured and unstructured data. Then there’s a central management console that you use to interact with your databases, and from where you can launch the various tools at your disposal. Everything so far has really been core database, but SQL Server goes further. It has a really excellent set of business intelligence tools that manage to be more or less usable by mere mortals. The reporting services are even better than they were in the previous release. And finally, SQL Server has been designed to be extremely developer-friendly.
The starting point for most people who want to use SQL Server is its Management Studio. This is the central management console from where you can work with your databases across all your database servers. If you’re familiar with earlier versions of SQL Server, the Management Studio replaces the old Enterprise Manager. The new version lets you monitor and manage SQL Server’s Integration Services, Analysis Services, Reporting Services, Notification Services, and SQL Mobile. SQL Server Management Studio also includes Query Editor, a replacement for the old Query Analyzer. The new version is more user friendly, so you get auto-complete suggestions on your SQL statements, and can choose to have the results displayed in XML format. As with the rest of SQL Server, the Management Console can be customised using SQL Management Objects (SMO). Management Studio looks quite different thanks to the fact that it’s been implemented using Winforms and the .NET Framework, and the Studio looks and feels like the Visual Studio environment.
Tools of the trade
Much of the work in managing a SQL Server database is carried out using the database tools, and SQL Server 2005 has a better selection than its predecessors. Replication Services is used to replicate data so you can create distributed databases, and make use of secondary data stores – for example, if you want to set up a copy of the database for use with reports.The next tool in the list is Notification Services. This gives you the means to develop applications that will send information to connected and mobile devices. The classic example of this is to send an email message to the sales team when the day’s sales figures reach a target figure, or a text to a mobile phone when a particular record is updated.
Integration Services provide ETL (extract, transform, and load) facilities. This is a key requirement if you plan to use SQL Server for data warehousing – data needs to be consistent and clean if it’s going to give sensible answers. Integration Services is what replaces SQL Server 2000’s Data Transformation Services (DTS), but it’s a lot better than its predecessor in terms of its performance and how easy it is to use and manage. Integration Services now comes with Business Intelligence Workbench and SQL Server Workbench utilities, which are designed to help you when you’re extracting data and transforming it for use in your data warehouses and for analysis.
From the Business Intelligence Workbench, you can choose to create reports, analysis services or integration services projects
The next two services, Analysis and Reporting, are amongst the most interesting, because they provide a way to get information about your data. Analysis Services gives Online analytical processing (OLAP), so you can put your data into multiple dimensions and slice and dice it to see the information that’s hidden behind the acres of data. This area has received some of the most work in the new version, with several new algorithms available – Association Rules, Time Series, Regression Trees, Sequence Clustering, Neural Network, and Naïve Bayes. You can also make use of Key Performance Indicators on your analysis cubes, and run MDX scripts.
Reporting Services is used for more traditional database reporting processes, whether you’re designing reports for paper or for the web. It will probably already be familiar to those of you who’ve been using SQL Server 2000, but in the new release it has been revamped, integrated into SQL Server 2005 rather than being standalone, and now has the addition of Report Builder to make it easier to use. Report Builder is brand new in this version. Essentially, it’s a report designer for end users. To them it looks something like a cross between Excel and PowerPoint running in a web browser. They can use it to create reports by pointing and clicking to the data objects provided by your queries and base reports.
Creating a query in the SQL Server Management Studio
Developer tools
In most database products, there is a distinct separation between the development you do within the database and the development you do in ‘standard’ programming environments. You might write an application in Visual Studio using Visual Basic or C#, and that application could well make use of the data from a database stored in SQL Server, but you’d treat it pretty much as a black box that you pulled data from as and when needed. If you wanted to carry out database-specific tasks, such as creating database structures, writing stored procedures, designing reports, scripting in T-SQL or similar languages, then you’d work within the database, in what were frankly very basic environments with no real assistance or nice tools to help you. There seemed to be an underlying assumption that the database developers were the poor relations in terms of the facilities they needed, and also a feeling that if you were going to write a stored procedure, for example, then you obviously knew what you were doing and could just get on with it. There was an editor that you could type in, what more did you need?Things have definitely changed in this release. We’ve been welcomed into the fold with the ‘real’ programmers, given nice editing environments and more tools. What’s more, because everything is tied much more closely together, you can develop applications that mix and match standard applications techniques alongside database specifics.
Creating a UDF in Visual Studio – note the autocomplete tip offering advice on what you might type next
In specific terms, SQL Server 2005 is closely integrated with Visual Studio 2005. It has good support for web services, and includes the Common Language Runtime. This means that you can choose the language you’re most familiar with when you want to develop your database application. That could be Transact-SQL, it could be VB.NET, or it could be C#. You can develop and use .NET-based stored procedures, functions, and triggers.
Transact-SQL is, of course, still important and has been strengthened in this release. You now have facilities for error handling, creating recursive queries, and support for the SQL Server database engine promises improved performance. You can also make use of XML throughout your applications, along with Multidimensional Expressions (MDX), and XML for Analysis (XML/A). Another addition is the Business Intelligence Development Studio. This is an environment in which you can develop BI solutions using Visual Studio. It consists of the database engine, analysis services, and reporting services, and the idea is that you will use it to design your own SQL Server Integration Services (SSIS) packages that you can then use to work with data from multiple sources, with facilities so you can transfer, mine, transform, and consolidate data from heterogeneous sources.
While most developers will be interested in developing applications that use the database for storing data, you can go further if you want to. All the elements of SQL Server – the data extraction, transformation, and loading (ETL), data mining, OLAP, and reporting tools, can be programmed from within Visual Studio, and if you’re really keen, the subsystems in SQL Server all ship with their own object model and set of APIs.
Some of the new programming support is provided through SQL Management Objects, (SMO). This is a new set of programming objects that you can use to automate tasks such as applying Transact-SQL scripts, creating SQL Server Agent jobs, and scheduling backups. You can go further with tasks such as creating databases, though these and similar tasks will generally be more suited to normal administrative tools.
An enterprise level database?
SQL Server has been incredibly popular for departmental level databases, but one criticism that has been levelled against it by the makers of rival databases is that it doesn’t scale. Fine if you’re talking databases in gigabytes, but if you’ve got terabytes of data, you need product X instead, goes the argument. Microsoft has put lots of effort into ensuring that SQL Server 2005 will be the release that overcomes this caveat. SQL Server 2005 supports features such as database mirroring, failover clustering, and database snapshots for distributed databases.Database mirroring is key to creating some highly available database applications. In SQL Server’s interpretation of the technology, the database runs on a source server, and the transaction log is continuously copied to a destination server that contains the database mirror. If something goes wrong with the primary server, the server with the mirror detects the failure and the users who were attached to the primary server are connected to the mirrored database instead. This technology will, however, be turned off in the initial release of SQL Server 2005; Microsoft currently plans to introduce it in the first half of 2006.
One technology that will be included in the launch version is support for failover clustering. When used with Microsoft Windows Clustering Services, SQL Server 2000 could be set up to make use of virtual servers so that systems could be quickly failed over if the server failed. Support for Clustering Services has been extended to include SQL Server Analysis Services, Notification Services, and SQL Server replication. The main criticism of the support in SQL Server 2000 was the fact that you could only have two cluster nodes, (four if you ran Windows 2000 Datacenter Server and SQL Server 2000 Enterprise Edition); this has now been increased to eight.
Another high availability option that has been added to SQL Server 2005 is the ability to create database snapshots – read-only views of the database. The snapshot starts by creating a complete copy of the database, and then adds only the pages that are modified.
Some of the other changes to the way SQL Server works may sound quite minor, but they add greatly to its usability in situations where you can’t have the database unavailable. For example, you can now create, rebuild, or drop an index while the table is still online and in use. You can also carry out a restore operation on part of a database while the rest of the database remains online and available. Previous versions required you to take down the entire database for these operations – hardly a 24x7 scenario.
The CLR support means you have SQL specific objects such as User Defined Functions and Triggers available from within Visual Studio
Scalability
Scalability is a key area for SQL Server 2005; it has to scale well if it is to break into new markets, so a lot of work has gone into providing the means to scale. Three areas in which this has happened are 64-bit support, table partitioning, and snapshot isolation. The 64-bit support is pretty self-explanatory; you can run both 32-bit and 64-bit applications on an x64 processor. Any 64-bit applications are executed in 64-bit mode, but your existing 32-bit applications run natively. This overcomes one of the big drawbacks of moving to a 64-bit architecture – the need to rewrite existing applications.It remains to be seen how many people will be moving to 64-bit. A more generally applicable improvement is the support for table and indexing partitioning. The majority of databases in the market that SQL Server is targeting offer the ability to partition tables and indexes across servers in order to spread the load on a particular machine, and SQL Server has provided some support for partitioning since SQL Server 7.0. The facilities were limited, though, particularly on the administration side, and you had to write your applications being aware of where the tables lived. SQL Server 2005 makes the whole process more transparent. It also provides a twist on the usual techniques by giving you the means to partition tables across filegroups in a database. A SQL Server filegroup is a logical grouping of the files containing data from a single SQL Server database to simplify database administration tasks, such as backup. If you define a filegroup and place one or more tables in it, you can assign it to be placed on several different physical disks and leave SQL Server to work out which records in your tables should be placed on which disk to ensure that all the disks are used more or less evenly.
Security
SQL Server has hit the headlines several times over the past few years when high profile attacks succeeded in bringing down many servers. It has also gained a certain notoriety because of the old default of installing SQL Server with a default blank password on the all-powerful sa system administrator login account. Subsequent problems where worms made use of this to assume administrative rights on systems were undoubtedly the fault of database administrators for not changing the sa password to something slightly more secure, but mud sticks.These days, Microsoft has a different attitude to security, and this shows in SQL Server 2005. You can enforce policies on your SQL Server login passwords; you can manage permissions in a much more granular fashion so you can run a ‘least privileges’ policy, only giving users the minimum permissions they need on any data. Your databases can be encrypted, and communications between the client and the server are encrypted by default.
Conclusion
We’ve waited a long time for this version of SQL Server, and on the whole, I think it lives up to most expectations. Earlier releases did make it feel a bit as though database developers were the programming equivalent of the foreign legion, condemned to work in appalling conditions with no support and no interaction with mainstream developers. That’s pretty much gone. The integration with Visual Studio, the better query designers and report designers all make the environment a lot more friendly. The new management console is much better, and SQL Server 2005 shines in its data analysis, business intelligence and data mining facilities.Kay Ewbank, editor of Server Management magazine, is a highly experienced database analyst who has followed the development of database technology from dbase through to today’s SQL servers.
Comments