SQL Server 2005 - An Introduction

Microsoft has been working hard, very hard indeed at re-engineering many of its products to cope with the demands of the 21st Century. Their flagship database product, SQL Server, has not escaped the overhaul, and today we now have a worthy competitor to other mainstream database systems.

In fact, the overhaul has led to the main product being so feature rich that there are now four versions of SQL Server 2005 to match any company’s requirements: Enterprise, Standard, Workgroup and Express.

If all you require is a simple database solution, then Microsoft is even offering the SQL Server 2005 Express edition for free to help you get started.

You can get a comparison on each of the products and a full guide to all the new features by clicking here.

SQL Server 2005 has been redesigned to provide:-

  • A high level of security and protection against viruses, hackers and disasters to ensure 24x7 availability of all information.
  • A greater range of development facilities to help get applications to market more quickly.
  • Up-to-date support with today’s Internet facilities to provide faster and more secure access for online transactions.
  • A wider range of administration facilities to ensure proactive problem resolution.
  • Built-in facilities for business intelligence. With the right business intelligence you can quickly spot trends and quickly make decisions that could result in making, or saving, your company potentially significant amounts of revenue.

So how does SQL Server 2005 do this? Read on!

Security and Trustworthy Computing

Today, all you have to do is use Google to search the Internet for any well-known database, together with the words ‘viruses’ or ‘security’, and you will have several hundred hits regarding vulnerabilities in these databases.

As a result, in 2002, Microsoft started a ten year objective to ensure that its products conform to the requirements of the Trustworthy Computing initiative, which gives recommendations for the security, privacy and reliability of a product.

From a security perspective, Trustworthy Computing states that a product should be secure by design, secure by default and secure by deployment.

Prior to the launch of SQL Server 2005, the development team conducted several months of analysis and audits of potential vulnerabilities to neutralise and thwart any potential hacks, thus taking SQL Server to a new level of security.

As a result, there are many new security features, such as native encryption, integration with one of the highest levels of user authentication known as Kerberos, and a greater set of permissions to help manage user access to various components of your data model.

Each of these is beneficial in helping protect your investment in database design, deployment and configuration, in addition to the confidentiality of the data held within the database.

SQL Server 2005 also includes several tools to help identify and manage security risks. For example, one new tool is the Surface Area Configuration Tool.

Essentially, now, when you now install SQL Server, many of the features are no longer installed by default. This is to reduce the number of potential programs (surface area) and features that can be hacked and to meet the secure by default tenet of the trustworthy computing initiative.

To make this illustration more obvious, it is harder to hack into a program with ten lines of code than it is to hack a program with five million lines of code.

However, it may be that you inherit a site where many of the features have been installed. This tool can help you identify whether the features need to be installed and the associated risks that come with each SQL service that is installed.

High Availability

With every release of a new product, we hear about how much more scalable and reliable it is than the previous version. Microsoft SQL Server 2005 is no exception to this.

Without trying to over-excite you with words such as failover, clustering, database mirroring and replication - all of which have been extracted from the product guide- it is now possible for you to ensure your data is available 24 hours a day, 7 days a week.

Using the extra backup and restore capabilities, it is also possible to backup said data without any disruption to the business. Each of these solutions can be combined, or used in isolation, depending on the importance of your requirement.

Failover Clustering addresses concerns regarding the failure of powerful but expensive hardware that is supporting high transaction rates.

Should the server unexpectedly crash, another server in that cluster will take over data on the failed server’s hard disks and service the users previously connected to the crashed server. SQL Server 2005 will allow up to eight servers to be configured in this fashion.

Database Mirroring is used where you have moderately large amounts of data and a requirement for backup servers throughout the enterprise. In addition, unlike failover clustering, no specialised hardware is required.

Database mirroring requires three servers, known as a principal, a mirror and a witness. Data is saved to the principal server and then copied over to the mirror server.

The witness server is used to ensure the whole process is running smoothly. Should the witness server detect that the principal is no longer running, it will immediately assign the mirror server the role of principal server, and redirect all client transactions to this ‘new’ principal server.

Replication allows you to ensure your data is available by copying all transactions made at any server to other peer servers, whilst allowing users to access any other server.

This type of replication is also known as multi-master replication, which has loose consistency and convergence as there may be a delay whilst other servers send new transactions to all other servers. If no changes are made to the database then each server will eventually have an exact copy of all the information.

Should one of the servers become unavailable then users will be able to access the information from the other servers. Replication can also allow you to set-up load balancing across your network, so that users are not all hitting the database on one server, and is great for networks with sufficient bandwidth.

Developer Heaven

Ok, perhaps not quite heaven but SQL Server 2005 does now integrate with many current technologies that SQL Server 2000 had not yet, or was only just, coming to grips with.

These new facilities allow developers to have greater flexibility to create databases using tools with which they are already familiar, or with some of the newer technologies now available.

For example, one major enhancement with SQL Server 2005 is the addition of native XML support, which enables users to build applications across any platform or device. In other words, business to business web-based data models can now be built using SQL Server 2005.

Previous to SQL Server 2005, you could not use protocols such as HTTP to access your database. With the advent of support for existing and emerging open standards, such as HTTP, XML, SOAP, XQuery, and XML Schema definition language (XSD), a whole new set of web-based applications can now be created.

We could go on about some of the newer functions, or the fact that SQL Server now supports Microsoft’s .NET framework. This may mean nothing to many readers but, essentially, it means that your developers can now take advantage of all the .NET library routines, without having to write their own from scratch.

To put this into layman’s language, it means that that your company gets:-

  • Applications created faster as existing libraries of routines are utilised.
  • A standard consistent approach is used that will make it easier for future developers to quickly get up to speed on your database.
  • Database developers can spend more time on coding without the burden of your of having to write documents on routines that now already exist.
  • Better tested database applications. In other words, less time is spent debugging code because you are now utilising pre-tested code from the .NET libraries.

Honestly, the product guide goes into at least eight pages of enhancements for developers but the final developer tool that I’d like to leave you with is the integration with Visual Studio 2005.

This may not seem significant but over the last four years Visual Studio has grown in such stature and functionality that it has become one of the leading development environments.

Its flexibility in working with a variety of programming languages from a running, compiling, debugging and, more importantly, help perspective, has led it to be a major development tool for the Microsoft Windows operating system.

Visual Studio 2005 supports all the additional objects of SQL Server 2005, such as XML, plus it can help newer database developers quickly get up to speed in creating secure database applications.

Administrator Heaven

Actually, there is no such thing as administrator heaven and, frankly, if there was, SQL Server 2005 would take administrators further away. Youch! Why am I saying this? Well the following facts are certainly true:

  • The aforementioned reduced attack surface means less problem solving for administrators.
  • The native HTTP support means that system administrators don’t even require products such as Microsoft’s Internet Information Services to be the underlying host for the database application, which is one less component to worry about and troubleshoot.
  • SQL server 2005 comes with more precise tools to better manage your company’s databases across a variety of systems.
  • SQL Server 2005 comes with more performance management tools to precisely analyse how the database is being used (or abused).
  • Better security means less likelihood of hackers (and users) accidentally corrupting the database.

On the surface, it does look like the administrators are getting a good deal. So what’s the problem? To be frank there isn’t one, there is just more for an administrator to manage should their organisation fully utilise the facilities within SQL Server 2005.

Two examples of this are the new notification and reporting services.

Notification and Reporting Services

The SQL Server 2005 Notification Services will enable your developers to create applications that use any device (PDA, mobile etc) to tell groups or specific users about important information.

For example, developers could use notification services to let the warehouse manager know when a certain item was nearly out of stock. Who has to ensure the smooth running? That’s right, it’s the administrator.

The reporting services feature is a whole new ball game. Using the SQL Server 2005 comprehensive report toolset, you can either have the developer, the administrator, or even the end-user, create the reports required by the various levels of management.

Reports could be on top ten customers and the products they are purchasing, the least selling product, monthly inventory level audits and so on….. But no matter who creates these reports, guess who has to ensure that these reports are printed smoothly and troubleshoot them if they aren’t? Yes the administrator!

Business Intelligence

Business Intelligence (BI), as referenced by some of Microsoft’s white papers, is the ability to extrapolate timely information from your database models that could lead to substantial cost savings, additional product sales from trend analyses, and timely market information.

Business Intelligence has now become an art-form in its own right and, over the last decade, many companies have found that, through the use of more and more sophisticated data-mining tools, they could leverage the data from their databases to help predict trends that have saved or gained their businesses literally millions of pounds in some cases.

The Microsoft SQL Development team realised this and so have included a rich set of BI tools to help design systems that can take advantage of the valuable information hidden within your data. In addition, these tools will also help with the analysis of reports created from the data-mining process.

In Conclusion

There are so many new and innovative features within SQL Server 2005 that can potentially help your business that a mini-report of this size just cannot do it any justice, apart from whetting your appetite. There are so many areas that can potentially help your busines.

It would be well worth your while getting the whole product guide (and by the way tons of free tools and tuition) by going to the SQL Server home page.

Ben Chai is author of Migrating from Windows NT to Windows 2000 and has worked on several Active Directory projects in the financial sector.