SQLServer 2005 -- Dynaic Management Views

The dynamic management views (DMVs) in SQL Server 2005 are designed to give you a window into what's going on inside SQL Server. They can provide information on what's currently happening inside the server as well as the objects it's storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000. This article provides an introduction to DMVs and covers a few of the basic views and functions.

The Dynamic Management Views are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. They have been broken up into twelve categories:

Common Language Runtime Related Dynamic Management Views

I/O Related Dynamic Management Views and Functions

Database Mirroring Related Dynamic Management Views

Query Notifications Related Dynamic Management Views

Database Related Dynamic Management Views

Replication Related Dynamic Management Views

Execution Related Dynamic Management Views and Functions

Service Broker Related Dynamic Management Views

Full-Text Search Related Dynamic Management Views

SQL Operating System Related Dynamic Management Views

Index Related Dynamic Management Views and Functions

Transaction Related Dynamic Management Views and Functions

SQL Server 2005 Performance Dashboard Reports

Free report from Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Overview
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

AAP Certification

AAP (Accredited ACH Professional) Certification Exam
Overview:
120 Multiple Choice Questions
Including 20 non-graded pilot questions
3 Hours to Complete


Test Makeup:
General payment overview 10%
Marketing & products 8%
Risk management 15%
Rules and regulations 24%
Operational process/flow 28%
Technical standards/formats 15%

Granularity of Locks

Hierarchical lockable units:

assume set of resources to be locked is organized in a hierarchy, e.g. (database, areas, files, records) as a simple hierarchy. Each node of hierarchy can be locked, and in doing so, one implicity locks all of that node's descendants. There is shared access (S, shared-read access) and exclusive access (X, exclusive read/write access).
In order to lock a subtree rooted at node R in S or X, one must prevent others from obtaining incompatible locks on any ancestor of R. A new mode, "intention mode" is introduced to do this - all ancestors of a node are tagged with intention mode before the node itself is locked. Summary of modes:
null (NL): no access
intention-share (IS): allows requestor to lock descendant nodes in S or IS mode. (does no implicit locking)
intention-exclusive (IX): allows requestor to explicitly lock descendants in X, S, SIX, IX, or IS mode. (does no implicit locking)
share (S): access to node and all descendants without setting further locks. (implicitly sets S locks on all descendants)
share and intention exclusive (SIX): implicitly locks all descendants of node in share mode and allows requestor to explicitly lock descendant nodes in X, SIX, or IX mode. (for finer grained locking)
exclusive (X): exclusive access to node and all descendants. (implicitly sets X locks on all descendants)

To request a node,
before requesting S or IS on node, all ancestor nodes must be held in IX or IS mode.
before requesting X, SIX, or IX on node, all ancestors must be held in SIX or IX mode.
locks should be released either at end of transaction (in any order), or in leaf to root order in the middle of a transaction.

Source from: http://swig.stanford.edu/pub/summaries/database/locks.html

Detach and Attach a database

--Step 1 : Detach Database using following script
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks',
@keepfulltextindexfile=N'true'
GO

--Step 2 : Move Data files and Log files to new location

--Step 3 : Attach Database using following script
USE [master]
GO
CREATE DATABASE [AdventureWorks] ON
( FILENAME = N'C:\UserData\AdventureWorks_Data.mdf' ),
( FILENAME = N'C:\UserData\AdventureWorks_Log.ldf' )
FOR ATTACH
GO

IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N'AdventureWorks'
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [AdventureWorks].dbo.sp_changedbowner @loginame=N'sa',
@map=false
GO

Voice of Customer

The "voice of the customer" is a process used to capture the requirements/feedback from the customer (internal or external) to provide the customers with the best in class service/product quality. This process is all about being proactive and constantly innovative to capture the changing requirements of the customers with time.

The "voice of the customer" is the term used to describe the stated and unstated needs or requirements of the customer. The voice of the customer can be captured in a variety of ways: Direct discussion or interviews, surveys, focus groups, customer specifications, observation, warranty data, field reports, complaint logs, etc.

This data is used to identify the quality attributes needed for a supplied component or material to incorporate in the process or product.


What is Focus Group?

Focus groups are not polls but in-depth, qualitative interviews with a small number of carefully selected people. brought together to discuss a host of topics.

Unlike the one-way flow of information in a one-on-one interview, focus groups generate data through the give and take of group discussion. Listening as people share and compare their different points of view provides a wealth of information — not just about what they think, but why they think the way they do.

SQL Server High Availability Technologies

Extracted from: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#EVKAG

SQL Server 2005 now has at least four high availability technologies, and while there is some overlap, each technology has its own relative advantages and disadvantages. Those technologies are

• Database Mirroring - For this discussion, we will be considering the High Availability operating mode with safety FULL and a witness.

• Failover Clustering - The most typical configuration is a two-node Windows failover cluster with one SQL Server instance.

• Log Shipping - Assume SQL Server built-in log shipping with a separate monitoring.

• Transactional Replication - Consider a configuration with a separate distribution server and one subscriber which serves as the standby server if the publisher server fails.

Database Mirroring and Clustering
The most important contrast between database mirroring and failover clustering is the level at which each provides its redundancy. Database mirroring provides protection at the database level, whereas clustering provides protection at the server instance level. Another important difference is that in database mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, whereas a SQL Server instance on a cluster gets one virtual server name and IP address that remains the same no matter what node of the cluster is hosting the instance.

If you need database protection at the server level (for example, your application requires access to many databases on the same database server simultaneously), failover clustering may be a more appropriate choice. However, if you are concerned to provide availability for one database at a time, database mirroring has a number of advantages.

Unlike clustering, database mirroring does not require proprietary hardware and does not have a potential failure point with shared storage. Database mirroring brings the standby database into service much faster than any other high availability technology, and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.

You cannot use database mirroring within a cluster, but you may consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be forewarned that because a cluster failover is longer than the timeout value on database mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.

Database Mirroring and Transactional Replication
Database mirroring and transactional replication are both based upon reading the transaction log of an originating server, but after that their technologies diverge considerably. (For more details about transactional replication, see the associated topics in SQL Server Books Online.) Transactional replication is often used for high availability because it can deliver user transactions from a publisher database to a subscriber in matters of seconds. Database mirroring has the advantage that it is as fast as or faster than replication, but delivers all of a database's transactions, not just those related to user tables.

Transactional replication is an appropriate technology for scaling out data to multiple subscribers for reporting. Transactional replication subscriber databases are normally considered read-only anyway, so they are ideal candidates when access to near-real-time data is required.

Database mirroring is compatible with transactional replication, and is most useful as method of keeping a hot standby of a publisher database. Other methods of protecting a replication publisher, such as log shipping, cannot keep a standby server for the publisher ahead of the publisher's own subscribers. In other words, transactional replication can deliver transactions to its subscribers much faster than a transaction log backup scheme. Because database mirroring is so fast, it is much more suitable for keeping a hot standby of a publisher database.

If the publisher should fail, however, you will have to manually re-establish the recovered standby database as the publisher, and reconnect it to the distribution server, just as you must currently do if using log shipping to maintain a publisher server standby.

Database Mirroring and Log Shipping
Database mirroring and log shipping both rely on the restore and recovery capabilities of SQL Server databases. A database mirroring mirror database is in a constantly recovering state, more or less continuously replaying transactions from the principal. A log shipping secondary replays transactions applied to it periodically from transaction log backups. Because bulk-logged data is appended to a transaction log backup, log shipping can work in the bulk-logged recovery model. Database mirroring, on the other hand, transfers log records directly from the principal to the mirror and cannot deliver bulk-logged data.

In many cases database mirroring can provide the same kind of data redundancy as log shipping with higher availability and automatic failover. However, if your application relies on multiple databases on one server, log shipping may be an equally valid approach (see "Multi-Database Considerations" in the previous section).

Additionally, there are database mirroring scenarios where log shipping may supplement availability. For example, you could have a High Availability database mirroring configuration in-house, and log ship the principal server to a remote site for disaster recovery purposes. Figure 24 illustrates how such a configuration might take place.

SQL Server Performance in a SAN/NAS Environment

SAN and NAS Systems

SAN and NAS systems provide different types of storage from traditional internal or direct attached storage. A SAN (Storage Area Network) is designed to provide access to storage over a private fibre channel network. A NAS (Network Attached Storage) is used to provide storage over a standard network.

SAN SYSTEMS

A SAN system is an external storage system that allows multiple computer systems to access the same storage. The RAID controller inside the external storage system is able to take requests for different logical volumes within the storage system from different HBAs (Host Bus Adapters). This allows for several different features. One of the most common uses of a SAN is for storage consolidation. This is where multiple systems share the disks in the external storage subsystem. This allows for consolidation of storage resources and management.

With storage consolidation, even though the storage in the external disk subsystem is shared among the different systems, it is not entirely accessible to all systems. Logical disks are carved out of the physical disk drives and allocated to each of the computer systems. Only one system can access a particular disk volume.

Another use for a SAN system is for clustering. Failover clusters use a shared disk subsystem that allows one of two systems to access the same storage.

SAN Performance Considerations When putting together a SAN system you must not only look at the I/O traffic that is being generated by one system, but the I/O traffic being generated by all systems in the SAN. So in addition to sizing the I/O subsystem disk drives and RAID levels, you must look at the traffic on the SAN itself, as well as keeping in mind that other systems might be accessing the same RAID controllers. It is also possible to run into bandwidth limitations on the SAN itself, since fibre channel has a limited bandwidth.

NETWORK ATTACHED STORAGE (NAS) SYSTEMS

Network attached storage is similar to the SAN system in that the brains of the storage is external to the computer system. However, unlike the SAN system where the storage is connected via a fibre channel connection, a NAS system is accessed via the network.

NAS System
Although the NAS system is supported under SQL Server, the NAS system usually cannot support the performance required by SQL Server unless you use a sufficiently fast network interface. The speed of the NAS is usually limited by the speed of the network interface.

NAS Performance Considerations The NAS is different from the SAN in that multiple systems can access the same NAS storage simultaneously, whereas in a SAN, typically only one system accesses a particular file system at a time. So, the NAS may have performance problems related to too many people accessing the storage at the same time.

However, the main issue with NAS is that the path that the I/O must take typically is much longer than that of a SAN. This is due to the fact that the NAS has network protocols involved that must be processes as well as file system overhead.

SQL Server Connection String

SQL ODBC connection strings
Standard Security:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"

Trusted connection:< br> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"
SQL OLE DB connection strings
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL OleDbConnection .NET strings
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL SqlConnection .NET strings
Standard Security:
1. "Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;" < br>2. "Server=Your_Server_Name;Database=Your_Database_Name;UserID=Your_Username;Password=Your_Password;Trusted_Connection=False"

Trusted connection:
1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
2."Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"

ACH Return Code

http://www.solutran.com/resources/ach_return_codes.htm

VSS 101-- Label, Share, Branch, Share and Branch

Label
Labeling is like a milestone or checkpoint, which can be applied to all files in a project, and all subsidiary projects. This label can be used later to retrieve all of the files at that milestone.

Share
The Share menu option lets you place a file in multiple branches of the SourceSafe tree. A shared file does not “belong” to any one project more than any other. A shared file is simply pointed to from multiple locations, from different project hierarchies where they hold link (reference) to.

Branch
Branching breaks the link on shared files. Use branching when you want to perform changes on a file in one project and not have the changes affect the other projects into which the file is shared.
Braches can be merged later if necessary.

Share and Branch
This just takes a copy of the shared project and starts its own project, so it will get a totally independent copy of the library and the changes you make won’t make the least bit of difference. Both projects have shared past but different future.


Reference for VSS basics:
http://portal.dfpug.de/dfpug/Dokumente/Partner/Hentzenwerke/Essential%20SourceSafe%20Chapter%2002.pdf

IIS Commands to Create Web Sites and Virtual Directory

REM: create web site using specified app pool and port
iisweb /create "C:\Inetpub\wwwroot\MyWebSite" "My First Web Site" /b 5009 /ap MyAppPool

REM: create virtual directories under the web site and use the specified app pool
iisvdir /create "My First Web Site" MyWebApplication "C:\Inetpub\wwwroot\MyWebSite\MyWebApplication"

VBScript to Create IIS Application Pool

strAppPool = "MYNEWAPPPOOL"
Set objAppPools = GetObject("IIS://localhost/W3SVC/AppPools")
Set objAppPool = objAppPools.Create("IIsApplicationPool", strAppPool)

' 0 = Local System
' 1 = Local Service
' 2 = Network Service
' 3 = Custom Identity -> also set WAMUserName and WAMUserPass
objAppPool.AppPoolIdentityType = 3

' Note:WamUserName must be added to the IIS_WPG group to have correct security rights

objAppPool.WAMUserName = "USERNAME"
objAppPool.WAMUserPass = "PASSWORD"

objAppPool.SetInfo

How to reverse an ACH file, ACH batch, or ACH record?

There are several steps to edit the original ACH file:

1. if reverse the whole file, no need to change the file header record - "1" record.
2. change all the "5" records, replace the description field with "REVERSAL". ACH service class code (e.g. 200) might have to be changed depending on its value.
3. change all the "6" records to swap the transaction code for each of them. Swap transaction 22 to 27 and swap 27 to 22. (or 32 to 37 and vice-versa)
4. change all the "8" records to swap the position of the amount. If it is debit amount, change it to the position of credit amount, and vice-versa.
5. change all the "9" records with updated batch counts, entry/addenda counts and credit / debit total.
6. keep the rest of information the same as the original value.

a quick ACH format reference on web:

http://members.aol.com/getmydata/pskb/NACHA-ACH-CCD-Format.htm

SQL ERRORLOG

SQL Server logs events (although only certain system events and user-defined events) to the SQL Server error log . Use the information in the error logto troubleshoot problems related to SQL Server.

There are quite a few important events like starting up server/database.shutting down of server, recovery of the databases etc. which are getsloggedinto SQL server logs. These logs are very helpful to do troubleshooting.

Every time SQL Server is started, the current error log is renamed toerrorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3,and so on

Use sp_cycle_errorlog which will close the current errorlog file and willcreate a new one for you.
use master
execute sp_cycle_errorlog

Execute permissions for sp_cycle_errorlog are restricted to members of the sysadmin fixed server role. MS Link : http://msdn2.microsoft.com/en-us/library/ms182512.aspx