abhishek[CPM]
(Moderator): Welcome to the India Community Webchat on
- SQL Server 2005 Tools Exposed abhishek[CPM]
(Moderator): The Expert for Today: Vinod Kumar who is an
MVP in SQL Server abhishek[CPM] (Moderator):
He also runs a very popular website called at: www.extremeexperts.com abhishek[CPM] (Moderator): The
second expert of the Day is: Srinivas Sampath - MVP in SQL Server
as well abhishek[CPM] (Moderator):
I am Abhishek Kant - Community Program Manager and will be the moderator
for today abhishek[CPM] (Moderator):
we are going to start in another 5 mins as more people can join in abhishek[CPM] (Moderator): In
the meantime wanted to tell you about 2 resources on SQL Server that
you can access for your questions: abhishek[CPM]
(Moderator): 1. Microsoft Forums at: abhishek[CPM] (Moderator):http://forums.microsoft.com
where you can discuss issues related to development on SQL Server
e.g. MARS, ADO.NET, Data Caching, Connection Pooling etc abhishek[CPM]
(Moderator): 2. Microsoft Newsgroups where you can discuss
about any SQL Server related issues like Administration, Replication,
Development etc abhishek[CPM] (Moderator):
Finally if you are a SQL Server enthusiast you can join the SQL Server
User Group in your city. A list of User groups in your city is located
at: http://www.microsoft.com/india/communities/usergroups/default.aspx abhishek[CPM] (Moderator): Let
us welcome the experts for today and hand over the floor to them!
Welcome Vinod and Srinivas! Vinod_SQL
(Expert): A: Hi All Vinod_SQL
(Expert): A: Nice to see you all for this Chat
over SQL Server Tools abhishek[CPM] (Moderator):
At this time, please feel free to post your questions to experts using
the "Ask the Expert" checkbox located next to the "Send"
button Vinod_SQL (Expert): A: Srinivas will join soon. Vinod_SQL
(Expert): A: So to start with the first set
of Tools for SQL Server 2005 Vinod_SQL
(Expert): A: There are actually more than a
dozen tools officially released from MS Vinod_SQL
(Expert): A: And many third party tools have
also been developed over some of the SQL Server 2005 Features Vinod_SQL (Expert): A:
Disclaimer: SQL Server 2005 is going through its last beta cycles.
Some minor changes in these management tools may occur before release.
But this chat is based over the experience from the June CTP that
was released. Vinod_SQL (Expert): A: I had to make that Disclaimer though Vinod_SQL
(Expert): A: So the First tool we will concentrate
is SQL Server Management Studio Vinod_SQL
(Expert): A: Also commonly called as SSMS in
short Vinod_SQL (Expert): A: SQL Server 2005 delivers an entirely new set of management
tools for both DBAs and developers. Vinod_SQL
(Expert): A: With the addition of some new
services in SQL Server 2005, service management is becoming a more
important area of DBA concern. Vinod_SQL
(Expert): A: The new SQL Server Management
Studio replaces both the SQL Server 2000 Enterprise Manager and the
Query Analyzer. Vinod_SQL (Expert): A: You need to be aware of two supporting tools: the SQL
Server Configuration Manager and the Surface Area Configuration tool.
But we will dwel upon them later Vinod_SQL
(Expert): A: Management Studio also provides
integrated management of Reporting Services, Notification Services,
XML, SQL Server 2005 Mobile Edition (formerly code-named Laguna),
and multiple versions of SQL Server from the same interface for increased
DBA productivity, flexibility, and manageability. Vinod_SQL
(Expert): A: Fundamentally the SSMS is built
over the VS Shell and hence the infrastructure that this base provides
is multifold. Vinod_SQL (Expert): A: For SQL Developers one of the limitation that the tools
had was the integration with a SourceControl. Vinod_SQL
(Expert): A: But this has been in the Visual
Studio environment for ages. Now even the SQL Developer can enjoy
these benifits. Vinod_SQL (Expert): A: This is from a high level overview. There are some
cool features that the tool as such also provides. Some include: Vinod_SQL (Expert): A:
Now we can filter on a specific node. This improves usability of the
tool in general for sure rather than scrolling 1000's of objects Vinod_SQL (Expert): A:
All operations that invoke a window to configure will have a Script
Option now. I would strongly recommend to use this feature. Vinod_SQL
(Expert): A: You can script to Clipboard, File
or Query Analyzer window. This way operations like creating user etc
can be scripted and added to Source Control in the initial stage rather
than as a separate activity at the end. Vinod_SQL
(Expert): A: The tool also allows easy search
over the MSDN and more importantly also allows to search community
sites and post the query from the tools window itself. Vinod_SQL
(Expert): A: A number of 20+ management reports
have been integrated to the tool. This allows administrators to get
a quick snapshot. Vinod_SQL (Expert): A: Moreover these are reports seen from the desktop without
any additional rights over the server. Vinod_SQL
(Expert): A: Walking on the same lines there
another tool called as Business Intelligence Development Studio or
the BI Tool Vinod_SQL (Expert): A: Primarily this is a development utility, this program
is intended for creating Business Intelligence solutions, Vinod_SQL
(Expert): A: integrating projects involving
Analysis Services, Reporting Services, and Data Transformation Services. Vinod_SQL (Expert): A:
Similar to SQL Server Management Studio, its interface is solution
oriented, with each solution grouping individual projects, such as
reports (utilizing Reporting Services) or DTS packages. Vinod_SQL
(Expert): A: The tool also provides integration
with Visual SourceSafe. Vinod_SQL (Expert): A: So you can see that for disconnected type of requirements
the BI is used and for a connected systems we use the SSMS tools infrastructure Vinod_SQL (Expert): A:
Any questions on using SSMS and BI tools? Vinod_SQL
(Expert): A: So letme get to the next interesting
tool Vinod_SQL (Expert): The
basic functions of a profiler remains the same. But there have been
some useful additions made to this tool. Vinod_SQL
(Expert): One of them is the ability to analyze execution
of MDX Analysis Services statements, extending the scope of monitoring
beyond SQL Server activities only Vinod_SQL
(Expert): Some interesting additions include: Vinod_SQL
(Expert): Permission to run the utility is no longer limited
to members of SysAdmin fixed server role. This has been something
all the DBA's would love to hear. Vinod_SQL
(Expert): Instead they can be granted by including designated
logins in the Analysis Services server role Vinod_SQL
(Expert): Events of a specific type can be extracted and
stored as corresponding file types. Vinod_SQL
(Expert): Showplan and Deadlock trace files can be saved
in XML format, and subsequently loaded for analysis in SQL Server
Management Studio. These two are special cases though. Vinod_SQL
(Expert): Deadlocks can be displayed in a graphical view,
simplifying analyzing the problem, by presenting more clearly the
processes, types of access requests, and requested objects Vinod_SQL
(Expert): You can also integrate Profiler and the PerfMon
data and find what statement was executing when the Processor spiked
or when the memory went on a hig / low value etc. Vinod_SQL (Expert): Related link
include: http://www.extremeexperts.com/SQL/Yukon/DeadLockDetection.aspx Vinod_SQL (Expert): Any questions
on the Profiler Tool? Vinod_SQL (Expert):
Let us move to the next tool if there are no questions Vinod_SQL
(Expert): There are multiple command line tools that we
have with SQL Server. I would try to talk over the SQLCMD utility
here though. Vinod_SQL (Expert):
This is the next generation tool to be used moving forward replacing
the osql used previously. Vinod_SQL (Expert):
Having said that the osql still exists for backward compatibility
but this new transformed tool is interesting in its own way. Vinod_SQL
(Expert): SQLCMD.EXE has a number of functional enhancements,
accommodating the creation of more elaborate scripts,
which can significantly simplify automation of a majority of administrative
tasks. Let me list some of the interesting things we can do: Vinod_SQL
(Expert): Prior to connecting to a SQL Server instance
using SQL Server authentication, it is possible to set the SQLCMDPASSWORD
environment variable this is applied for the duration of the subsequently
invoked session. Vinod_SQL (Expert):
You can assign values to custom and predefined scripting variables
to be used within scripts invoked from the command line. Vinod_SQL
(Expert): Options :error , :out, :perftrace allow you to
spool the output, errors, perfmon data alone separately as the batch
is executed. Vinod_SQL (Expert):
This is also an interactive commandline option. Vinod_SQL (Expert): We can also
connect to SQL Server in a DAC (Dedicated Admin Connection), -A option.
This is a connection that is available to the administrator when the
Server is non-responsive. More on DAC at: http://www.extremeexperts.com/SQL/Yukon/DAC.aspx Vinod_SQL
(Expert): Even though we can create our own variables,
some useful predefined variables include and not limited to: Vinod_SQL
(Expert): 1. SQLCMDSERVER and SQLCMDDBNAME - set with -S
and -d switches, designate the target SQL server and database (respectively)
to connect to. Vinod_SQL (Expert):
2. SQLCMDWORKSTATION - set with -H switch, assigns the name that will
appear in the hostname column of the sys.processes catalog view. Vinod_SQL (Expert): 3. SQLCMDUSER
and SQLCMDPASSWORD - set with -U and -P switches, determine the credentials
to be applied for connection to a target server. Vinod_SQL
(Expert): It is possible to execute SQLCMD scripts within
Query Editor by switching it to SQLCMD mode. Vinod_SQL
(Expert): Some links include: http://www.extremeexperts.com/sql/Yukon/sqlcmdUtility.aspx http://www.sswug.org/see/23045 Vinod_SQL (Expert): Any Questions
on the command line tool? Vinod_SQL (Expert): Q: are there any command level tools available in SQL
Server 2005? A: We are just discussing them. Till now,
you have seen SSMS, SQLCMD and Profiler. Out of these SQLCMD is the
command line tool that can help you control SQL Server 2005 and issue
queries from a command shell. Vinod_SQL
(Expert): Ok. Looks like there are no questions. This is
Srinivas Sampath. Am using Vinod's machine for now... Vinod_SQL
(Expert): The next tool that we will see is called "Database
Mail". Vinod_SQL (Expert):
It is not a tool per se, but a feature in SQL Server 2005 that allows
you to send emails from the database. Vinod_SQL
(Expert): Just to give you a background, SQL Server 2000
had a feature called SQL Mail that allowed you to send mails from
the database. Vinod_SQL (Expert):
For example, if you want to report the progress of a procedure, locking
information etc, you could use SQL Mail. Vinod_SQL
(Expert): However, SQL Mail had its own share of issues
and the biggest one was that it required an "Outlook" profile
to send its mail. Vinod_SQL (Expert):
The designers of this feature listened to all the customer complaints
and came up with a new solution in SQL Server 2005. Vinod_SQL
(Expert): Enter Database Mail, which is a new high performance
tool for sending emails and is intended as a replacement for SQL Mail. Vinod_SQL (Expert): Database Mail
has several new features, and these can be grouped under availability,
scalability, security and supportability. Vinod_SQL
(Expert): We will quickly examine each of the new features
under these headings. Vinod_SQL (Expert):
Availability Vinod_SQL (Expert):
1. Database mail now uses SMTP (no outlook dependency) and this is
its biggest feature Vinod_SQL (Expert):
2. Mail calls are made outside of the SQL Server process. Its a separate
executable (DatabaseMail90.EXE) Vinod_SQL
(Expert): 3. Cluster support is built-in for Database Mail Vinod_SQL (Expert): 4. There is
support for SMTP account failover. You can configure upto 256 different
email servers Vinod_SQL (Expert):
5. No SQLCLR dependency. The outside process uses CLR, but at the
DB level, there is no dependency Vinod_SQL
(Expert): 6. Stored in MSDB (7 tables named sysmail%) Vinod_SQL (Expert): Just to explain
option (2). Basically, the mail program (the one that sends the mails
off) runs outside the process of SQL Server. This ensures that any
failures in the external process, will not compromise SQL Server. Vinod_SQL (Expert): Scalability Vinod_SQL (Expert): 1. Asynchronous
and queued architecture (using Service Broker) Vinod_SQL
(Expert): 2. Database Mail uses the concept of profiles.
A profile is a collection of email accounts. Vinod_SQL
(Expert): 3. You can configure multiple profiles and each
such profile can point to different servers, thus allowing you to
scale your solution Vinod_SQL (Expert):
Security Vinod_SQL (Expert):
1. Profiles can be Public or Private. Vinod_SQL
(Expert): 2. A public profile is visible to all users,
whereas a private profile is available to only select users (governed
by permission) Vinod_SQL (Expert):
3. Database Mail tracks all system events, sent emails Vinod_SQL
(Expert): 4. There is also a facility for controlling the
size of attachments and to disallow unwanted file extensions Vinod_SQL
(Expert): 5. Mail capability off by default (this is as
per the trustworthy computing initiative from MS) Vinod_SQL
(Expert): From a manageability perspective, all of the
intricate details of Database Mail are abstracted in a well structured
UI that you can use. This UI is available in SSMS from the Management
menu. In case you still want control (for those who love to do things
using T-SQL :-)), you can still use system procedures for the same. Vinod_SQL (Expert): Functionality Vinod_SQL (Expert): 1. Emails
with query results and file attachments (possible only in Windows
Authentication) Vinod_SQL (Expert):
2. The integration with SMTP provides new features and capabilities
like: Vinod_SQL (Expert):
2a. Allows customized "From" and "Reply-To" Vinod_SQL (Expert): 2b. Supports
HTML in email body Vinod_SQL (Expert):
2c. Allows specifying "importance" and "sensitivity" Vinod_SQL (Expert): 2d. Supports
Unicode Vinod_SQL (Expert):
3. Used by SQL Server Agents for alerts Vinod_SQL
(Expert): 4. Supported in the SMO object model Vinod_SQL
(Expert): Ok. That's about the basic feature set. Now,
if you want to use this feature, what do you do? Vinod_SQL
(Expert): 1. Enable it using SQL SAC (Surface Area Configuration
tool) Vinod_SQL (Expert):
2. Use Management > Database Mail to start the wizard Vinod_SQL
(Expert): 3. Create a profile and associated multiple SMTP
accounts to it Vinod_SQL (Expert):
4. Start sending emails using the sp_send_dbmail procedure. You can
get a detailed account of this procedure from books online. Vinod_SQL
(Expert): Ok. The question that people who have earlier
used SQL Mail will have now is: What about SQL Mail? Vinod_SQL
(Expert): 1. For now, it is still shipped in SQL Server
2005, but it will be officially depricated Vinod_SQL
(Expert): 2. Database Mail is not a 100% replacement for
SQL Mail, but it provides a highly efficient and scalable solution
for sending emails from the database. Vinod_SQL
(Expert): Some of the features that Database Mail does
not support are: Vinod_SQL (Expert):
1. Does not support reading or deleting of emails (may be a future
update) Vinod_SQL (Expert):
2. Does not provide synchronous operation (may be a future update) Vinod_SQL (Expert): Ok. That brings
me to the end of this tool. Any questions before I move on to the
next tool? Vinod_SQL (Expert):
Ok, let's move on. The next tool that we will see if called "Server
Management Objects (SMO)" earlier called DMO in SQL Server 2000. Vinod_SQL (Expert): We will quickly
see what are some of the new features of this tool. Vinod_SQL
(Expert): SQL Server Management Objects (SMO) are objects
designed for programmatic management of Microsoft SQL Server. Vinod_SQL (Expert): You can use
SMO to build customized SQL Server management applications. Vinod_SQL
(Expert): Although SQL Server Management Studio is a powerful
and extensive application for managing SQL Server, there might be
times when you would be better served by an SMO application. Vinod_SQL
(Expert): SMO extends the DMO model and uses concepts and
nomenclature similar to DMO. Thus, transferring of skills to SMO is
quite easy. Vinod_SQL (Expert):
SMO does not support compatibility level 60 or 65. This means that
you cannot manage SQL Server 6.5 or any database set to that compatibility
in a higher version of SQL Server. Vinod_SQL
(Expert): Q: Srinivas..Can you give an example
where SMO can be used? A: Sure. Typically, you wold use
the management studio to perform most of your tasks, but for example,
if you want to write a program that executed its own scripts in SQL
Server and also perform maintenance task (without using the SSMS UI),
SMO provides a good alternative. For example, SSMS itself is written
using SMO! Thus, you can see the power that it puts in your hands. Vinod_SQL (Expert): New features
in SMO include the following: Vinod_SQL
(Expert): 1. Cached object model and optimized object instance
creation. Vinod_SQL (Expert):
1a. Objects are loaded only when specifically referenced. Vinod_SQL
(Expert): 1b. Object properties are only partially loaded
when the object is created. Vinod_SQL
(Expert): 1c. The remaining objects and properties are
loaded when they are referenced directly. Vinod_SQL
(Expert): 2. Batched execution of Transact-SQL statements.
Statements are batched to improve network performance. Vinod_SQL
(Expert): 3. Capture Transact-SQL statements. Allows any
operation to be captured into a script. Vinod_SQL
(Expert): 4. Management of SQL services with the WMI Provider.
SQL services can be started, stopped, and paused programmatically. Vinod_SQL (Expert): 5. Advanced
Scripting. Transact-SQL scripts can be generated to re-create SQL
Server objects. Vinod_SQL (Expert):
SMO also represents as new objects or properties a number of features
and components new to SQL Server 2005 like: Vinod_SQL
(Expert): 1. Table and index partitioning for storage of
data on a partition scheme. Vinod_SQL
(Expert): 2. HTTP endpoints for managing SOAP requests. Vinod_SQL (Expert): 3. Snapshot
isolation and row level versioning for increased concurrency. Vinod_SQL (Expert): 4. XML Schema
collection, XML indexes and XML datatype provide validation and storage
of XML data. Vinod_SQL (Expert):
5. View point databases for creating read-only copies of databases. Vinod_SQL (Expert): 6. Service
Broker support for message-based communication. Vinod_SQL
(Expert): 7. Synonym support for multiple names of SQL
Server database objects. Vinod_SQL (Expert):
8. The management of Database Mail Vinod_SQL
(Expert): 9. Registered servers support for registering
connection information. Vinod_SQL (Expert):
10.Notification Services for sending and subscribing to notifications
events. Vinod_SQL (Expert):
11.Support for certificates and keys for security control. Vinod_SQL
(Expert): 12.DDL triggers for adding functionality when
DDL events occur. Vinod_SQL (Expert):
SMO uses the Microsoft System.Data.SqlClient object driver to connect
to and communicate with instances of Microsoft SQL Server. Vinod_SQL
(Expert): To develop applications with SMO, you must have
Microsoft Visual Studio 2005 installed. Vinod_SQL
(Expert): We cannot get into all the capabilities of SMO,
simply because its huge. However, Vinod and myself are planning to
give a session on each tool at the Bangalore .NET user group very
soon. So, if you are in Bangalore, do not miss it! Vinod_SQL
(Expert): Ok. Am now handing it over to Vinod to continue
with his set of tools. Vinod_SQL (Expert):
Interestingly there is another tool that needs attention Vinod_SQL
(Expert): The Database Tuning Advisor (DTA), replaces the
Index Tuning Wizard (ITW), Vinod_SQL
(Expert): providing a richer, more scalable experience
that leverages the core concepts we introduced in the ITW in SQL Server
7.0. Vinod_SQL (Expert): To
improve productivity, we've separated the UI from the tuning engine. Vinod_SQL (Expert): With this
new tool, DBAs can leave a tuning session, close the UI, and come
back to it later or have multiple sessions tuning at the same time,
each session potentially running against different servers. Vinod_SQL
(Expert): DTA can also recommend a partitioning strategy
which was missing in the SQL Server 2000 version. Vinod_SQL
(Expert): Indexes on Indexed views too are an added bonus
in this new version Vinod_SQL (Expert):
We represents Microsoft Vinod_SQL (Expert):
As you can see the effect has gone beyond just index recommendation
now the tool is more specific to the Databases tuning. Vinod_SQL
(Expert): So any questions on DTA or any other tools? Vinod_SQL (Expert): We are almost
time to wrap up. Vinod_SQL (Expert):
Some interesting links for your bookmarking: Vinod_SQL (Expert): SQL Management
Tools: http://www.windowsitpro.com/SQLServer/Article/ArticleID/46798/46798.html
Tools WebCast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032263411&EventCategory=5&culture=en-us&CountryCode=US
Microsoft SQL Server: SQL Server 2005 Features Comparison: http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx
Sample - Chapter 2: SQL Server Management Studio -http://www.yukonxml.com/chapters/apress/ss2005revealed/mgmtstudio/
Profiler and Trace in SQL Server 2005 - http://www.only4gurus.com/v3/preview.asp?ID=8073 Vinod_SQL (Expert): Can also check
My Web space at : www.ExtremeExperts.com and Srinivas Sampath's site
at: http://www32.brinkster.com/Srisamp Vinod_SQL (Expert): And catch
both of us blog: http://blogs.sqlxml.org/ Vinod_SQL (Expert): If you are
in Bangalore. Do drop by http://groups.msn.com/SQLBang
- SQL Server Usergroup Vinod_SQL (Expert):
We can talk elaborately over each tool Vinod_SQL
(Expert): Thats it from us for this week. hope to see you
all at SQLBang Meeting or the Next chat Vinod_SQL
(Expert): Bye all abhishek[CPM]
(Moderator): Thanks everyone for joining in the chat!
