SQL Server Relational Engine: Resource Center
Articles
- SQL Server Service Broker: This new and promising feature of SQL
Server 2005 offers asynchronous features on SQL Server 2005. This article offers a code walkthrough
to get started with the service broker. It starts from scratch: you just need SQL
Server (regular or Express), management studio, and Visual Studio (although if your
handy with notepad and csc.exe, you don't even need Visual Studio).
- Writing SQL functions in .NET: Did you know that
cpu intensive User Defined Functions (UDF) can run more than 10 times faster when
written in .Net versus a T-SQL version? That's the speedup you get from compiled
versus interpreted code. But the .Net UDFs are interesting for other reasons as
well: every .Net method is only a few keystrokes away in your T-SQL queries. This
article describes how you can get a function for evaluating regular expressions
in T-SQL.
- XML in SQL Server: SQL
Server supports xml as a new data type. But what for interesting things can you
do with a database column of type XML? In this article we demonstrate how you can
combine .Net and SQL Server to serialize objects to a SQL Server database,
query and manipulate the serialized objects in the database, and deserialize them
again.
- Calling web services in T-SQL: Although
SQL Server 2005 allows you to easily create web services, calling a web service
from within T-SQL is hard... unless you use the option of creating
T-SQL stored procedures and functions in .Net managed code. This article walks you
through the scenario of creating a function which retrieves the current conversion
rate from euros to dollars via a web service.
Courses
SQL Server is a feature-rich software product. Many courses have been developped
to guide you through this feature-rich product. This paragraph guides you through
the list of courses. You can use this as a sort of algorithm to find the course
which will suite you best.
- Do you speak the language of SQL?
In SQL Server, the SQL language (Structured Query
Language) is used a lot. Some familiarity with this language is
needed, especially if you want to become a developper, although for lots of administrator
tasks it helps as well to speak a bit of SQL. If the difference between GROUP BY
and ORDER BY, or the difference between INNER JOIN and OUTER JOIN are unclear to
you, you might consider following the Transact-SQL course before taking any other
course. See MS2778
for more detail.
- Understanding SQL Server 2005/2008
Although the basics of relational databases are all the same (tables, columns,
primary keys, foreign keys,...), every relational database management system (RDBMS)
has its own pecularities: how is data being stored, which types of indexes do exsist,
which types do exsist, how do you make backups, how do you write stored procedures,...
If you are new to Microsoft SQL Server, there are two courses to
familiarize yourself with SQL Server 2005/2008:
MS2779 for development tasks, and
MS2780 for administrator related tasks. These courses also help to prepare
yourself for the exam 70-431 (Microsoft Certified Technology Specialist).
If you are already familiar with SQL Server 2005, you have to catch up with all
the changes and extensions which are made in the 2008 version, but you don't want
to loose time on things you already know from the old SQL Server. Don't worry, course
UPSQL8 only
discusses the new functionality of SQL Server 2008.
- Administration or development?
From here on, the courses get very specialised. Because of that, they are splitted
in a developer track and an administrator track:
- Administration
Security and high availability are two of the main concerns for administrators.
Course MS2787 discusses
security for SQL Server. It goes a lot further than just logins
and users, but also talks about network security, monitoring for security, ... Course
MS2788 talks about
high availability: How can I make sure my users can query the database,
even when Murphy is visiting. Mirroring, clustering and log shipping are the major
topics in this course.
Course MS2786 discusses
the design of the SQL Server infrastructure. This not only discusses
where to store your data and how to backup the data, but also topics such as consolidating
multiple servers, data distribution and archiving, and Database conventions and
standards. Course MS2789
discusses all the technologies to automate operations in SQL Server,
including the SQL Server Agent and Integration Services. Finally, workshop
MS2790 talks about all the aspects of optimising the server,
ranging from network connections and monitoring tools, over query plan inspection
till solving locking problems.
All these courses together help you prepare for exam 70-443 and 70-444, which gives
you the title of Microsoft Certified IT Professional: Database Administrator.
For more information on certification, check out
http://www.u2u.net/Brochure/U2UCertification2006.pdf
- Development
Designing server side solutions is not trivial. The time where a database
was 'just a bunch of tables' lies behind us. For lots of applications, we have to
decide which technologies to use. Are we going to use triggers, .Net triggers, event
notifications or the service broker? Are we storing this data shredded in tables,
in Xml, or will we build our own type for this? All these types of technologies
and best practises on when to use them, are the topic of course
MS2781. Course MS2782
discusses the approach to designing the actual databases. In this
course, we pay attention to the difference between the logical and physical design,
designing for scalability, best practises, et cetera.
For those developers who need to develop a data tier, there is
also a one day course which discusses common issues in this area, such as when to
apply MARS (Multiple Active Result Sets), caching strategies, the use of cursors,...
This is course MS2783.
The last course in this category is
MS2784, which is a workshop that focusses on all techniques for optimising
queries, including topics such as the design of indexes, rewriting
code to avoid cursors, and normalizing and denormalizing data.
All these courses together help you prepare for exam 70-441 and 70-442, which gives
you the title of Microsoft Certified IT Professional: Database Developer.
For more information on certification, check out
http://www.u2u.net/Brochure/U2UCertification2006.pdf
For practical details such as course calendars, we refer to our course site:
http://www.u2u.be/sql.aspx
 |
The SQL Server Service Manager was a popular tool for SQL Server 2000: it showed
you the status of the servers (relational as well as OLAP, DTS, ...), and gave you
an easy way to start, stop, pause and restart all the sql server services. Unfortunately,
SQL Server 2005 doesn't ship with this small but handy tool, but luckily, somebody
rewrote the tool for 2005. You can find it at
http://www.sqldbatips.com/showarticle.asp?ID=46
PS if you did an in-place upgrade of SQL 2000, your old service manager still works
well for the relational engine, but you will experience problems when using it with
other 2005 services, such as Integrations Services and Analysis Services. |
 |
The SQL Server Service Broker (SSB for short), is a great new feature
of SQL Server: having an asynchronous component in a relational database, alows
us to 'postpone' trigger code, auditing information to a later point in time, or
another machine. The only thing that is a bit troublesome is creating all the objects
to setup a Service Broker dialog: Management Studio has no GUIs for creating all
these objects. However, using SMO, it is not to hard to program such a tool. You
can download one from
http://staff.develop.com/nielsb/ct.ashx?id=0f036cd1-2692-4362-837a-81cb2587acc2&url=http%3a%2f%2fstaff.develop.com%2fnielsb%2fcode%2fssbadminproj.zip |
 |
Although this is commercial software, I still include it in this overview because
it is an often requested feature in SQL Server Management: Intellisence. Red Gate
offers a free version of SQLPrompt, which gives you intellisence
in SQL Server Management Studio, Visual Studio and Query Analyser. This offer is time restricted, so quickly
check http://www.red-gate.com/products/SQL_Prompt/index.htm
to see if it is still available. |
|
Toad is a nice piece of software which is 'the' tool for thousands of Oracle users.
Now Toad is also available for SQL Server. And what is better, it is available for
free, although certain features are lacking. Get it at
http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm |
|