Sections:
|
Firewalls
Configuring the Windows Firewall to Allow SQL Server Access
msdn.microsoft.com/en-us/library/cc646023.aspx Labels:
firewalls, mssql, SQL Server, DBA Storage
For optimized I/O parallelism, use 64 KB or 256 KB stripe size.
Clustering
This article describes some important do's and don'ts for using SQL Server clustered servers with the different
versions of SQL Server.
Labels:
cluster, SQL Server, DBA, toread Connections
Configuring Client Network Protocols Client applications can connect to Microsoft SQL Server using the TCP/IP, named pipes, VIA, or shared memory protocols. The protocols are implemented using a client network library contained in the SQL Server Native Client dll. To configure SQL Server clients, use SQL Server Configuration Manager, which is a new Microsoft Management Console (MMC) snap-in, available from the Start menu, or within the Computer Management utility. SQL Server Configuration Manager provides information about your client network protocols and allows you to configure some protocol options. SQL Server Configuration Manager also lets you change the default network protocols and define how to connect to specific servers. How To Set the SQL Server Network Library in an ADO Connection String
support.microsoft.com/kb/238949 SQL Server allows you to use the following network
libraries when establishing a connection. They are:
Labels:
connection strings, SQL Server Backup/Restore
Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database. Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.
For more information, see Copy-Only Backups. In general, it is best to use a local disk, not a network drive, for backups, and then copy the data later. Use compression when you can, but when you use compression with backups, be mindful not to overwhelm SQL Server. For example, SQL LightSpeed compresses during backup which can disrupt SQL Server performance.
DMVs and DMFs
Labels:
mssql, upgrade, DBA, DMVs sys.dm_os_performance_counters (Transact-SQL) Returns a row per performance counter maintained by the server. For information about each performance counter, see Using SQL Server Objects. Dynamic Management Views and Functions (Transact-SQL)
msdn.microsoft.com/en-us/library/ms188754.aspx SQL Server Browser Service
How SQL Server Browser Works
When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or "pipe," is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4. When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance. Using SQL Server Browser
If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433. However, if the SQL Server Browser service is not running, the following connections do not work:
Transaction Log
In some circumstances the transaction logs can get quite large and not knowing what is in the transaction log or how much space is being used can become a problem. So how to you determine how much of the transaction log is being used and what portions are being used?
Labels:
transaction log, mssql, DBA, toread Downloads
Download details: SQL Server 2008 Report Builder 2.0
www.microsoft.com/downloads/details.aspx?familyid=... Report Builder 2.0
Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 Reporting Services. The download provides a stand-alone installer for Report Builder 2.0 Download details: SQL Server Health and History Tool (SQLH2)
www.microsoft.com/downloads/details.aspx?familyid=... The
Microsoft SQL Server Health and History Tool (SQLH2) allows you to
collect information from instances of SQL Server, store this
information, and run reports against the data in order to determine how
SQL Server is being used.
Labels:
tools, todownload, toread Download details: RML Utilities for SQL Server (x86)
www.microsoft.com/downloads/details.aspx?familyid=... RML Utilities for SQL Server (x86)OverviewDo you know which databases or applications are using the most resources on your server? How will a service pack upgrade, configuration change or application change affect your production SQL Server? The RML Utilities for SQL Server provide you a set of tools and processes to answer these questions and much more. The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see: You can also test how the system will behave with some change (different service pack or hotfix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture. Database Optimizations
There are two cases when SQL Server will not automatically create column statistics, even when they fit the criteria described above. These include: when the costs needed to create the column statistics are larger than the costs of the query plan; and when SQL Server is too busy.
One way to tell if the statistics for a column are missing or outdated, or if the accuracy of current statistics is not ideal (they may not be ideal if the sample size of the statistics are not high enough), is to run a graphical execution plan of a query as an estimated plan and as a real plan. In other words, you will run both an estimated and an actual graphical execution plan using Query Analyzer, and then compare the results. When you run an estimated graphical execution plan for a query, you get results, such as the estimated row count returned. When you run an actual graphical execution plan for a query, you get the actual row count returned. If the statistics for the column(s) used by the query are current and accurate, then the estimated row count and the actual row count should be very similar. If they are wildly different, this can mean three things. Either the statistics are missing, out of date, or not accurate enough. Configuration
Need to figure out what privileges the account needs in order for the SQL Server 2005 VSS Service to startup properly.
Pricing and Licensing
Microsoft SQL Server: SQL Server Pricing and Licensing
www.microsoft.com/sql/howtobuy/sqlserverlicensing.... SQL Server Pricing and LicensingDocument detailing the licensing of SQL Server 2005. Labels:
licensing |
Important: