Follow SQLMaster on Twitter
Welcome to SqlServer-QA.net Sign in | Help

SQL Server 2008 R2 Monitoring Management Pack - Operations Manager

If your corporate environment is monitored by System Center Operations Manager 2007 then here is the update to download the Monitoring Management Pack. This pack of SQL Server Management Pack provides the capabilities for Operations Manager 2007 SP1 and R2 to discover SQL Server 2005, 2008, and 2008 R2. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

SQL Server Monitoring Management pack download page refers the summary as follows:

Feature Summary:
The following list gives an overview of the features of the SQL Server management pack. Refer to the SQL Server management pack guide for more detail.

  • Support for Enterprise, Standard and Express editions of SQL Server 2005, 2008, and 2008 R2 and 32bit, 64bit and ia64 architectures.
  • Support for both simple and complex SQL configurations such as clustered installations, multiple instances and 32bit roles running on a 64bit OS. For full details on supported configurations refer to the guide included with the management pack.
  • Discovery and monitoring of SQL Server roles such as DB Engine, Reporting Services, Analysis Services, Integrations Services.
  • Discovery of SQL components such as databases, the SQL Agent and SQL jobs.
  • Views covering areas such as database free space, SQL related performance, SQL related alerts, and lists of the various SQL roles and components which are discovered and their related state.
  • Discovery and basic monitoring for SQL Server Reporting Services and Integration Services.
  • Reports for longer-term analysis of common problem areas related to SQL Server such as SQL Server lock analysis and top deadlocked databases, SQL Server service pack levels across discovered roles, user connection activity. Likewise the generic reports from the Microsoft Generic Report Library can be used against the roles and components discovered by the SQL MPs to review availability and performance across many systems and over longer periods of time.
  • Role and component specific tasks which provide access to common tools, triage information, or corrective actions without needing to leave the Operations Console in most cases.
  • Monitoring of databases covers database status, database free space, log shipping monitoring for both the source and destination, and transaction log free space.
  • Monitoring of key SQL related services.
  • Monitoring for persistent SPID blocking.
  • Monitoring of numerous SQL events and performance data points. Alerts bring the issue to your attention and provide knowledge on the impact and possible resolutions.
  • A low-privilege configuration for discovery and monitoring that eliminates the need for SQL sysadmin, dbo, db_owner, and box admin privileges.


Further the documentation refers that this SQL2008R2 management pack is designed to monitor the SQL Server 2005, 2008, and 2008 R2 in the following configuration:

  • 32-bit SQL Server on 32-bit operating system
  • 64-bit SQL Server on 64-bit operating system. For SQL Server 2005, only SP2 or later is supported
  • 32-bit SQL Server on 64-bit operating system

Only a subset of monitoring will work for 32-bit installations of SQL Server that are monitored on a 64-bit operating system. Please see MP guide and Knowledge Base article 891238 for detail. 
 

 

Released: Cumulative Update #11 for SQL Server 2005 Service Pack 3

If you are following the patching of your SQL Server instances with Cumulative Updates then here is another instalment available for testing & deployment.

To obtain and apply this CU #11 hotfix release for SQL Server 2005 SP3 you must apply the Sp3 for SQL Serve 2005 instance. Here is the KBA913089 to get the latest service pack on SQL Server 2005. For more information on the list of files that are updated as a part of this CU11 release, see KBA2258854 link, also there is an information about SQL Native Client update as a part of this CU#11 update see below:

File information for the stand-alone package of SQL Native Client that is released together with Cumulative Update 11 for SQL Server 2005 Service Pack 3

Also another advantage feature that is added to all Cumulative Update packages to uninstall if there are any issues at later stage where you need to get back the environment o SQL Server 2005 Sp3 (or as per your build state) you can :

  1. In Control Panel, open the Add or Remove Programs item.
  2. Click Change or Remove Programs .
  3. To see all the updates for the SQL Server 2005 installation, click to select the Show Updates check box.
  4. Uninstall the cumulative hotfix package.

Finally KBA960598 gives the list of builds that were released after SQL Server 2005 Service Pack 3 was released.Further to obtain the SQL Server 2008 R2 CU3 release you have to create a seperate support service request (usual support costs will apply, as per the support contract between your company & Microsoft).

SQL Server - What is Transaction log file? Can I delete it? Think about Database Recovery Model too!

This was the question asked by a developer, due to a particular process fills up the transaction log so quickly.

I had to explain the reason and tricks that can reduce the transaction log bulge using best recovery methods on the database. Going back to basics there certain FAQ about Transaction log usage within SQL Server.

 

Transaction Log - why it is required?

A log is a seperate file that stores all kinds of information about the SQL Server instance and databases. Each database has its own log file (one or more) and this (these) log file(s) are required to keep a history of the transactions that hit the database, so that the database can be recovered to a state of both physical and logical consistency in case of failure. Irrespecive of the recovery models when a particular process of transaction is executed against the database, then the transaction is first recorded in the log file, and then actual data pages, that need to be modified, are brought into memory.

 

Recovery Model - What is it?

Recovery models are designed to control transaction log maintenance. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. Books ONine defines the following table summarizes these recovery models.

Recovery model Description Work loss exposure Recover to point in time?

Simple

No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

Can recover only to the end of a backup.

Full

Requires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error).

Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For more information, see Restoring a Database to a Point Within a Backup.

Bulk logged

Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.

Can recover to the end of any backup. Point-in-time recovery is not supported.

 

Going back to first question about recovery model option, at certain intervals of time a background process, known as a CHECKPOINT runs, which flushes the dirty pages (modified pages in memory) to the disk.

 

How the Transaction log file is accessed by a database?

This is more important topic if you want to know internals of how the transaciton log file is accessed.

The access mode of a transaction log file is always sequential, for the space management the architecture of Transaction log is divided in to the logical segments which are called - Virtual Log Files (VLF). So the each log file is divided into multiple VLFs, and when you run DBCC SQLPERF(LOGPSACE) the amount of %age free space is nothing all of these VLFs are empty and ready to be used. So when the database or SQL Server is accessed the LOG WRITER action of process takes the first VLF and when it is full the writing process continues to the second and so on. When the last VLF is reach and when 70% of this last VLF is filled up it triggers the growth-of-file process whereby the default value of 10% of actual size of transaction log file will be actioned.

The growth of transaction log file is always dependent upon the size of value you have mentioned in the database properties. Also the recovery model that has been obtained, in general users think that if SIMPLE is adopted then LOG is truncated and no growth is expect, ITS WRONG. As soon as the last VLF is accesss the trigger will be ready to increase the log file size further and depending upon the transaction size (if its OPEN) then log is set to grow further until the information is fit.

 

Not always the VLF writing method will be sequential, say if the log file has 20 VLFs and if the odd number of VLFs are empty and as per the order of Log Writer looking for free VLFs it will search for any inactive VLF in the chain and in case VLF 3,5 & 6 are free then it will start writing into VLF 3. Once all the VLFs are full then log writer will trigger a log file growth, thereby creating more VLFs at the expense of disk space. 

 

What is the best recovery model to choose?

Going back to RECOVERY model question, it is ideal to speak about choosing the suitable recovery model for a database.

The answer is 'it depends' for the production instances. The SIMPLE recovery model is appropriate if the server is classified as Test/Development. For the produciton either FULL or BULK-LOGGED (for bulk copy activities) is ideal to maintain the point-in-time recovery. To decide the optimal recovery model for a particular database, you should consider both the recovery goals and requirements for the database and whether you can manage log backups. See Books Online for the topic 'Recovery Models (SQL Server) --> Choosting the Recovery Model for a Database topic.

 

What are log truncation and shrink method?

Log truncation is nothing but when a statement of BACKUP ... WITH NO_LOG or TRUNCATE_ONLY  (discontinued from SQL Server 2008 R2) the tail of the log will be truncated without writing to the disk by marking a VLF as an inactive, which will have a mismatch of LSN when you perform a transaction log backup.

However, the SHRINK method works differently to truncate method, where it will reduce the number of VLFs in the log file to claim the free space on disk. Such as based on the SHRINK value sya 50% reduction and if the database log file has 20 VLFs then it will be reduce to 10 VLF numbers.

 

Further to the above here is the excellent reference from technet article that talks about how it works in case the database is in SIMPLE or BULK-LOGGED recovery model: 

If a database is in simple recovery model, a VLF will be truncated only when all the conditions below are fulfilled:

  • All transactions that have begun in the specific VLF has been either committed or rolled back.
  • All transactions, that have at least one command written into the specified VLF have either been committed or rolled back. Assuming that a transaction has started in VLF 5 and has continued to VLF 7 through VLF 6, it has to be either committed or rolled back before VLF 6 can be truncated.
  • A checkpoint has run on the database.
  • All transactions, that have at least one command written into the specified VLF have been replicated.

If a database is in bulk-logged recovery model or full recovery model, a VLF will be truncated only when all the conditions below are fulfilled:

  • All transactions that have begun in the specific VLF has been either committed or rolled back.
  • All transactions, that have at least one command written into the specified VLF have either been committed or rolled back. Assuming that a transaction has started in VLF 5 and has continued to VLF 7 through VLF 6, it has to be either committed or rolled back before VLF 6 can be truncated.
  • A checkpoint has run on the database.
  • The VLF in question has been backed up by taking a log backup after the checkpoint has run.
  • All transactions, that have at least one command written into the specified VLF have been replicated.

The 2 useful statements you need to see are DBCC SQLPERF(logspace) to know the %age of log file is in use and DBCC LOGINFO(DatabaseName)that gets status of each VLF in the log file. A status of 0 means that the VLF has already been truncated and can be overwritten. A status of 2 means that the VLF has active transactions and cannot be overwritten.

 

SQL Server database maintenance: How to find out the execution duration of ONLINE Index build operation?

The question is tricky as the answer too!

Online index operation (Enterprise Edition specific) is the new addition in SQL Server 2005, the simple operation behind the scenes will be preparation, build and final, it is possible to perform the concurrent online index DDL operation on the same table or views when you are creating multiple nonclustered indexes (new) and in same case of reorganizing such nonclustered indexes. But you cannot simply create another index ONLINE when another index online exists on same table. Although not necessarily that online index operating can cause a deadlock when a corresponding UPDATE statement is executed, in such cases the DB engine will select the user application activity as DEADLOCK victim. 

There is no difference on the amount of disk space used by ONLINE or OFFLINE index operations, in both cases the process is treated as large-scale operation that generates data loads can cause transaction log to grow quckly. Until the operation is finished the Transaction log will continue to grow and cannot be truncated, but you can simply carry over to backup the transaction log which this operation is continued. As usual there are certain guidelines that are applied before you consider it on your environment:

  • You cannot rebuild clustered indexes ONLINE when underlying table contains LOB data types (IMAGE/NTEXT or XML etc.), so the operation must carry over as OFFLINE.
  • Indexes on local temp tables cannot be created, rebuilt or dropped online.

ALTER INDEX ALL ON <TABLENAME> REBUILD WITH(ONLINE = ON)

If you want to rebuild a specific index on a table you can run the following:

ALTER INDEX idx_TableIndex_name ON <TABLE NAME> REBUILD WITH(ONLINE = ON)

Such an advantage of running ONLINE index operation during the live hours is an advantage for performance, but also bit of degradation if there is any high activity on table that may cause DEADLOCK or sometimes slow performance issues for end-users. REcently there was a question posed in usergroup meeting and also posted among few forums, which caught my attention that how best we can derive the execution times of a particular ONLINE INDEX operation.  

PROFILER is the simple answer, though another overhead of running this during ONLINE hours and you can enable a server-side trace to obtain such an information.

Within the Events Selection go to Progress Report class and sub-class of' Progress Report: Online Index Operation' which gives the indication of progress of an online index rebuild operation, as usual this is a long running process due to the other activites on database server this event class an be used to view the progress of such ONLINE build operation when it is executed. Its like watching the paint dry!

 

 

SQL Server log messages: Analysis of database is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

BACKUPS are key to recover the data during the disaster recovery, but many times just taking backups is not sufficient as you need to ensure the backups are good enough with no corruption.

The subject line message though classified as an information message, recently one of the databases were taking long time to finish the recovery whenever the SQL Server has been restarted and also during the RESTORE operations. Not an issue if this a Development environment but in the case of agreed downtime this will have further issues to complete the RECOVERY and database will be online. In a way such informational messages are good for DBAs to be alert on future issues, but examining the SQL Server error log indicates that significant time has been spent by the server to analyze and recover the specified database recovery process.

Whenever the SQL instance has been restarted or RESTORE execution or ATTACH database process is executed then initial stage will begin for database recovery process and during this operation SQL Server proforms a discovery of all Virtual Log Files present in all the Transaction Log files. At this point such process of verification can take a very long time depending upon upon the number of Virtual Log Files present in the specific database. A database can end up with a large number of Virtual Log Files if the Transaction Log encounters frequent auto-grow with the growth happening for very small size increments. Finally we could find the root cause for this problem is due to I/O problem that has caused database corruption, to avoid this I have added CHECKSUM option on the database & during the backup operation.

SQL Server 2005 has given a new option during the BACKUP operation using CHECKSUM option, which is useful for error detection method when the PAGE_VERIFY database option either to CHECKSUM or TORN_PAGE_DETECTION is set on the database. As a background process SQL will always check for any I/O based corruptions, so the CHECKSUM or TORN_PAGE_DETECTION value needs to be set in order to calcuate with the original value set on the page. In the beginning it was assumed that BACKUP.... WITH CHECKSUM is an alternative to perform CHECKDB related actions, but not this is only useful to perform the error management options to determine whether backup checksums are enabled for the backup operation and whether the operation will stop on encountering an error. It is worth to talk about how the process works, when CHECKSUM keyword is used in BACKUP statement then it will enable the backup checksums, so that BACKUP can do the following:

  • Prior to writing a page to the backup media, BACKUP verifies the page (page checksum or torn page), if this information is present on the page.
  • Regardless of whether page checksums are present, BACKUP generates a separate backup checksum for the backup streams.
  • Restore operations can optionally use the backup checksum to validate that the backup is not corrupted.
  • The backup check is stored on backup media, not on the pages of database.

Also you can use the conditional execution of backup by using { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } to control whether a backup operation stops or continues after encountering a page checksum error. Bear in mind the backup checksum will affect the workload & backup throughput refer to Sankar's_blog_post in this regard and How-tocheck-if-backups-CHECKSUM post.

This is good in terms of CHECKSUM during the backup operation, if the backup checksums are present on media then RESTORE operation will also folllow same route. Both RESTORE & RESTORE VERIFYONLY operations will perform the checksum on backup & page areas. Though the RESTORE VERIFYONLY (see BOL information) verifies the backup and unless the backup checksum is enabled & performed the restore cannot reliably verify the PAGE CHECKSUM. As per BOL if there is any explicit request CHECKSUM for a restore operation and if the backup contains backup checksums, backup checksums and page checksums are both verified, as in the default case. However, if the backup set lacks backup checksums, the restore operation fails with a message indicating that checksums are not present.

     

     

    SQL Server: What is the internal representation of all the datatypes?

    That is the question from SSP forums asking about the internal representation of data types in SQL Server. Here is further explanation for that questions where I picked up new information too.

    Back to the basics, that all the data that needs to be stored must be compatible with the data types in SQL Server, also the data objects that are derived from source objects such as expressions are assigned to the base data type. Also BOL has enormous information to talk about it, a data type is an attribute that specifies the type of data that object hold such as integer/character/monetery type of data. SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types.

    There was further explanation by the OP that how the initial representation about data types, it is read that float is stored as binary and decimal, numeric is stored as number itself. So I want to learn how the value of each datatype is stored(Structured), that is better than initial question.

    The usage of appropriate data types must be designed at earlier states where the type to each column and data value allowed for that column. By default SQL Server system data types needs to be used, depending on the requirement you can even create user-defined data type by creating alias data types that are based on system data types or using UDT from .NET framework common language runtime too. The data integrity is the important aspect of data design methods, so that the entered data or any changes to existing data can comply with the type that has been specified. As in comparing apples to apples: keep the number based data on numeric columns & alphabetic in CHAR or VARCHAR data types. Here is the compiled list of table that represents different data types & their storage sizes:

    Character data types

    Data Type Length Storage Size Max Characters Unicode

    char

    Fixed

    Always n bytes

    8,000

    No; each character requires 1 byte

    varchar

    Variable

    Actual length of entry in bytes

    8,000

    No; each character requires 1 byte

    nchar

    Fixed

    Twice n bytes

    4,000

    Yes; each character requires 2 bytes

    nvarchar

    Variable

    Twice actual length of entry in bytes

    4,000

    Yes; each character requires 2 bytes


    Integer data types

    Data type Minimum value Maximum value Storage size
    tinyint 0 255 1 byte
    smallint -32,768 32,767 2 bytes
    int -2,147,483,648 2,147,483,674 4 bytes
    bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 8 bytes

     

     Precision storage requirements (Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.)

    Total characters (precision) Storage size
    1 - 9 5 bytes
    10 - 19 9 bytes
    20 - 28 13 bytes
    29 - 38 17 bytes

     

    Float and real data type restrictions

    Data type n Minimum Value Maximum value Precision Storage size
    float(n) 1 - 24 -1.79E + 308 1.79 + 308 7 digits 4 bytes
    25 - 53 -1.79E + 308 1.79E + 308 15 digits 8 bytes
    real n/a -3.40E + 38 3.40E + 38 7 digits 4 bytes

    Smalldatetime and datetime restrictions

    Data type Minimum value Maximum value Accuracy Storage size
    smalldatetime January 1, 1900 June 6, 2079 Up to a minute 4 bytes (the first 2 bytes store the date; the second 2 bytes store the time)
    datetime January 1, 1753 December 31, 9999 One three-hundredth of a second 8 bytes (the first 4 bytes store the date; the second 4 bytes store the time)

     

    Smallmoney and money restrictions

    Data type Minimum value Maximum value Storage size
    smallmoney -214,748.3648 214,748,3647 4 bytes
    money -922,337,203,685,477.5808 922,337,203.685,477.5807 8 bytes

    XML data type is entirely a different league when compared to usual core standard type of data storage, a brief note from documentation that "The data type of an element or an attribute can be specified in an XDR schema. When an XDR schema is used to extract data from the database, the appropriate data format is output as a result of a query. The dt:type and sql:datatype annotations are used to control the mapping between XDR data types and Microsoft SQL Server data types."

    All SQL Server data types are converted into string representations. Some data types require additional conversions. The following table lists the conversions that are used for various dt:type values.

    XML data type SQL Server conversion

    bit

    CONVERT(bit, COLUMN)

    date

    LEFT(CONVERT(nvarchar(4000), COLUMN, 126), 10)

    fixed.14.4

    CONVERT(money, COLUMN)

    id/idref/idrefs

    id-prefix + CONVERT(nvarchar(4000), COLUMN, 126)

    nmtoken/nmtokens

    id-prefix + CONVERT(nvarchar(4000), COLUMN, 126)

    time/time.tz

    SUBSTRING(CONVERT(nvarchar(4000), COLUMN, 126), 1+CHARINDEX(N'T', CONVERT(nvarchar(4000), COLUMN, 126)), 24)

    All others

    No additional conversion

     In addition to the above topics SQL Server 2008 has new programmability features where by new set of data types are introduced:

    FILESTREAM Storage

    Sparse Columns and Column Sets

    Spatial Data Types

    User Defined Types (UDT)

    Wide Tables

    Date and Time Data Types

    hierarchyid Data Type

    So it is a continutation topic which si covered in future blog posts and the above information should give satisfactory response for the question on the subjectline. Before closing here are new references:

    Identify the owner of a data type: (BOL) 
    SELECT TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId') AS owner_id, name, system_type_id, user_type_id, schema_id
    FROM sys.types;
    Return the precision of a data type (for instance tinyint) 
    SELECT TYPEPROPERTY( 'tinyint', 'PRECISION');
     
     

    SQL Server Installation : SMO related problems and client toosl issue - 'Microsoft.SqlServer.Management.Smo.Table.UrnSuffix' not found

    A typical problem occurs when SQL Server client tools do have any issues.

    There is no easy way to say that SSMS (client tools) are corrupted, unless you can reproduce the same issue on other machine it cannot be assured. If it is a bug from tools you can easily file them on Microsoft connect site and preferably search for similar problem before posting a new one. 

    Here is an interesting issue happened on my laptop and not on other desktop when I was trying to connect to a SQL intance (2008 version). As a part of testing I was trying to add a new login to that instance from Management Studio from the laptop which presented with an error :Method 'Microsoft.SqlServer.Management.Smo.Table.UrnSuffix' not found. (SQLEditors).

    I was able to add the login and do the rest of stuff using scripts within the SSMS tool but not through certain steps using GUI. As referred tried to reproduce the issue from another machine where no issues reported, so that confirmed that client tools on laptop are stuffed. So attempted to repair the installation of SQL Server on my machine, well not an easy option from SQL Server Installation Center when used setup. Next thought was to uninstall the client tools & reinstall, which isn't a viable option to go.

    So here is the process I followed which is a reliable way to correct any corrupted files on your SQL Server tools:

    Launch Installation Center -> Maintenance -> Repair

    On the SELECT INSTANCE screen, select only "Repair shared features only" option from the drop down list.

    As a part of repair process I have restarted the laptop to ensure all the changes are affected for good, it does work. Just an additional note that if you are unable to recover the problem with this REPAIR method then you have to opt the way to uninstall & reinstall of tools and services again.  You need to perform the uninstall from Control Panel --> Add/Remove Programs, on the SELECT INSTANCE screen,  select only "Remove shared features only" option from the drop down list. Make sure that you have un-checked the necessary boxes that might even uninstall the SQL Server services. 

    There is a note about actual SSMS and SSMSE (for SQL Express editions), for the earlier you have to go by SQL setup media that was used and for SSMSE you can download from http://msdn.microsoft.com/en-us/library/ms365247.aspx here to reinstall.

    Edit:

    Also I fould few refereces about SMI installation from this link: http://msdn.microsoft.com/en-us/library/ms162189.aspx  and you can obtain SMO management featuer pack from SQL Server 2008 R2 Feature Pack, get yours now link.

    tyntVariables = {"ap":"Read more: ","as":"@sqlmaster"};

    SQL Server Connnection errors: "No process is on the other end of the pipe" or "An existing connection was forcibly closed by the remote host"

    This is a typical error during the connection attempt to a SQL Server 2005 instance, this morning I was faced with similar issue at workplace.

    Initially I was confused why it is specifying the error: NO process is on the other end of pipe, which gives the thought about using Named-pipes protocol for that SQL instance. To ensure the TCP/IP and/or Named-pipes are enabled I have gone through SQL Server Surface Area Configuration tool as below:

    Start > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Suraface Area Configuration

    Click the Surface Area Configuration for Services and Connections link at the bottom of the window.

    In the View By Instance pane on the left, expand the node for the SQL Server that is giving you the error, and click the Database Engine > Service > Remote Connections.

    In the setting for the Remote Connections, click the Local and Remote Connections option.

    The details for remote connections for Tcp/IP & Named-pipes are enabled, and still unable to connect to the specific SQL instance. Further to this settings I have also ensured that SQL Server services are up & running, specified TCP/IP port is open having exception within Firewall to allow and no reported issues on DNS from server side. Still no luck as the connection was terminated with the above error messages.

    A thought of what changes occured recently has comeup in the mind, so remotely logged on to the server to check recent installation of Windows Update,  using start --> progams --> Windows update option --> check under 'Review your update history' pane. The recent changes for windows update on the server are as follows, which were tested before deployment:

    Microsoft .NET Framework 3.5 SP1 and .NET Framework 2.0 SP2 Update for Windows Server 2003 and Windows XP x86 (KB982524)

    Windows Malicious Software Removal Tool - June 2010 (KB890830)

    Microsoft .NET Framework 3.5 SP1 Update for Windows Server 2003 and Windows XP x86 (KB982168)

    Further to this checkout I have had a breif discussion with deployment team about any recent changes on the server, which explains the reason of certificate installation required for the application from web-server on SQL Server. This gives a lead to resolution for the problem as KBA 919710 refers the root cause that:

    This problem occurs because a certificate that has the AT_SIGNATURE key specification is used for Secure Sockets Layer (SSL) encryption for the instance. A certificate that has the AT_SIGNATURE key specification cannot be used for SSL encryption in SQL Server 2005. On a computer that is running Microsoft Windows Server 2003, the certificate is recognized as not valid. Therefore, the SQL Server service does not load the certificate, and the service does not start. However, on a computer that is running Windows XP or Windows 2000, the SQL Server service loads the certificate, and the service starts successfully. This behavior causes the connection to fail.

    The problem has been resolved after applying the certificate on SQL Server using SQL Server configuration manager tool, to achieve this you can go to Start --> Programs --> SQL Server 2005 --> SQL Server configuration Manager and right-click on SQL isntance to choose 'Certificate table' where you can see the list of certificates that are validated by SQL Server 2005 and load the required ones, lastly don't forget to restart that SQL Server instance.

    Finally we got around the issue by uploading valid certificate for the authentication to connect from web server, you need to make sure if you are using self-signed SSL certificate for you IIS then refer to http://hansolav.net/blog/SelfsignedSSLCertificatesOnIIS7AndCommonNames.aspx post too for further information. Just a note on process that you must use encrypted connection, you need to see a good certificate for SQL Server 2005 notes & SQL Server 2005 Encryption - Certificates 101 good one too.

    Slipstream and StreamInsight - SQL Server new features for your advantage

    Slipstream and StreamInsight, for a second both of them sound similar and you may think they are same too.

    For my session at Microsoft Tech-Ed North America 2010 - SQL Server Upgrade, 2000 - 2005 - 2008: Notes and Best Practices from the Field we had a good feedback from the users, waiting to get answer for their questions (after the session) and clarifying their doubts from the Database Platform booth at Tech-Ed Exhibition hall. Keeping that aside  I had that embarassing moment (for a second) when I was staffing at Tech-Ed NA 2010 Database Platform booth then a user drop-by and asked about StreamInsight. I thought correct answer for that and prounounced different answer about SlipStream!!!

    Another question that was asked about, do you have support to perform SlipStream for SQL Server 2005, the answer is YES & NO. SQL Server 2005 supports slipstreaming for hotfixes on RTM version but not for SP1 or SP2 or SP3, so that leads to 2005 version does not support slipstreaming of service packs. But there is a way to answer that problem, you can use a third party tool called InstallAware that can help to enable one-click deployment of your SQL Server 2005 environment.

    Still with SQL Server 2008 during the setup  you might encounter few issues such as when you run the original release version of SQL Server 2008 Setup, the Setup program copies itself on the local computer, and then re-runs from the local copy. Therefore, if there is a later version of the support files on the computer, the Setup program will run these updated files. Therefore, you can update the SQL Server 2008 Setup support files before you run the Setup.exe file.

    So this is where SlipStream comes handy from SQL Server 2008 Service Pack 1 onwards with a procedure or install to an existing SQL Server 2008 installation, an entry is created for the Service Pack in Add or Remove Programs. You can uninstall the service pack by using this entry. Another thought is you can easily use the MAP kit [ Microsoft Assessment Planning Toolkit 4.0 http://technet.microsoft.com/en-us/solutionaccelerators/dd537566.aspx] to verify that whether a service pack has been installed properly from the installation center with un-attended installation mode. The report from MAP kit delivers you about version 10.n.xxxx, where n represents the service pack version. For example, 10.1.xxxx represents Service Pack 1.

    Also there are 2 methods that you can use to update the installation fo SQL server 2008, one is slipstream and creating a merged drop. For sure Slipstream is easy to manage having the benefits of quick update to SQL Server 2008 SP1 in a single instance, reduce the restart of instances and improve the setup to avoid any known setup issues. Peter Saddow has given another good explanation about Create a merged (slipstream) drop containing SQL Server 2008, Server Pack 1 and a Cumulative Update (CU) based on Server Pack 1.

    Finally about StreamInsight I will explain on the other blog section, as it suits better there.

     

    Error: 17308, Severity: 16, State: 1. LazyWriter: Process 4 generated an access violation. SQL Server is terminating this process

    Transparent Data Encryption (TDE) is the good feature that has been enhanced in SQL Server 2008, that will help  secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers.

    Whenever you consider the security of data platform you will think about passwords, access (physical) to the server and chances are high that a malicious party can just restore or attach the database and browse the data. So thats where the data encryption comes into picture and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.

    Previously the encryption methods were expensive in terms of scalability & performance,but TDE reduces that overhead by performing real-time I/O encryption and decryption of the data and log files. BOL refers that "The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module". It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

    IN the recent times when testing the TDE based settings following messages are logged in the SQL Server ErrorLog.

    spid51      Setting database option ENCRYPTION to ON for database 'DATABASENAME' spid3        Beginning database encryption scan for database 'DATABASENAME'.

    SqlDumpExceptionHandler: Process 4 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    Error: 17308, Severity: 16, State: 1.
    LazyWriter: Process 4 generated an access violation. SQL Server is terminating this process.

    Whenever the above messages are logged the SQL instance becomes unresponsive and few times I had to restart SQL Server services, just a background about affinity I/O mask information where it is involved within TDE settings. SQL Server supports processor affinity by means of two affinity mask options: affinity mask (also known as CPU affinity mask) and affinity I/O mask. For more information on the affinity mask option, see affinity mask Option. CPU and I/O affinity support for servers with 33 to 64 processors requires the additional use of the affinity64 mask Option and affinity64 I/O mask Option respectively.

    In general it is not recommended to set I/O settings unless they have been tested fully, so for such high-end SQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL Server threads issuing I/Os. For TDE related settings it is compulsory to ensure that the affinity I/O mask values are set to default values, as the testing has been performed on a high-end server with changes to such configuration settings that has been reverted back to default settings.

    A search on Knowledge Base articles revealted that the Access Violation messages may also occur because of the incorrect synchronization between the logreader and lazywriter  threads when TDE has been set on a SQL Server instance. By design SQL Server 2008 and SQL Server 2008 R2 environments do not support setting IO affinity mask to non-default values if you also want to enable TDE for databases on this instance of SQL Server

    Finally when enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.  FOr more information on TDE see Understanding Transparent Data Encryption (TDE)  & 298402  INF: Understanding How to Set the SQL Server I/O Affinity Option

    The SQL Server 2005 evaluation software is no longer available. Learn more about the enhancements and new features in SQL Server 2008

    Well it is a surprise (rather a bad news) for the SQL Server users, especially those who wanted to evaluate the SQL Server 2005 edition.

    From SQL Server 2005 downloads resources you can obtain:

    ....but when you access SQL Server 2005 Trial Software to download the free, 180-day trial software you will be presented with "The SQL Server 2005 evaluation software is no longer available. Learn more about the enhancements and new features in SQL Server 2008".

     

     

     

    SQL Server 2008 R2 Feature Pack, get yours now

     In continuation to the SQL Server 2008 R2 RTM release here is the another download the Feature Pack which consist the a collection of stand-alone packages which provide additional value to your R2 environment.

    Get the SQL Server 2008 R2 Feature pack  and here is the notes from that link for more information:

    It includes the latest versions of:

    • Redistributable components for Microsoft® SQL Server® 2008 R2.
    • Add-on providers for Microsoft® SQL Server® 2008 R2.

    Note: Some components in the Microsoft® SQL Server® 2008 R2 Feature Pack of SQL Server® have certain Internet-enabled features, including the Customer Experience Improvement Program and Error and Usage Reporting component, enabled by default to help us identify and address issues that you may encounter while using SQL Server®. For more information and instructions on how to disable these features, see the SQL Server® Privacy Statement

    Note: Some components in the Microsoft® SQL Server® 2008 R2 Feature Pack of SQL Server® have two different methods to complete the installation process:

    • Attended Installation – an interactive user interface will guide you through the set-up process and obtain all of the information and consent required to complete the installation. This includes the displaying, acknowledgement, and archiving of the applicable SQL Server license terms.

    • Un-attend Installation – this is a DOS command Line set-up process that does not have an interactive user interface to guide you through the set-up process. In this case, you will be required to enter a parameter during installation that indicates your acceptance of the license terms.

     

    The following components are available for download as part of the Feature Pack.

    • Microsoft® SQL Server Report Builder 3.0 for Microsoft® SQL Server 2008 R2
    • Microsoft® SQL Server® PowerPivot for Microsoft® Excel
    • Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft® SharePoint® Technologies 2010
    • Microsoft® SQL Server® 2008 Reporting Services Add-in for Microsoft® SharePoint® Technologies 2007
    • Microsoft® SQL Server® 2008 R2 Policies
    • Microsoft® Sync Framework 2.0 Software Development Kit (SDK)
    • Microsoft® SQL Server® Compact 3.5 SP2
    • Microsoft® SQL Server® Compact 3.5 SP2 For Windows Mobile
    • Microsoft® SQL Server ®Compact 3.5 SP2 Server Tools
    • Microsoft® SQL Server® Compact 3.5 SP2 Books On-line
    • Microsoft® SQL Server® JDBC Driver 3.0
    • Microsoft® Connect 1.1 for SAP BW for SQL Server® 2008 R2
    • Microsoft® System CLR Types for SQL Server® 2008 R2
    • Microsoft® SQL Server® 2008 R2 Remote Blob Store
    • Microsoft® SQL Server® 2008 R2 Books On-line
    • Microsoft® SQL Server® 2008 R2 Upgrade Advisor
    • Microsoft® SQL Server® 2008 R2 Native Client
    • Microsoft® Core XML Services (MSXML) 6.0 SP 1
    • Microsoft® OLEDB Provider for DB2
    • Microsoft® SQL Server® 2008 R2 Command Line Utilities
    • Microsoft® SQL Server® Service Broker External Activator for SQL Server® 2008 R2
    • Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2
    • Microsoft® SQL Server® 2008 R2 Shared Management Objects
    • Microsoft® SQL Server® 2008 R2 ADOMD.NET
    • Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2
    • Microsoft® SQL Server® 2008 R2 Analysis Management Objects
    • Microsoft® SQL Server® 2008 Data Mining Add-ins for Microsoft® Office 2007
    • Microsoft® Datamining Viewer Controls For Microsoft® SQL Server® 2008
    • Microsoft® SQL Server® Driver for PHP 1.1
    • Microsoft® SQL Server® Migration Advisor
    • Microsoft® SQL Server® 2008 R2 Best Practices Analyzer

    SQL Server Certification - a database platform primer for your career path

    When you need to upgrade your knowledge then training is required, at the same time certifications will help you to keep up on what you have learned!

    There is a big debate on the web about whether certifications are important in your career or not, the bottomline is if you do not know the stuff or unable to answer few basic technical questions, it does'nt matter how many certifications you have then you will not get the job, well I'm not starting the same discussion here.  But in the recent times I was presented with the question about SQL Server certification path, as there has been changes to versions & releases of SQL Server during last 3 to 5 years. 

    As the current version of SQL Server is 2008 & R2, it is better to highlight on those aspects, to find 2008 path see this Microsoft SQL Server 2008 Certification Paths document and 

    Microsoft Learning: Microsoft SQL Server Certification pages.

    For the R2 here is the interesting blog post on  Why the SQL Server 2008 Certifications Don’t Cover R2  is a must read, finally

    Certification path for SQL Server 2008, whats your choice? - SQL ...

     for your information.

    SQL Server 2008 Best Practices Analyzer - keep an eye for the release

    What practice do you classify as a best practice?

    The answer is its not a rocket science, you don't need any specific formula to satisfy the need!

    Ok what if a tool can follow those common best practices & perform an alerting mechanism that can help DBAs to keep the data platform secure & safe according to industry best practices. Not only that even DBAs need to perform a tweaking practices on SQL Server platform manually to find it as ideal configuration. SQL Server Best Practices Analyzer can help to test & implement a combination of best practices.

    It has been started since SQL Server 2000 Best Practices Analyzer that lets you verify the implementation of common Best Practices. These best practices typically relate to the usage and administration aspects of SQL Server databases and ensure that your SQL Servers are managed and operated well. Then here comes SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

    All is well, how about having such handy tool for SQL Server 2008?

    CSS SQL Server engineers blog gets such a sweet news for SQL Server users:

    The new SQL Server 2008 R2 BPA is based on the new Microsoft Baseline Configuration Analyzer (MBCA) v2.0. We hope to release the free downloadable package to the web in early Summer of 2010. Here are a few facts about the new BPA and some sneak peek at screenshots (the details are subject to change):

    • Supports both SQL Server 2008 and SQL Server 2008 R2
    • Free download from the web.
    • Runs on various operating systems such as Vista, XP, Windows Server 2003, Windows Server 2008, and Windows Server 2008 R2
    • Supports GUI and command line execution
    • Supports remote execution through remote Powershell 2.0
    • SQL Server 2005 rules converted where appropriate
    • ~150 rules in the first release spanning engine, security, replication, SSAS, SSRS, Servicing, and SSIS

    So to get to know on the release of SQLBPA for SQL 2008 in coming months look at this SQLBPA tag post

    A tip about how best you can implement these best practices when using SQL Server 2008 version is Policy Based Management framework, which I will cover in upcoming post by joining both BPA & PBM features.

    Keep watching this space.

    SQL Server in Virtual platform - keep a close watch on physical server's configuration

    Server consolidation & virtualization is a hot topic and also it is one of the biggest concerns for many DBAs is scalability/performance when it comes to virtualization.

    At this point,performance for virtual machines can be close to, if not the same as, many physical server configurations. Just not in case if you have multiple SQL instances, its fuel to problem. Also the DBAs cannot ignore the fact that Virtualization is advantageous for the company and consolidation gets flexibility to manage the multiple instance easily. To that point I have been involved on such projects and to the affect there are definitely some considerations that will affect that decision whether to Virtualize the SQL environment or not. Outside world you will get the answer, it depends and never take it granted unless you have thouroughly satisfied with the enviornment availability & performance.

    Coming to actual configuraiton on physical machine, its a fact that virtual machine throughput scales well for both 32-bit and 64-bit virtual machines. Whereas, 64-bit virtual machines provide better scalability for SMP virtual machines as user load increases along with SMP virtual machines provide better throughput scalability than UP virtual machines as user load increases, but with a higher CPU cost (reference to http://sqlserver-qa.net/blogs/tools/archive/2008/02/12/managing-sql-server-hot-topic-virtualization-and-consolidation.aspx link).

    Obviously I don't want to repeat about VMWare related notes on SQL Server, as you can get VMWare-Community-SQLServer documents & SQLServer_on_VMWare-lessons learned are good ones. Then coming to Windows Server 2008 Hyper-V which is Microsoft's server-based virtualization solution for businesses of all sizes. Virtualization refers to a decades-old technology that allows you to run one or more virtualized, software-based PC-type environments under a host PC. Software-based virtualized PC environments or virtual machines (VMs)--are often referred to as guest PCs, though Hyper-V introduces slightly different naming conventions. Microsoft documentation refers: Hyper-V injects itself between the operating system and the hardware in a way that makes a Hyper-V-based Windows Server 2008 install quite different from a more traditional install. Hyper-V runs as a so-called Hypervisor (thus the name) that offers near-bare metal performance in the virtualized child partitions (similar, conceptually, to guest PCs) alongside the parent partition (which is always Windows Server 2008). The hypervisor approach also allows Hyper-V to offer better security--there is little code and no third party device drivers contained within, thus it has a minimal attack surface--and complete isolation between partitions. I recommend further reference on

    Optimizing Performance on Hyper-V & http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx links which are good resources to go in-deep about HyperV enviornment.

    The important aspect of Virtualization is server resources Memory & CPU: so memory is similar to processor, although it is not as much of a concern. With most hypervisors feature when you configure a virtual machine to use X GB of memory, it will consume that much under the hypervisor. That means that the server that functions as the hypervisor will generally have a lot of memory. You do have to also realize that virtualization has overhead. For instance if you configure a guest to use 8 GB of memory, it is really 8 GB plus whatever overhead of virtualization is imposed by the hypervisor. You will see the difference of such hypervisor environment will have better functioning as they generally have a lot of memory in them. Most of the content on web always talk about Memory & CPU but not about I/O which is one the high concern for virtualizing the nodes for the sake of data management, so whether  iSCSI based hardware in the node is used or virtual HBAs,  so all of the guests are sharing the hypervisor’s I/O architecture. I insist to take a good care of I/O too which is a key to a good portion of SQL Server's performance. So the best practice to obtain better performance from guests is to configure them to use physical disks, not the virtualized disk configuration  through the hypervisor. This stands the real fact that using “real” hardware is better where you can in this case provides a big benefit since you have full bandwidth of the disk.

    On the performance monitoring collection for baseline & benchmarking the primary measure of OLTP workloads is the throughput in units that specific to transactions per second, so keep an eye on I/O, CPU & Memory related counters. By default with any system-level workload the resource usage is critical aspect of database workoad and very important for sizing & capacity planning too on top of performance & availability features. So the data CPU usage data collected within VM is not useful for the reason that data does not always accurately reflect the overhead of virtualization that is incurred by physical server host and also the feature of way time is recorded in VM is not accurate. On the configuration side UP & SMP virtual machine configuration performance can be compare in many ways with different dimensions. On the consolidation process the key choice is processor & disks, CPU count of VM (UP or 2 way or 4 way SMP) will have higher virtualization overhead than UP VM which benefits the end user & database workload.

    In this context for testing I recommend the storage layout of placement of database & log files is important, placew them on VMFS partitions located on independent disk arrays, place the files on RAID 5 LUN for the 32-bit virtual machine and usual RAID 5 LUN for the 64-bit virtual machine too (database is nominal OLTP based). As a best practice we can place the log files on RAID 1 LUN for the 32-bit virtual machine and  64-bit virtual machine (its a common configuration you think, eh!). By design RAID 5 arrays provide reasonable fault tolerance and performance with acceptable cost and overhead unless the workload is dominated by a large number of write operations having the RAID 1 provides the higher level of fault tolerance required for database log files. ON the other side of hardware parameters for SAN, HBA, and Fibre Channel switch are left at their default values. Finally the TEMPDB will be located on the operating system partition (C: drive) and depending on possibility to achieve higher throughput rates you can place TempDB to a SAN partition, especially for decision support or business intelligence workloads.

    Finally coming to costing point of view, that virtualization brings cost reduction on licensing for SQL Server and Windows for each guest, see below:

    Microsoft has changed their licensing to allow you to run as many virtual instances of Microsoft SQL 2005 or Microsoft SQL 2008 on the same server that you bought a license of the product for. So the more Microsoft SQL instance you need that can run on the same physical server the more money you can save. Below is the licensing statement for both SQL 2005 & SQL 2008 quoted directly from Microsoft:

    For SQL 2005 Licensing in a Virtualized enviornment:

    (Source: http://www.microsoft.com/sqlserver/2005/en/us/Special-Considerations.aspx)

    When SQL Server 2005 runs inside a virtual operating environment, it requires at least one license per virtual operating environment. Several copies or instances of SQL Server 2005 can run inside a virtual operating environment. These must be licensed as follows:

    • When Licensed per Server/Client Access License. Workgroup, Standard, and Enterprise editions of SQL Server 2005 now allow for unlimited instances within each virtual or physical operating environment. Previously, only the Enterprise Edition of the Server license allowed multi-instancing. This is a great incentive for customers to adopt the Server/Client Access License (CAL) model. For Workgroup and Standard, each virtual or physical operating environment containing a running instance of SQL Server requires a Server license. For Enterprise Edition, each physical operating environment containing a running instance of SQL Server requires a Server license and no separate licenses are needed for SQL Server instances running in virtual operating environments on the same machine.
    • When Licensed per Processor. Workgroup, Standard, and Enterprise editions of SQL Server 2005 allow for unlimited instances in each virtual or physical operating environment. For Workgroup, Standard and Enterprise Edition, each virtual operating environment running SQL Server 2005 must have a processor license for each processor that the virtual machine accesses. If a copy of SQL Server is running on a physical operating environment, processor licenses are required for all of the processors on that physical server. For Enterprise Edition there is an added option: if all processors in a machine have been licensed, then the customer may run unlimited instances of SQL server 2005 on an unlimited number of virtual operating environments on that same machine.

    For SQL 2008 Licensing in a Virtualized enviornment:

    (Source:  SQL Server 2008 Licensing Overview)

    When SQL Server 2008 runs inside a virtual operating environment, it requires at least one license per virtual operating environment, except for SQL Server Enterprise edition. Several copies or instances of SQL Server 2008 can run inside a virtual operating environment. These must be licensed as follows:

    When licensed Server / CAL

    Workgroup and Standard editions now allow you to run any number of instances of the server software in one physical or virtual operating system environment on the licensed server at a time. Previously, only the Enterprise edition of the Server license allowed multi-instancing. This is a great incentive for customers to adopt the Server/CAL model.

    For Enterprise edition, for each server to which you assign a software license, you may run, at any one time, any number of instances of the server software in one physical and any number of virtual operating system environments on that server.

    When licensed Per Processor

    With Workgroup, Web, and Standard editions, for each server to which you have assigned the required number of per processor licenses, you may run, at any one time, any number of instances of the server software in physical and virtual operating system environments on the licensed server. However, the total number of physical and virtual processors used by those operating system environments cannot exceed the number of software licenses assigned to that server.

    Microsoft recommends that if you choose SQL Server 2008 Enterprise Edition and license all of the processors in the server with the hypervisor, you can configure SQL Server on any number of virtual processors under that one physical server.  Also a note about the edition installation as per this licensing FAQ that if you do not purchase an Enterprise Edition license for the hypervisor, you will need to license each guest separately. For windows based see these http://www.microsoft.com/licensing/about-licensing/virtualization.aspx &  http://www.microsoft.com/Windowsserver2008/en/us/hyperv-calculators.aspx pages. (also see these white papers from http://searchsystemschannel.techtarget.com/generic/0,295582,sid99_gci1508623,00.html & http://searchsystemschannel.techtarget.com/generic/0,295582,sid99_gci1507601,00.html links). “Green IT in Practice: SQL Server Consolidation in Microsoft IT” & “SQL Server Consolidation at Microsoft”.


     

     

     

    More Posts Next page »