Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for the ‘Uncategorized’ Category

Configuring and Installing SSIS on a SQL 2008 R2 failover cluster infrastructure.

Posted by rahmanagoro on October 23, 2011


SSIS isn’t cluster aware out of the box, so there is some bit of config changes to make in order to make it work on a cluster.

C

Select just the Integration service.

On the Client Access point screen, type in the name and also the IP address as well looking at the requirement spreadsheet.

Click on Next

Since the SSIS service has been installed in its own resource group, we now need to set the dependencies.

Set the disk dependency for the SSIS service

Add the disk resource dependency

Requirement: Change SSIS configuration, custom configuration

SSIS Custom Configuration

Navigate to the path: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

Create a folder on the root of the SSIS cluster disk called SSIS_Config

Create a folder on the SSIS cluster disk called Packages

Copy the file from the location C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml to S:\SSIS_Config\



Right Click the SSIS service and select properties and then go onto Registry replication and add the registry key below.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\


Click on OK and then Apply.

Take the resource offline and bring it back online for the changes to take effect.



Perform the following steps for SSIS on the second node.

Change the MsDTSSrvr.ini.xml file on the location defined above and change the packages folder to point to S:\Packages

Make the registry entry changes on the second node as well to finish off the configuration.

Test the failover by failing over the SSIS resource group by NodeA to NodeB.

Copy a .dtsx file onto the S:\Packages folder and open up management studio.

In the illustration below, you can see that we have connected to the SSIS instance and we can see the packages.

102311_1839_Configuring28


Advertisements

Posted in Clustering, SQL 2008, Tips, Uncategorized | 5 Comments »

SQL 2008 Management Datawarehouse Overview

Posted by rahmanagoro on April 13, 2011


 

With the release of SQL 2008, Microsoft has developed a warehouse framework for database administrators to have an idea of what is actually going in within SQL server. A lot of the times during my career, I have heard the phrase “The database was slow between 3AM and 4AM) This is especially true if you work within a global framework whereby the database server is serving requests 24/7. I would have users in the Asia pacific region using the database from around midnight GMT (UK time), the as they finish the UK users start around 8AM, and as they wrap up the US users kick in around 1PM BST. So it’s a never ending circle.

To buttress the point, management data warehouse gives me the high level and detailed view of what is happening on the database server, I like to use the waits and queues methodology to see what SQL server is waiting for, and to also keep tabs on my queues as well. If a process is queing up waiting for resources, then there must be a reason for it and as a database administrator, I want to check and understand why we have the waits and queues on the database server.

This post assumes that management data warehouse has already been installed on the database server, I am still running SQL 2008 RTM SP1 cumulative update 5 on my systems and till date, there is no way to remove management data warehouse once it has been installed. I installed management data warehouse on the dedicated DBA database on the production system, the database schema gets installed on the database as well.

To launch management data warehouse, follow the steps below.

Management data warehouse works by collecting information from a number of DMV’s and using connection points to upload the data onto the database and then store it within  data warehouse style tables.

 

Memory section

 

If you click on the memory graph, it drills down to show you the key memory counters. Here you can see things like page life expectancy, SQL server internal memory consumption by type

Reviewing waits, within management data warehouse you can check your wait statistics and further drill into what percentage the composition is.

Memory counters based on page life expentancy.

 

 

 

 

 

Disk IO statistics information can also be found below.

On the IO specific information, you can drill down on top queries based on duration, total IO, physical disk reads and also logical writes.

Posted in Management, SQL 2008, Tips, Uncategorized | Leave a Comment »

Troubleshooting using default trace information

Posted by rahmanagoro on March 11, 2011


The default trace is built into SQL server, its one that often tells you operations that are happening on the database server which is often not provided within the SQL error logs.

I regularly query the default trace to pick up issues that might be arising on the database server, the scripts that I use to query the default trace can be found below. The default trace also allows me to create policies using policy based management within SQL 2008, we once had a deployment using Visual studio and for those who have used visual studio, it creatred databases as a project and this is used in storing database schema, database deployment, integration with SVN etc. At the end of the day, the repository was not upto date and Auto skrink was turned on with visual studio, I found this had been turned on with visual studio. This was revealed within the default trace and I was able to create a policy to check for this across the database estate.

To get the default trace file currently in use, run the query below.

SELECT *
FROM fn_trace_getinfo(default);

To group the various events to see what has been happening on the database server, you can run the query below.

--Auditing various event types in the trace file.

SELECT
trc_evnt.name, trc_evnt.category_id
,count(*) as occurence
FROM fn_trace_gettable('y:\databases\log_5170.trc', NULL) AS dflt_trc
INNER JOIN sys.trace_events AS trc_evnt
ON dflt_trc.EventClass = trc_evnt.trace_event_id
group by trc_evnt.name, trc_evnt.category_id
order by trc_evnt.name

You can change the default trace file to query the last five default traces, so for example y:\databases\log_5170.trc is the current trace, to check the one before this simply decrement the number by one, so run the query y:\databases\log_5169.trc.

I get information similar to the one below

Audit DBCC Event    8    9006
Audit Server Alter Trace Event    8    2
Hash Warning    3    2
Object:Created    5    644
Object:Deleted    5    201
Sort Warnings    3    1

I have used this method to troubleshoot various issues on the database such as the following:

missing statistics: I subesequently created and index or statistics on the column.

transaction logs growing every night: I changed the growth settings and expanded the size so as to remove the need for the growth.

Posted in Management, Scripts, SQL 2008, SQL Administration, Tips, Uncategorized | Leave a Comment »

Replication snapshot agent error The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity.

Posted by rahmanagoro on September 14, 2010


We recently had a production release where I had to add two new subscribers to the replication set, the idea was that we were moving onto new hardware but we couldn’t delete the two older subscribers, as we had to leave the subscribers running for a few more weeks just in case we had issues with the two new database servers in the new data center. The strategy was that should we encounter issues in the new data center, we would just re-route our applications to connect to the two subscribers in the old data center.

After adding the two new subscriber, meaning we had six subscribers in total, I started seeing errors within the replication snapshot agent. The error was as follows

Error messages:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

I noticed that the only article Microsoft had on this was on the link, http://technet.microsoft.com/en-us/library/ms152484.aspx. It was just telling me something that the message already says, the page didn’t detail a proper workaround, one thing with our replication topology is that we use the same server as the OLTP, publisher and distributor and whenever the replication snapshot agents failed, its normally at busy periods of the day, but I couldn’t still nail down the problem, I then decided to add some form of verbose logging on the snapshot agent job to see what the problem was, but by the time I got round to doing this, I was told that we can now remove the two extra subscribers, after removing the two extra subscribers the errors stopped. It was also difficult for me to recreate the problem on the test environment because we didn’t have six subscribers on test environment; we only had a maximum of two and besides this issue only started after adding two extra subscribers.

I also looked at the idea of changing the heartbeat interval, but this didn’t resolve the problem as this merely changed the details of the error message to reflect the new interval period specified.

It’s a weird one, but one which I hope Microsoft will be able to provide a more accurate workaround.

Posted in SQL 2008, SQL replication, Tips, Uncategorized | Leave a Comment »

Changing collation setting on a SQL 2008 instance

Posted by rahmanagoro on August 28, 2010


It is possible to chang the collation setting on a database, but what happens if you try to change the collation setting on a SQL instance or on the system databases, for SQL 2008 this can be done from the command line. Generally, what i tend to do is as follows.

  1. I will find the location of all my databases, as changing the collation forces a mini re-installation and thus will detach all user databases and rebuild system databases.
select * from master..sysaltfiles

2      A full backup of system databases, msdb, master and model is also recomended. Changing collation will rebuild system databases.

Lets assume that the drive containing the SQL 2008 installation media is E:\

Follow the steps below from the command prompt


cd E:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="domain\username" /SAPWD= "London2012" /SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS

 

The parameters are as follows, /SQLSYSADMINACCOUNTS this specifies the sysadmin account nominated.

/SAPWD = This is the SA password to be used afterwards.

Posted in Day to day queries, Scripts, SQL 2008, SQL Administration, Uncategorized | Leave a Comment »

SQL 2008 Installation step by step with pictures including silent unattended installation

Posted by rahmanagoro on August 28, 2010


SQL 2008 Manual Installation Guide

The following guide details how to install SQL 2008 step by step guide with pictures.

One important setting worth mentioning is collation setting, please ensure that you are using the right collation settings, the chosen database collation setting has been detailed is: SQL_Latin1_General_CP1_CS_AS .

Manual SQL Installation Steps

This page is for the SQL 2008 Installation steps.
Prerequisites To install SQL Server 2008

1. Insert the SQL Server installation media. From the root folder, double-click setup.exe. To install from a network share, locate the root folder on the share, and then double-click setup.exe. If the Microsoft SQL Server 2008 Setup dialog box appears, click OK to install the prerequisites, then click Cancel to exit SQL Server 2008 installation.
2. If the .NET Framework 3.5 SP1 installation dialog box appears, select the check box to accept the .NET Framework 3.5 SP1 License Agreement. ClickNext. To exit SQL Server 2008 installation, click Cancel. When installation of .NET Framework 3.5 SP1 is complete, click Finish.
3. Windows Installer 4.5 is also required, and might be installed by the Installation Wizard. If you are prompted to restart your computer, restart it, and then restart SQL Server 2008 setup.exe

Carry on with the following steps.

Click on show details and ensure that all the checks are passed.

Select all the features for installation

Select the root directory for the installation.

Check disk space summary.

Select the account details for provisioning the SQL service and other related services.

Select the collation setting for the installation, note the collation setting in use here.

Select Authentication mode, if mixed authentication is used, select the SA password.

Select a directory for the database.

Select the filestream option if applicable.

Select the Analysis services account configuration.

Select the directory for the Analysis service related files.

Select reporting services configuration.

Select error reporting options.

Ensure that installation rules are all passed.

Installation screen, the process will take some time, this depends on the capacity of the machine on which it is being installed on to.


After a successfull installation, open up Programs >> SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager

Check the TCP/IP section and enable it.

Click on Enable.

SQL 2008 Scripted Installation

 

Use the command line options below to install SQL 2008, remember to change the directory to the directory where the installation media is.
Setup.exe /QUIET /QS /ACTION=Install /ERRORREPORTING=1 /SAPWD=”******” /SQLSVCPASSWORD=”****” /AGTSVCPASSWORD=”****” /ASSVCPASSWORD=”****” /ISSVCPASSWORD=”****” /RSSVCPASSWORD=”******” /ConfigurationFile=C:\Downloads\Config.ini
Attention should also be paid to the configuration file as well, see attached configuration file which contains answers for the installation.
Install SQL 2008 SP1 Quietly
You can install SQL 2008 SP1 quietly without the need for a reboot, just ensure that you close all Management Studio windows that are open, this way a reboot will most likely not be required. Use the attached batch file, ensure that you have downloaded the service pack and change the path referenced in the batch file to the correct path.

Configuration.ini file

Below is a sample configuration.ini file for the installation above, copy it and save it to  a file called Config.ini as referenced above in the command line installation.


;SQLSERVER2008 Configuration File
[SQLSERVER2008]

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance.

INSTANCEID="MSSQLSERVER"

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ACTION="Install"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components.

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,AS,RS,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK,OCS

; Displays the command line parameters usage

HELP="False"

; Specifies that the detailed Setup log should be piped to the console.

INDICATEPROGRESS="False"

; Setup will not display any user interface.

QUIET="False"

; Setup will display progress only without any user interaction.

QUIETSIMPLE="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.

X86="False"

; Specifies the path to the installation media folder where setup.exe is located.

MEDIASOURCE="I:\"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.

ERRORREPORTING="False"

; Specify the root installation directory for native shared components.

INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

; Specify the installation directory.

INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.

SQMREPORTING="False"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).

INSTANCENAME="MSSQLSERVER"

; Agent account name

AGTSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Auto-start service after installation.

AGTSVCSTARTUPTYPE="Manual"

; Startup type for Integration Services.

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account.

ISSVCACCOUNT="NT AUTHORITY\SYSTEM"

; The name of the account that the Analysis Services service runs under.

ASSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Controls the service startup type setting after the service has been created.

ASSVCSTARTUPTYPE="Automatic"

; The collation to be used by Analysis Services.

ASCOLLATION="Latin1_General_CS_AS"

; The location for the Analysis Services data files.

ASDATADIR="C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data"

; The location for the Analysis Services log files.

ASLOGDIR="C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Log"

; The location for the Analysis Services backup files.

ASBACKUPDIR="C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup"

; The location for the Analysis Services temporary files.

ASTEMPDIR="C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Temp"

; The location for the Analysis Services configuration files.

ASCONFIGDIR="C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config"

; Specifies whether or not the MSOLAP provider is allowed to run in process.

ASPROVIDERMSOLAP="1"

; Specifies the list of administrator accounts that need to be provisioned.

ASSYSADMINACCOUNTS="DOMAIN\username"

; Startup type for the SQL Server service.

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3).

FILESTREAMLEVEL="3"

; Name of Windows share to be created for FILESTREAM File I/O.

FILESTREAMSHARENAME="MSSQLSERVER"

; Set to "1" to enable RANU for SQL Server Express.

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine.

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account.

SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Windows account(s) to provision as SQL Server system administrators.

SQLSYSADMINACCOUNTS="Domain\username"

; The Database Engine root data directory.

INSTALLSQLDATADIR="C:\Database"

; Default directory for the Database Engine backup files.

SQLBACKUPDIR="C:\Database"

; Default directory for the Database Engine user databases.

SQLUSERDBDIR="C:\Database"

; Directory for Database Engine TempDB files.

SQLTEMPDBDIR="C:\Database"

; Provision current user as a Database Engine system administrator for SQL Server 2008 Express.

ADDCURRENTUSERASSQLADMIN="False"

; Specify 0 to disable or 1 to enable the TCP/IP protocol.

TCPENABLED="0"

; Specify 0 to disable or 1 to enable the Named Pipes protocol.

NPENABLED="0"

; Startup type for Browser Service.

BROWSERSVCSTARTUPTYPE="Disabled"

; Specifies which account the report server NT service should execute under.  When omitted or when the value is empty string, the default built-in account for the current operating system.
; The username part of RSSVCACCOUNT is a maximum of 20 characters long and
; The domain part of RSSVCACCOUNT is a maximum of 254 characters long.

RSSVCACCOUNT="NT AUTHORITY\SYSTEM"

; Specifies how the startup mode of the report server NT service.  When
; Manual - Service startup is manual mode (default).
; Automatic - Service startup is automatic mode.
; Disabled - Service is disabled

RSSVCSTARTUPTYPE="Automatic"

; Specifies which mode report server is installed in.
; Default value: “FilesOnly”

RSINSTALLMODE="DefaultNativeMode"

Posted in Day to day queries, Professional Development, SQL 2008, SQL Administration, Uncategorized | 1 Comment »

SQL 2008 replication error ” retrying command or the process is running and waiting for a response from the server “

Posted by rahmanagoro on August 26, 2010


I was working with SQL 2008 replication and on checking replication monitor, I noticed that on the distribution agent for one of the databases, it had a retrying command status. I further looked into it to see what the problem was, here I can see that the distribution agent had become stucked whilst trying to send commands to the subscriber. The first thing that came to my mind was, “what is happening with the subscriber ? ”


I ran the following on the subscriber

use  master
 GO
 sp_who2 active

I was able to see that there is a blocking spid at the moment, which is coming from my distributor. So what must have happened here is that, as a snapshot was being delivered to the subscriber, objects such as stored procedures and functions were also being delivered as well, thereby causing a deadlock to occur.

To confirm this, I ran the code below to check for replication errors.

select * from distribution..MSrepl_errors

I had entries like

1092 2010-08-01 00:35:16.560 0 0 MSSQL_ENG HYT00 Query timeout expired 0x0001262200000210000100000002 1 0

1092 2010-08-01 00:35:16.557 0 1 MSSQL_ENG if @@trancount > 0 rollback tran 0x0001262200000210000100000002 1 0

On the subscriber, I confirmed the locking scenario by looking at the wait type for the replication SPID, and it was a LCK_M_S wait type, which confirmed it was a deadlocking situation.

I also ran a deadlocking detail script as well.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
 owt.session_id AS waiting_session_id,
 DB_NAME(tls.resource_database_id) AS database_name,
 owt.wait_duration_ms,
--    owt.waiting_task_address,
 owt.wait_type,
--    tls.resource_associated_entity_id,
--    tls.resource_description AS local_resource_description,
 tls.resource_type,
 (CASE tls.resource_type
 WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
 WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
 ELSE (SELECT  OBJECT_NAME(pat.[object_id], tls.resource_database_id)
 FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)

 END
 ) AS object_name,
 tls.request_mode,
 tls.request_type,
 tls.request_session_id,
 owt.blocking_session_id,
 owt.resource_description AS blocking_resource_description,
 (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
 (CASE WHEN ers.statement_end_offset = -1
 THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
 ELSE ers.statement_end_offset
 END
 - ers.statement_start_offset
 ) / 2)
 FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
 CASE WHEN owt.blocking_session_id > 0
 THEN (
 SELECT
 est.[text] FROM sys.sysprocesses AS sp
 CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
 WHERE sp.spid = owt.blocking_session_id)
 ELSE
 NULL
 END AS blocking_query_text,
 qp.query_plan AS waiting_query_plan
FROM sys.dm_tran_locks AS tls
 INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
 INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
 OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
GO


I was able to see that one spid was calling the stored procedure:

exec sp_MSacquiresnapshotdeliverysessionlock

and the other spid only had a begin tran statement showing from my dbcc inputbuffer statement.
I killed this SPID which had begin tran as the sql statement which it was running, and the replication commands were delivered and replication was working once more. The problem was fixed.

Posted in SQL 2008, SQL Administration, SQL replication, Uncategorized | 2 Comments »

Hello world!

Posted by rahmanagoro on February 21, 2010


Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | Leave a Comment »