The SQLCMD Tool and SQL Server Management Objects
Typing commands at prompt and automating repetitive SQL Server administration related tasks
The SQLCMD Tool
Many DBMS include command line tools that allow DBAs and users to work with the system by typing commands at a prompt. SQL Server 2005 contains a powerful new command-prompt utility called SQLCMD . This tool communicates with the database server using the OLE DB protocols. SQLCMD may be used to run database programming related and maintenance related scripts with input parameters, ad hoc queries and more. One of the most important features of the SQLCMD mode is preallocated connection bandwidth. Consider a server that is 'hung' or stuck on some erroneously executing slow process. SQLCMD may be used to obtain a guaranteed connection to the stalled server. The DBA may use system monitoring tools called Dynamic Management Views to find the process that is consuming all the CPU and delete it without having to shut down and restart the server.
The SQL Server Query Editor that opens when the 'New Query' icon is clicked may be used to develop SQLCMD scripts. Simply turn on the SQLCMD mode by clicking 'SQLCMD' in the 'Query' drop down menu. SQLCMD can also be invoked from the DOS command prompt by typing 'SQLCMD'.
SQL Server Management Objects
SQL Server Management Objects (SMO) may be used to automate repetitive or frequently executed SQL Server administration related tasks such as new database creation, T-SQL script execution, SQL Server Agent Job creation, backups and so on. SMOs are implemented as sets of .NET assemblies. SMOs may be used to create custom SQL Server instance manipulation applications and web based applications.Windows Management Instrumentation
SQL Server configuration may be managed through a Windows Management Instrumentation (WMI) configuration provider. DBAs may write management programs and scripts to monitor, configure, and control information about SQL Server by utilizing the WMI framework. For example, a DBA may save a snapshot of all the configuration settings of a SQL Server instance into an Excel file using WMI
Important Management Components
