Surface Area Configuration Tool (part 2 of 3)
By: Sarah Barela
Important Terms And Definitions, And What They Mean to Us
All About Full Text Service
Next we have the full text service. The full text service manages full text indexes and full text indexes store information about words within a column. A normal index will look for the first word in that column and index it that way. A full text index will index all the words within that column and store their location.
Now full text indexes sometimes get a bad reputation especially in SQL 2000, there were a lot of performance problems and it could put an overhead on your server. But if used smartly they can perform much better than wildcard searches. The full text index is built on the Microsoft search technology Liken 2000, but in 2005 it is more integrated into the database engine. It still runs as a separate service and that service is the MSFTSSQL service. Sometimes you’ll see that running on your machine, don’t panic, it’s supposed to be there. Another thing to note about the full text service is that for each instance of SQL Server running on a machine there is also an instance of the full text service.
Integration services. Oh I skipped notification services, let’s go back. Notification services is new to SQL 2005 and it is a platform for building applications that generate and send notifications to subscribers. Now, these notifications are alerts that can be sent to a variety of devices like an email or a text message, and they are based upon an event that changes within a database or a schedule. An easy one to think about is you could create a subscriber, myself, might want to know when the price of a television falls below $200.00. I could set up a notification that would alert me when the price of a TV falls below $200.00.
Now, notification services is not installed with SQL Server, it is a separate installation that has to be installed after SQL Server has been installed. However, once you do install it you can enable it or disable it here.
Now integration services, or SSIS, replaces SQL DTS from SQL 2000. It has been completely rebuilt, there is a new designer, there are new tasks and transformations, as well as there were improvements to both the performance and the deployment of it. By default SSIS is set to automatic. Now, one thing to keep in mind is maintenance plans in SQL 2005 are not based on SSIS, but you can disable SSIS and still use maintenance plans.
And our final service is the SQL browser service. The SQL browser service returns name, pipe, and TCPI port information to the client applications. If you choose to disable this service you can use static ports and let your application know what those port numbers are. Now, the SQL browser service is kind of intelligent. It is set to automatic only when certain conditions exist on your server. If you have named instances, so if you install more than one instance on your server, this will set itself to automatic. If you are installed on a cluster or if you’re installed side by side with SQL 2000.
Now we are finished with the surface area configuration tool for services and features so I’m going to go ahead and I always click apply just to be safe before I close out of it, and we’re going to hit okay. Now just like with the surface area configuration tool for services and connections, the surface area configuration tool for features you can view by instance or component. There are three main services that you have features for: there is the database engine, analysis services, and reporting services .
And let’s go ahead and open this up. I’m going to go ahead and collect here on feature, and once again it takes a second to come up. As you can see the database engine is first, and let’s start it here with ad hoc queries. What are ad hoc queries? Ad hoc queries only refer to distributed ad hoc queries, and I would not enable this unless you really know what you’re doing.
What distributed queries can do is they can allow a user to access another data source like a text file or active directory or another SQL Server using the security context of the account that SQL Service is running under. Now, you should lock down and secure your SQL service accounts, but still this can potentially allow a user to access another data source that it normally would not have permissions to do. By default this feature is disabled. If you need to query a distributed data source another option might be to create a linked server which you can then apply an individual security setting to.
What Exactly Is CLR?
Next we have CLR integration. CLR was one of the most anticipated features of SQL 2005, and CLR stands for common language run time is central to the Microsoft.net framework and it provides the execution environment for all .NET framework code. You might hear the term managed code to refer to CLR as well. Well, now in SQL 2005 you can write stored procedures, triggers, user defined functions in managed code. Because the managed code compiles to the native code prior to it being executed, you can achieve significant performance increases if used wisely.
Now, managed code also follows the SQL Server authentication and authorization rules so you can’t use managed code to access data that the SQL Server user normally would not have access to. The thing to keep in mind is that T-SQL is very good at retrieving and manipulating sets of data, but it is not a procedural programming language. CLR can handle some calculations and complicated logic better than T-SQL. I have enabled this on the bulk of my servers and I have not had any issues, it can be a very powerful thing. You do have to keep in mind that you might want to use T-SQL for retrieving data – or you wouldn’t want to use CLR for retrieving data, you want to use T-SQL for the things that it does best.
Helping You When Your Server Becomes Unresponsive
The DAC, the dedicated administrator connection sounds like such a great idea and I was very excited when I first heard about it. What the purpose of the DAC is is to help you when your server becomes unresponsive. On those times when you have a run away query and you can’t log in to find out what’s going on, you can use the DAC to connect to the SQL Server to troubleshoot what’s actually happening. Now, there are some drawbacks to the DAC, one of them is that you can only have one connection at a time so you can only have one administrator connection to your SQL Server.
The DAC will connect only to the default database so you want to make sure that your connection is connecting to the master database. You cannot run parallel queries like a restore and a backup. Now, I had an occasion that I used the DAC a couple months ago and the one thing, the server was unstable and my first thought whenever I encounter a situation like that is I want a backup and I want to get a backup immediately. But unfortunately I couldn’t use the DAC to create that backup because there are limited resources that are available. Keep in mind that the DAC is not available to Express, and by default only local connections are allowed.
If you are not in the physical presence of your server you need to enable remote connections here. Now, I’ve never enabled this on my servers, but I work in a data center so it's not a problem for me to run up and log into the console. However, if your server is in a remote location you might want to enable this.
SQL Mail's Replacement Is...?
Database mail. Database mail is also another new feature of SQL 2005 and it is a replacement for SQL mail. It’s such a huge improvement on SQL mail, it actually works and you don’t need to install Outlook or another mappy client on your server to use it. The nice thing about database mail is it also allows you to create multiple profiles within a SQL Server instance so that you can have a profile that will send your administrative alerts and alerts about how the SQL Server is performing, and then you can have a completely separate profile so that your accounting department and your marketing department can send e-mails from. Now you also have the option of making the profiles public or private. You would want to keep you administrative profile private, but then allow your sales and marketing profiles to be public so that the users could connect to that.
Database mail also supports fail over SMTP. You can, if one SMTP server is unavailable then you can have it direct to deliver from another server. Database mail is cluster aware and 64 bit compatible. The other nice thing is that database mail is a process that exists outside of the SQL Server. If database mail for some reason got hung up it’s not going to impact your SQL Server. You can limit the types of files and the size of those files that you can send with database mail.
And finally the database mail logs all activity, all e-mails that it sends so that you can audit and see who’s doing what. It’s really a nice feature in SQL 2005.
-----------
This article was taken from the session presentation at the SSWUG.ORG virtual conference. Please be sure to see the other parts of this article, if any, linked at the top of this article. For additional information about the SSWUG.ORG virtual conference and expos, please see http://www.sswug.org/uvc
