Sunday, July 27, 2008
SQL Server Performance Checklist
Documented the SQL Server Performance Checklist in Excel Spreadsheet format .
Saturday, July 19, 2008
SQL Server 2005 SP2 installation failed on Active/Passive Cluster
SP2 fails to install database services - MSP Error: 29512 “Unable to install Windows Installer MSP file”
Errors from Summary.log
----------------------------------------------------------------------------------
Product : Database Services (SQL084)
Product Version (Previous): 2153
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29512
Error Description : MSP Error: 29512 SQL Server Setup was unable add user pbs\csparq_service to local group AU\SGG-GM_ServiceAccounts.
----------------------------------------------------------------------------------
Configuration failed.
One or more products failed to install, see above for details
Exit Code Returned: 29512
Errors from SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
MSI (s) (68:4C) [00:21:37:156]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Service Pack 2 for SQL Server Database Services 2005 (64-bit) ENU (KB921896)' could not be installed. Error code 1603.
Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
Local group AU\SGG-GMRISK_ServiceAccounts doesn't exist
Failure adding user pbs\acoe_csparq_service to local group AU\GMRISK_ServiceAccounts (5)
Error Code: 0x80070005 (5)
Windows Error Text: Access is denied.
Source File Name: sqlca\sqlsecurityca.cpp
Compiler Timestamp: Sat Oct 7 09:43:41 2006
Function Name: Do_sqlGroupMember
Source Line Number: 1132
MSI (s) (68:4C) [00:21:37:156]: Note: 1: 1729
MSI (s) (68:4C) [00:21:37:156]: Transforming table Error.
MSI (s) (68:4C) [00:21:37:156]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (68:4C) [00:21:37:188]: Transforming table Error.
MSI (s) (68:4C) [00:21:37:188]: Transforming table Error.
Workaround:
Errors from Summary.log
----------------------------------------------------------------------------------
Product : Database Services (SQL084)
Product Version (Previous): 2153
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29512
Error Description : MSP Error: 29512 SQL Server Setup was unable add user pbs\csparq_service to local group AU\SGG-GM_ServiceAccounts.
----------------------------------------------------------------------------------
Configuration failed.
One or more products failed to install, see above for details
Exit Code Returned: 29512
Errors from SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
MSI (s) (68:4C) [00:21:37:156]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Service Pack 2 for SQL Server Database Services 2005 (64-bit) ENU (KB921896)' could not be installed. Error code 1603.
Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
Local group AU\SGG-GMRISK_ServiceAccounts doesn't exist
Failure adding user pbs\acoe_csparq_service to local group AU\GMRISK_ServiceAccounts (5)
Error Code: 0x80070005 (5)
Windows Error Text: Access is denied.
Source File Name: sqlca\sqlsecurityca.cpp
Compiler Timestamp: Sat Oct 7 09:43:41 2006
Function Name: Do_sqlGroupMember
Source Line Number: 1132
MSI (s) (68:4C) [00:21:37:156]: Note: 1: 1729
MSI (s) (68:4C) [00:21:37:156]: Transforming table Error.
MSI (s) (68:4C) [00:21:37:156]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (68:4C) [00:21:37:188]: Transforming table Error.
MSI (s) (68:4C) [00:21:37:188]: Transforming table Error.
Workaround:
Quality
ETVX: Entry, Task, Verification, and Exit (software process developed by IBM)
Quality ensured by:
--------------------
Test, WP Review, Audit, PMPR
PMPR : Post Milestone Project Review
Configuration Managment:
-------------------------
Configurable item
Baseline : Frozen work product
Levels of Control
-------------------
Formal Control: Should go thru Change Management
WP delivered to client
Managed & Controlled:
Internal Project documentation
RASIC Chart
--------------
The designations in the approach are defined as follows:
* Responsible: those solely and directly accountable for creating a work product
* Approving: the party (or parties) that reviews and assures the work product's quality
* Supporting: individuals or groups who help to create the work product
* Informed: those who are to be kept informed about proceedings
* Consulted: those who help design the product or put in place quality review criteria
X axis - Roles
Y Axis - Activities
Goals
----------
Helps on defining goals
GQM
Goal - Question - Metrics
SMART
Specific
Measurable
Action Oriented / Aggressive yet attainable
Relevant to Clients expectation / Relevant
Time bound
CoQ - Cost of Quality
---------------------
The "cost of quality" isn't the price of creating a quality product or service. It's the cost of NOT creating a quality product or service.
Root cause analysis
--------------------
Cause and Effect / Fishbone / Ishikawa Diagram
5 Whys
5 W & 2 H
Primitive Metrics are basic data
------------------------------------
1. Size
Measure how big the application / Product is.
Using of
Function Point - FP
Lines of Code - LOC
Pages of Documentation - POD
2. Effort
is the mo.of labor hours expended to complete project and production support tasks.
helps on measuring the Productivity
helps determining Staff needed for a project
the effort needed depends on the size of the task, complexity, experience, and organization tools and processes.
Effort drives cost
Analysing of efforts involves Time Tracking
Project effort is not the same as billable effort - metric collection and billing have difference objectives
Time tracking ( SAP, PIV, MS Project) accounts for Project time, production support time and administrative time
3. Staff
is the no.of people assigned to a project for a given reporting period
Peak Staff:
The total count of individual who are allocated to and worked on a project during report period.
Peak staff must be a whole number
Count any person assigned to more than one project as one person for any and all of the projects which he or she is assigned.
FTE ( Full Time Equivalent)
a measure of the resources (people) who are allocated during a month.
4. Duration
Duration is the number of calendar days from the defined measurement ‘start date’ of the project to the project measurement ‘end date,’ less any hold time, defined in number of calendar days
5. Defects
A defect is a flaw in the product, system, or service that fails to perform its required functions
helps on assessing Quality & Reliablity
Pre-Release Defect
6. Change
Change is the measure of volatility of a system/application and of project requirements
7. Computer Resources
computing requirement for use by a project in the development of a system or to support the production environment.
Derived metrics form the Basic Primitive data
-----------------
1. Productivity
2. Delivery Rate
3. Defect Density
4 Estimate Accuracy
Defect & Rework
--------------------
Standard defect-type classification
------------------------------------
Requirement Error
Design Error
Coding Error
Testing Error
Third party Error
Documentation Error
H/W or Systems Software
Human Intervention Error
Project Management Error
Process/ Standard Error
Other
Severity of defects
-------------------
1 - Catastrophic
2 - Major ( without workaround)
3 - Major ( with workaround)
4 - Minor
5 - Cosmetic
6 - Internal
Service Performance Indicators (SPI)
Information Engineering Metrics Standards and Guidelines (S&G)
Production Support
-------------------
Safe & Escape
Differnce between Verification & Validation
Verification:
--------------
Verification ensures that you are building a product according to its requirements, specifications, and standards. For Verification, you should ask the following questions:
Are you meeting the specified requirements?
Are you building the product right?
Verification is CORRECTNESS.
Example:
performed by the developers on not on intended environment
Validation:
-------------
Validation ensures that your product will be usable once it is in its intended environment. For Validation, you should ask the following questions:
Are you meeting the operational need?
Does this product meet its intended use in the intended environment?
Are you building the right product?
performed by the end users on intended environment
Validation is TRUTH.
Example: UAT
TRUTH is something absolute which cannot be interpretted differently.
Correctness is relative to the system as presented.
When prototypes are developed to ensure that specific requirements can be addressed, it is an example of a verification practice. When end users are asked to evaluate prototypes to ensure that the product will meet their needs, it is an example of a validation practice.
Earned Value Management
Basic on Earned Value Management
CMMi Level
Objective of High Maturity Model
Control: Understand and control process varaince
Predict: Predict Performance from process capability
Improve: Constantly improve process capability
Saturday, July 12, 2008
SQL Server Performance baseline
1 Find Objects using Cursors
-------------------------------------
SELECT object_name(id) FROM syscomments
WHERE text LIKE '%DECLARE%CURSOR%'
2. Find views using Views
-------------------------------------------
Select * from sysComments where text like '%from v%' and type = 'V'
3. TempDB Usage History at instance level
----------------------------------------------
SELECT getdate(),
SUM(user_object_reserved_page_count) * 8 as user_objects_kb,
SUM(internal_object_reserved_page_count) * 8 as internal_objects_kb,
SUM(version_store_reserved_page_count) * 8 as version_store_kb,
SUM(unallocated_extent_page_count) * 8 as freespace_kb
FROM sys.dm_db_file_Space_Usage
where database_id = 2
4. TempDB Usage History at object level
--------------------------------------------
SELECT
obj.objectid as [Object_Id],
getdate() as SampleDateTime,
sum(tmp.user_objects_alloc_page_count) AS user_objects_alloc_page_count,
sum(tmp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count,
sum(tmp.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count,
sum(tmp.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage AS tmp
LEFT OUTER JOIN sys.dm_exec_requests AS req
ON tmp.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS obj
WHERE tmp.session_id > 50
AND obj.objectid is not null
Group BY obj.ObjectId
5. Object Block history
-----------------------------------
SELECT s.object_id
, SampleDateTime = getdate()
, indexname=i.name, i.index_id
, [block_pct]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_ms = row_lock_wait_in_ms
, [avg_row_lock_waits_ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
And row_lock_wait_count <> 0
6. CPU Usage History
----------------------------
SELECT
objectid as [Object_Id]
, getdate() As SampleDateTime
, total_cpu_time
, total_execution_count
, number_of_statements
FROM (
SELECT
qs.sql_handle
, sum(qs.total_worker_time) as total_cpu_time
, sum(qs.execution_count) as total_execution_count
, count(*) as number_of_statements
FROM
sys.dm_exec_query_stats qs
GROUP BY qs.sql_handle ) dt
Cross Apply sys.dm_exec_sql_text(dt.sql_handle) eqp
WHERE ObjectId is not null
7. I/O Usage History
-----------------------------------
Select ObjectId As [Object_ID], GetDate() as SampleDateTime
, (total_logical_reads/Cast(execution_count as Decimal(38,16))) as avg_logical_reads
, (total_logical_writes/Cast(execution_count as Decimal(38,16))) as avg_logical_writes
, (total_physical_reads/Cast(execution_count as Decimal(38,16))) as avg_physical_reads
, Execution_Count
from (
select
Sum(total_logical_reads) as total_logical_reads
, Sum(total_logical_writes) as total_logical_writes
, Sum(total_physical_reads) as total_physical_reads
, Sum(execution_count) as execution_count
, sh.objectid
from sys.dm_exec_query_stats
Cross Apply sys.dm_exec_sql_text(sql_Handle) sh
Where objectId is not null
Group By objectid
) SubQry
8. Table Data Growth volume History
------------------------------------------
DECLARE @START_DATE datetime,
@END_DATE datetime
SET @START_DATE = getdate()
SET @END_DATE = getdate()
IF @START_DATE = @END_DATE
SET @END_DATE = Convert(varchar(10), DateAdd(day, 1, @END_DATE), 101)
SELECT startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
end
FROM Benchmark_Table_DataVolumeHistory startdt
INNER JOIN (
Select object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY object_Id
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime
SELECT UseCaseDesc, startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
end
FROM Benchmark_Table_DataVolumeHistory startdt
INNER JOIN (
Select uc.UseCaseDesc, hist.object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory hist
INNER JOIN Benchmark_useCase_Object_assn ua
ON hist.object_id = ua.object_id
inner join benchmark_usecase uc
on ua.usecaseid = uc.usecaseid
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY hist.object_Id, usecasedesc
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime
-------------------------------------
SELECT object_name(id) FROM syscomments
WHERE text LIKE '%DECLARE%CURSOR%'
2. Find views using Views
-------------------------------------------
Select * from sysComments where text like '%from v%' and type = 'V'
3. TempDB Usage History at instance level
----------------------------------------------
SELECT getdate(),
SUM(user_object_reserved_page_count) * 8 as user_objects_kb,
SUM(internal_object_reserved_page_count) * 8 as internal_objects_kb,
SUM(version_store_reserved_page_count) * 8 as version_store_kb,
SUM(unallocated_extent_page_count) * 8 as freespace_kb
FROM sys.dm_db_file_Space_Usage
where database_id = 2
4. TempDB Usage History at object level
--------------------------------------------
SELECT
obj.objectid as [Object_Id],
getdate() as SampleDateTime,
sum(tmp.user_objects_alloc_page_count) AS user_objects_alloc_page_count,
sum(tmp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count,
sum(tmp.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count,
sum(tmp.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage AS tmp
LEFT OUTER JOIN sys.dm_exec_requests AS req
ON tmp.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS obj
WHERE tmp.session_id > 50
AND obj.objectid is not null
Group BY obj.ObjectId
5. Object Block history
-----------------------------------
SELECT s.object_id
, SampleDateTime = getdate()
, indexname=i.name, i.index_id
, [block_pct]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_ms = row_lock_wait_in_ms
, [avg_row_lock_waits_ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
And row_lock_wait_count <> 0
6. CPU Usage History
----------------------------
SELECT
objectid as [Object_Id]
, getdate() As SampleDateTime
, total_cpu_time
, total_execution_count
, number_of_statements
FROM (
SELECT
qs.sql_handle
, sum(qs.total_worker_time) as total_cpu_time
, sum(qs.execution_count) as total_execution_count
, count(*) as number_of_statements
FROM
sys.dm_exec_query_stats qs
GROUP BY qs.sql_handle ) dt
Cross Apply sys.dm_exec_sql_text(dt.sql_handle) eqp
WHERE ObjectId is not null
7. I/O Usage History
-----------------------------------
Select ObjectId As [Object_ID], GetDate() as SampleDateTime
, (total_logical_reads/Cast(execution_count as Decimal(38,16))) as avg_logical_reads
, (total_logical_writes/Cast(execution_count as Decimal(38,16))) as avg_logical_writes
, (total_physical_reads/Cast(execution_count as Decimal(38,16))) as avg_physical_reads
, Execution_Count
from (
select
Sum(total_logical_reads) as total_logical_reads
, Sum(total_logical_writes) as total_logical_writes
, Sum(total_physical_reads) as total_physical_reads
, Sum(execution_count) as execution_count
, sh.objectid
from sys.dm_exec_query_stats
Cross Apply sys.dm_exec_sql_text(sql_Handle) sh
Where objectId is not null
Group By objectid
) SubQry
8. Table Data Growth volume History
------------------------------------------
DECLARE @START_DATE datetime,
@END_DATE datetime
SET @START_DATE = getdate()
SET @END_DATE = getdate()
IF @START_DATE = @END_DATE
SET @END_DATE = Convert(varchar(10), DateAdd(day, 1, @END_DATE), 101)
SELECT startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
end
FROM Benchmark_Table_DataVolumeHistory startdt
INNER JOIN (
Select object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY object_Id
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime
SELECT UseCaseDesc, startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
end
FROM Benchmark_Table_DataVolumeHistory startdt
INNER JOIN (
Select uc.UseCaseDesc, hist.object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory hist
INNER JOIN Benchmark_useCase_Object_assn ua
ON hist.object_id = ua.object_id
inner join benchmark_usecase uc
on ua.usecaseid = uc.usecaseid
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY hist.object_Id, usecasedesc
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime
Friday, July 11, 2008
MOC Client Conversation Logger
MOC Client Conversation Logger can be downloaded from
http://www.kellerinc.net/Downloads/tabid/56/Default.aspx
http://www.kellerinc.net/Downloads/tabid/56/Default.aspx
Tuesday, July 08, 2008
Hyber Threading - HT
A thread is a unit of execution that runs on a CPU that can only execute one thread at a time. However, a CPU is very good at switching between threads very quickly to share its power, giving the illusion of simultaneous execution.
Hyper-Threading is a technology found only on Intel processors, that tries to make more efficient use of the CPU by duplicating the architectural state to provide two logical CPUs.
This allows more than 1 thread to be scheduled and enables simultaneous execution when the threads require different processor functions. The effect of this is that you will see two logical CPUs to which the operating system can assign different threads. However, it is only the architectural state that is duplicated, not the physical execution engine. Figure shows a 4 dual-core system with Hyper-Threading enabled, giving 16 logical processors.
Hyper-Threading is only ever going to give you a maximum of 1.3 times nonHyper-Threaded performance and in practice it will be closer to 1.1 to 1.15 times. This knowledge helps put any decision about enabling it back into perspective.
Disable Hyper-Threading unless you have the time to test it thoroughly. Don’t use it on Windows 2000 as it’s not Hyper-Threading aware.
SQL Server license Sacket level not at core CPU
Multi-core effectively means more than one CPU on the same chip so when you buy a dual-core processor you’re actually buying two CPU cores, Windows will see two CPUs, and you should treat it for capacity planning purposes like two single-core CPUs.
It gets even better from a licensing perspective because Microsoft per-processor licensing is per socket, not per core. Socket refers to the physical socket that you plug the processor into.
This licensing model gives Microsoft a competitive advantage over its competitors in the database market, where others charge customers a license fee per core. This is great news for SQL Server customers because a server with four dual-core CPUs will perform comparably with an eight single-core server but at half the license cost.
Image from book
Multi-core processors are only licensed per socket so if you can get an 8-core processor you’ll only need a Windows and SQL Server license for 1 socket!
Monday, July 07, 2008
Setting Unrestricted growth
Problem:
On changing the log file growth to unrestricted. Using the management studio UI, simply clicking on the properties of the DB and then files and then change the option to unrestricted growth on the log file and hitting ok and check the properties, it flips back to unrestricted
Database file was created with the maxsize defined, we altered the database and change the maxsize to unlimited. But afterwards it reverted to the previous max size.
Resolution:
It is by design as advised on
ON BOL:
UNLIMITED
Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.
Workaround:
To get this 'unrestricted' radio button enabled for the log file growth again is to;
- backup db
- detach db
- rename log file to something xx_old.ldf
- attach db again using only the MDF file again and letting SQL create a new log file for you
- delete xx_old.ldf file if all successful
On changing the log file growth to unrestricted. Using the management studio UI, simply clicking on the properties of the DB and then files and then change the option to unrestricted growth on the log file and hitting ok and check the properties, it flips back to unrestricted
Database file was created with the maxsize defined, we altered the database and change the maxsize to unlimited. But afterwards it reverted to the previous max size.
Resolution:
It is by design as advised on
ON BOL:
UNLIMITED
Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.
Workaround:
To get this 'unrestricted' radio button enabled for the log file growth again is to;
- backup db
- detach db
- rename log file to something xx_old.ldf
- attach db again using only the MDF file again and letting SQL create a new log file for you
- delete xx_old.ldf file if all successful
Sunday, July 06, 2008
Free SQL Server tools that might make your life a little easier
check out on below link for free SQL Server tools that might make your life a little easier
http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/free-sql-server-tools-that-might-make-your-life-a.aspx
http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/free-sql-server-tools-that-might-make-your-life-a.aspx
Wednesday, July 02, 2008
Verify the TCP Port
Verify the server is listening on the correct port (1433 in my case). Do this by running the following command in a command prompt window:
netstat -ano | findstr 1433
netstat -ano | findstr 1433
Terminal Citrix Server Connection issue
1. On Run type “regedit”
2. Go to Registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSLicensing\Store”
3. Delete the LICENSE001
4. Try connecting on Citrix Terminal Server
5. Let me know if it works.
2. Go to Registry entry “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSLicensing\Store”
3. Delete the LICENSE001
4. Try connecting on Citrix Terminal Server
5. Let me know if it works.
Tuesday, July 01, 2008
Missing performance counters X64 OS with SQL2000 and SQL 2005
If you’re using x64 Windows and x64 SQL Server you shouldn’t have any issues. System Monitor will by default have the correct counters loaded and available for you to view and the same applies to IA64 Windows and IA64 SQL Server deployments.
However, if you’re using SQL Server in a Windows-On-Windows (WOW) mode on x64 (which is to say that x64 Windows is emulating a 32-bit environment to facilitate you running an x86 instance of SQL Server), When you start System Monitor on the x64 host, you’ll find that none of the counters relating to the 32-bit SQL Server instance are available. This is be cause on an x64 host you’ll be running an x64 version of System Monitor by default.
In the logfile I overlooked Event ID 1021:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a 64-bit environment. Contact the file vendor to obtain a 64-bit version. Alternatively, you can open the 32-bit extensible counter DLL by using the 32-bit version of Performance Monitor. To use this tool, open the Windows folder, open the Syswow64 folder, and then start Perfmon.exe.
Resolution:
Start C:\WINDOWS\SysWOW64\Perfmon.exe, this is the 32-bit version, and all the counters are there.
Monitoring the 32-bit SQL Server instance in the scenario will require running a 32-bit instance of System Monitor, and this can be achieved by running the following
mmc /32perfmon.msc
Once the 32-bit MMC console has started with the Perfmon snap-in, the 32-bit SQL Server counters should be available for addition to your log.
However, if you’re using SQL Server in a Windows-On-Windows (WOW) mode on x64 (which is to say that x64 Windows is emulating a 32-bit environment to facilitate you running an x86 instance of SQL Server), When you start System Monitor on the x64 host, you’ll find that none of the counters relating to the 32-bit SQL Server instance are available. This is be cause on an x64 host you’ll be running an x64 version of System Monitor by default.
In the logfile I overlooked Event ID 1021:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a 64-bit environment. Contact the file vendor to obtain a 64-bit version. Alternatively, you can open the 32-bit extensible counter DLL by using the 32-bit version of Performance Monitor. To use this tool, open the Windows folder, open the Syswow64 folder, and then start Perfmon.exe.
Resolution:
Start C:\WINDOWS\SysWOW64\Perfmon.exe, this is the 32-bit version, and all the counters are there.
Monitoring the 32-bit SQL Server instance in the scenario will require running a 32-bit instance of System Monitor, and this can be achieved by running the following
mmc /32perfmon.msc
Once the 32-bit MMC console has started with the Perfmon snap-in, the 32-bit SQL Server counters should be available for addition to your log.
DMV & DMF
There are two types of dynamic management views and functions:
* Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
* Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission
Dynamic management views and functions have been organized into the following categories.
1. Common Language Runtime Related Dynamic Management Views
2. I/O Related Dynamic Management Views and Functions
3. Database Mirroring Related Dynamic Management Views
4. Query Notifications Related Dynamic Management Views
5. Database Related Dynamic Management Views
6. Replication Related Dynamic Management Views
7. Execution Related Dynamic Management Views and Functions
8. Service Broker Related Dynamic Management Views
9. Full-Text Search Related Dynamic Management Views
10. SQL Server Operating System Related Dynamic Management Views
11. Index Related Dynamic Management Views and Functions
12. Transaction Related Dynamic Management Views and Functions
Excerpt from http://msdn.microsoft.com/en-us/library/ms188754.aspx
DMV Scripts are on Scripts Repository: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true
* Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
* Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission
Dynamic management views and functions have been organized into the following categories.
1. Common Language Runtime Related Dynamic Management Views
2. I/O Related Dynamic Management Views and Functions
3. Database Mirroring Related Dynamic Management Views
4. Query Notifications Related Dynamic Management Views
5. Database Related Dynamic Management Views
6. Replication Related Dynamic Management Views
7. Execution Related Dynamic Management Views and Functions
8. Service Broker Related Dynamic Management Views
9. Full-Text Search Related Dynamic Management Views
10. SQL Server Operating System Related Dynamic Management Views
11. Index Related Dynamic Management Views and Functions
12. Transaction Related Dynamic Management Views and Functions
Excerpt from http://msdn.microsoft.com/en-us/library/ms188754.aspx
DMV Scripts are on Scripts Repository: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true
Monday, June 30, 2008
Friday, June 27, 2008
Getting Detail of SQL Server from Registry
From a Command prompt you can run the following REG instructions
To Get remote server details:
- Add \\servername\... before HKLM to get remote machine details
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CurrentVersion
For SQL Server 2000
-------------------
reg query "HKLM\Software\Microsoft\Microsoft SQL Server" /v InstalledInstances
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\MSSQLLicenseInfo\MSSQL8.00" /v ConcurrentLimit
-- For each instance, replace MSSQLServer with 80\InstanceName from in the following
reg query HKLM\Software\Microsoft\MSSQLServer\Setup /v Edition
reg query HKLM\Software\Microsoft\MSSQLServer\Cluster /v ClusterName
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CSDVersion
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp /v TcpPort
----- Using Registy Server Listing ---
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" ServerN (where N represents the server number )
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" UserN (where N represents the default login corresponding the ServerN )
For SQL Server 2005
-------------------
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names"
- Subkeys represent a different SQL Server Product (SQL=SQL Server, RS=Reporting Server, etc)
- Replace MSSQL.1 with each of the keys from "Inteance Names" on below
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup" /v Edition
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\CurrentVersion" /v CurrentVersion
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort
To Get remote server details:
- Add \\servername\... before HKLM to get remote machine details
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CurrentVersion
For SQL Server 2000
-------------------
reg query "HKLM\Software\Microsoft\Microsoft SQL Server" /v InstalledInstances
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\MSSQLLicenseInfo\MSSQL8.00" /v ConcurrentLimit
-- For each instance, replace MSSQLServer with 80\InstanceName from in the following
reg query HKLM\Software\Microsoft\MSSQLServer\Setup /v Edition
reg query HKLM\Software\Microsoft\MSSQLServer\Cluster /v ClusterName
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CSDVersion
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp /v TcpPort
----- Using Registy Server Listing ---
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" ServerN (where N represents the server number )
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" UserN (where N represents the default login corresponding the ServerN )
For SQL Server 2005
-------------------
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names"
- Subkeys represent a different SQL Server Product (SQL=SQL Server, RS=Reporting Server, etc)
- Replace MSSQL.1 with each of the keys from "Inteance Names" on below
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup" /v Edition
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\CurrentVersion" /v CurrentVersion
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort
Tuesday, June 24, 2008
Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart
Flowchart on Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart is available on SQL Server Customer Advisory Team ( SQL CAT ) site.
http://sqlcat.com/files/folders/280/download.aspx
http://sqlcat.com/files/folders/280/download.aspx
Monday, June 23, 2008
5 Rules of Data Normalization - poster
This is the place to obtain a copy of the aging but still popular "5 Rules of Data Normalization" poster.
The poster was written in 1989 by Marc Rettig as a subscription premium for Database Programming and Design magazine.
http://www.marcrettig.com/poster/
The poster was written in 1989 by Marc Rettig as a subscription premium for Database Programming and Design magazine.
http://www.marcrettig.com/poster/
Sunday, June 15, 2008
What not to do as a DBA
Here are some tips for what not to do as DBA – Newbie or not!
1. Rebuild an index in working hours
This will hit disk I/O very heavily. It is rarely useful to do this during normal working hours, so always schedule it for the evening or overnight - that is, during the period of lowest user activity. Difficult if you work in a 24/7 environment like myself. Look for maintenance window !
2. Stopping the database engine without warning
Why? Lots of frustrated users and a telephone (helpdesk or yours) that won't stop ringing. Trust me I’ve been here – inadvertently shutting down database servers without informing the application users is a cardinal sin!
3. Performing a service pack upgrade during working hours
Usually this involves re-starting the core database engine. Don't do it, again it'll annoy many people. And besides that’s what overtime is for. Follow the Change control !
4. Running test queries against live servers
Do you really know how long they'll run for or how much disk I/O they will demand? I thought not! Again, past experience has led me to be extremely cautious in this department. Test, test out on Development/ Test environment before hitting on Production always !
5. Being arrogant towards other teams in the department
Why? A few can be a complete pain, but explaining to them the issues and trying to work towards a good working compromise is usually more productive than treating them unsympathetically. Likewise the helpdesk guys - you need each other. Foster good relationships with your work colleagues, in the long term it pays dividends.
6. Backing up during working hours
Again it's all about disk I/O. It serves the backup or it serves your users. If you have to do it, look at differentials or transaction log backups: they take less time and reduce dropped connections as a result. I’ve recently had to change from full to differential backups because of space restrictions too. Alternatively, monitor the server and talk to your users/tape guys about the best time to perform one - but only if you really have to.
7. Executing patches/updates against live data
You are kidding, right? At the very least, test it against a live copy, and backup the live database before you apply it. And if possible have a regression script which will allow you to back out the updates if reverting to a backup is not possible. In my experience when users say they have confirmed and tested everything is ok – they haven’t really, give it 3/4 days later – they’ll spot something else!
8. Not securing your database servers
Get clued up on security. Your organisation may have its own Information Security Officers like mine; define user roles and password retention policies etc
1. Rebuild an index in working hours
This will hit disk I/O very heavily. It is rarely useful to do this during normal working hours, so always schedule it for the evening or overnight - that is, during the period of lowest user activity. Difficult if you work in a 24/7 environment like myself. Look for maintenance window !
2. Stopping the database engine without warning
Why? Lots of frustrated users and a telephone (helpdesk or yours) that won't stop ringing. Trust me I’ve been here – inadvertently shutting down database servers without informing the application users is a cardinal sin!
3. Performing a service pack upgrade during working hours
Usually this involves re-starting the core database engine. Don't do it, again it'll annoy many people. And besides that’s what overtime is for. Follow the Change control !
4. Running test queries against live servers
Do you really know how long they'll run for or how much disk I/O they will demand? I thought not! Again, past experience has led me to be extremely cautious in this department. Test, test out on Development/ Test environment before hitting on Production always !
5. Being arrogant towards other teams in the department
Why? A few can be a complete pain, but explaining to them the issues and trying to work towards a good working compromise is usually more productive than treating them unsympathetically. Likewise the helpdesk guys - you need each other. Foster good relationships with your work colleagues, in the long term it pays dividends.
6. Backing up during working hours
Again it's all about disk I/O. It serves the backup or it serves your users. If you have to do it, look at differentials or transaction log backups: they take less time and reduce dropped connections as a result. I’ve recently had to change from full to differential backups because of space restrictions too. Alternatively, monitor the server and talk to your users/tape guys about the best time to perform one - but only if you really have to.
7. Executing patches/updates against live data
You are kidding, right? At the very least, test it against a live copy, and backup the live database before you apply it. And if possible have a regression script which will allow you to back out the updates if reverting to a backup is not possible. In my experience when users say they have confirmed and tested everything is ok – they haven’t really, give it 3/4 days later – they’ll spot something else!
8. Not securing your database servers
Get clued up on security. Your organisation may have its own Information Security Officers like mine; define user roles and password retention policies etc
To find the DBCC execution progress
To find the DBCC execution progress;
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests
Wednesday, June 11, 2008
SLA - Service Level Agreement for SQL Server 2005
Do you know what an SLA is?
SLA = Service Level Agreement. SLAs are agreements between you and your customers. If you're a DBA, then your customer is typically the company for whom you work. Examples of SLAs are:
1. In the event of a corruption, or other disaster, the maximum amount of data loss is the last 15 minutes of transactions.
2. In the event of a corruption, or other disaster, the maximum amount of downtime the application can tolerate is 20 minutes.
Usually, it's a combination of SLAs such as those above.
Do you know why SLAs are important?
Here's the catch - an SLA is really more than just an agreement between you and your customers - it's more like a contract that you're obligated to meet. This means that if you're a DBA with zero-downtime and zero-data loss SLAs, you need to make sure that in the event of a corruption you can actually meet those SLAs. The obvious thing is that if the SLAs cannot be met then the business will suffer downtime and data loss. The not so obvious thing is that if you're the one who agreed to the SLAs in the first place, and when the disaster strikes, the capabilities of the system are far below the SLA's requirements, then you could lose your job - resume/CV time - I've heard of it happening...
Do you know your SLAs?
You have to know what your SLAs are so you can make sure the system can meet them. Several DBAs I discussed this with don't know what their business' SLAs are, even though they are responsible for making sure they are met. I find this astounding - how can you sign up for meeting an SLA when you don't know that the SLA is? Especially if failing to meet the SLA could lead to resume/CV time...
Do you think you can meet your SLAs?
The other reason to know your SLAs, of course, is so that you can correctly architect your system to meet them. There are a bunch of technologies you can use and strategies you can employ to work towards meeting your SLAs (well beyond the scope of this blog post but will be covered through the year). If you find that you can't meet your SLAs, you need to push-back on your management - otherwise you're setting yourself up for trouble when a disaster occurs and you can't meet the SLAs - you'll be held responsible.
Do you know you can meet your SLAs?
Your disaster recovery plan looks great on paper - but have you actually tried it? I know of one company that has a 15 minute downtime SLA for a 300+GB database but the DBA is relying on clusters to provide that for him. That won't work if the database is corrupt (remember a failover cluster has a single point of failure in its shared-nothing configuration - the disks) and needs to be restored from the last full backup... Another company I know of relies on database mirroring to failover in the event of a disaster but has never tried it to see if their application fails over gracefully... You have to make sure you've practiced recovering from a disaster before the first real disaster happens - you'll be amazed at the little things that are discovered (e.g. if the on-site backups are bad, how long will it take to get the offsite copies brought in-house from the off-site location 100 miles away? Can you still meet your 15 minute downtime SLA in that case?)
SQL Server Specific on SLA
Hours of Operation
1. Hours that the partition/table/database is available to users. The SLAs may be different for various parts of the database, depending on what applications need access to them. Why is it important to differentiate at various granularities? For example, depending on how the unit of data is used, it may require different maintenance than other data and so knowing the availability SLA allows maintenance downtime to be planned.
2. Hours reserved for planned downtime. Again, this may differ at various granularities of data.
3. Amount of advance notice for extended downtime or other changes that affect users. For instance, when my bank upgraded its computer system last year, they gave a series of warnings over the preceeding few months so that people weren't surprised.
Service Availability
1. Percentage of time SQL Server service is running and able to service connections.
2. Percentage of time a particular partition/table/database is available for use (i.e. not exclusively locked for maintenance or restore).
System Performance
1. Number of concurrent users the system supports.
2. Number of transactions supported per unit of time.
3. Acceptable level of performance, such as latency experienced by users for a variety of operations.
4. Minimum time for an update to be replicated to various remote sites.
Disaster Recovery
1. Time allowed for recovery from each type of failure (e.g. accidental data deletion, database corruption, SQL Server crash, OS crash, server failure, site failure).
2. Time it takes to bring critical data online (e.g. the read/write partitions of a sales database) such that operations can continue and less critical data can be recovered later.
3. Time taken to recover data to the point of failure.
4. Maximum acceptable data/transaction/work loss for various kinds of failures.
5. Maximum time for application failover to a remote server/site.
Support
1. Methods available for application users to get help.
2. Maximum response/resolution time from a DBA to respond to various types of problems.
Other
1. Maximum amount of space for user tables/databases.
2. Amount of users in specific roles.
Some other things to consider are how you define an SLA - for example, in transactions per second or in commit latency that users experience - and the interplay between SLAs - for example, the commit latency SLA may be affected if the acceptable data loss SLA is zero and a solution such as synchronous database mirroring or remote SAN mirroring is used.
Bottom line is that although it can be simple to quickly define and announce a set of SLAs for a given application, its very difficult to make sure that each is palatable to all involved, guarantee that each can be met, and allow easy diagnosis of the system to work out which component is failing when an SLA is not met. SLAs really need to be defined while a sytem is being designed as retro-fitting SLAs after-the-fact can be very time-consuming and costly.
SLA = Service Level Agreement. SLAs are agreements between you and your customers. If you're a DBA, then your customer is typically the company for whom you work. Examples of SLAs are:
1. In the event of a corruption, or other disaster, the maximum amount of data loss is the last 15 minutes of transactions.
2. In the event of a corruption, or other disaster, the maximum amount of downtime the application can tolerate is 20 minutes.
Usually, it's a combination of SLAs such as those above.
Do you know why SLAs are important?
Here's the catch - an SLA is really more than just an agreement between you and your customers - it's more like a contract that you're obligated to meet. This means that if you're a DBA with zero-downtime and zero-data loss SLAs, you need to make sure that in the event of a corruption you can actually meet those SLAs. The obvious thing is that if the SLAs cannot be met then the business will suffer downtime and data loss. The not so obvious thing is that if you're the one who agreed to the SLAs in the first place, and when the disaster strikes, the capabilities of the system are far below the SLA's requirements, then you could lose your job - resume/CV time - I've heard of it happening...
Do you know your SLAs?
You have to know what your SLAs are so you can make sure the system can meet them. Several DBAs I discussed this with don't know what their business' SLAs are, even though they are responsible for making sure they are met. I find this astounding - how can you sign up for meeting an SLA when you don't know that the SLA is? Especially if failing to meet the SLA could lead to resume/CV time...
Do you think you can meet your SLAs?
The other reason to know your SLAs, of course, is so that you can correctly architect your system to meet them. There are a bunch of technologies you can use and strategies you can employ to work towards meeting your SLAs (well beyond the scope of this blog post but will be covered through the year). If you find that you can't meet your SLAs, you need to push-back on your management - otherwise you're setting yourself up for trouble when a disaster occurs and you can't meet the SLAs - you'll be held responsible.
Do you know you can meet your SLAs?
Your disaster recovery plan looks great on paper - but have you actually tried it? I know of one company that has a 15 minute downtime SLA for a 300+GB database but the DBA is relying on clusters to provide that for him. That won't work if the database is corrupt (remember a failover cluster has a single point of failure in its shared-nothing configuration - the disks) and needs to be restored from the last full backup... Another company I know of relies on database mirroring to failover in the event of a disaster but has never tried it to see if their application fails over gracefully... You have to make sure you've practiced recovering from a disaster before the first real disaster happens - you'll be amazed at the little things that are discovered (e.g. if the on-site backups are bad, how long will it take to get the offsite copies brought in-house from the off-site location 100 miles away? Can you still meet your 15 minute downtime SLA in that case?)
SQL Server Specific on SLA
Hours of Operation
1. Hours that the partition/table/database is available to users. The SLAs may be different for various parts of the database, depending on what applications need access to them. Why is it important to differentiate at various granularities? For example, depending on how the unit of data is used, it may require different maintenance than other data and so knowing the availability SLA allows maintenance downtime to be planned.
2. Hours reserved for planned downtime. Again, this may differ at various granularities of data.
3. Amount of advance notice for extended downtime or other changes that affect users. For instance, when my bank upgraded its computer system last year, they gave a series of warnings over the preceeding few months so that people weren't surprised.
Service Availability
1. Percentage of time SQL Server service is running and able to service connections.
2. Percentage of time a particular partition/table/database is available for use (i.e. not exclusively locked for maintenance or restore).
System Performance
1. Number of concurrent users the system supports.
2. Number of transactions supported per unit of time.
3. Acceptable level of performance, such as latency experienced by users for a variety of operations.
4. Minimum time for an update to be replicated to various remote sites.
Disaster Recovery
1. Time allowed for recovery from each type of failure (e.g. accidental data deletion, database corruption, SQL Server crash, OS crash, server failure, site failure).
2. Time it takes to bring critical data online (e.g. the read/write partitions of a sales database) such that operations can continue and less critical data can be recovered later.
3. Time taken to recover data to the point of failure.
4. Maximum acceptable data/transaction/work loss for various kinds of failures.
5. Maximum time for application failover to a remote server/site.
Support
1. Methods available for application users to get help.
2. Maximum response/resolution time from a DBA to respond to various types of problems.
Other
1. Maximum amount of space for user tables/databases.
2. Amount of users in specific roles.
Some other things to consider are how you define an SLA - for example, in transactions per second or in commit latency that users experience - and the interplay between SLAs - for example, the commit latency SLA may be affected if the acceptable data loss SLA is zero and a solution such as synchronous database mirroring or remote SAN mirroring is used.
Bottom line is that although it can be simple to quickly define and announce a set of SLAs for a given application, its very difficult to make sure that each is palatable to all involved, guarantee that each can be met, and allow easy diagnosis of the system to work out which component is failing when an SLA is not met. SLAs really need to be defined while a sytem is being designed as retro-fitting SLAs after-the-fact can be very time-consuming and costly.
Subscribe to:
Posts (Atom)