Solved

Defind RTO and PTO for one of db instance

Posted on 2016-10-17
48
67 Views
Last Modified: 2016-10-20
Hi

I need to provide some information to IT about.
I am not sure,  how I can generate this information?

i.      OR RTO
ii.      OR RPO
iii.      DR RTO
iv.      DR RPO

Is there any query or script that I can run against database to pull these numbers
v.      BAU Number of transactions per minute
vi.      Peak Number of transactions per minute

How I can determine, how much storage I would need in near features  
vii.      Day 1 Storage needed (approx. TB)
viii.      Storage needed 6 months after then (approx. TB)
ix.      Storage needed 1 year after then (approx. TB)

Thx, M
0
Comment
Question by:michalek19
  • 24
  • 17
  • 5
48 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41847829
DR stands for Disaster Recovery. What OR stands for?

Is there any query or script that I can run against database to pull these numbers
You can query the dm_os_performance_counters DMV.
SELECT *
 FROM sys.dm_os_performance_counters
 WHERE counter_name = 'transactions/sec'

Open in new window


How I can determine, how much storage I would need in near features
This is simple math calculation.
You'll need to know the actual database size for Day 1 storage but you'll need the database growth history to make the calculations for the future. You can get a script to give you that information from this article.
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41847897
RTO & RPO are business decisions and unique to your business!

We obviously cannot tell you what is important to your business, but Business Continuity is a decision that needs to come from the top guided by the requirements from the various parts of your business and is not just an IT Decision.

E.g if the power to your building fails for 3 days, how does it help that the computer room has power for 12 via its various UPS's?

E.g. If the DB is down for a week, how will the rest of the business cope?

NB the shorter the Business RTO, the more expensive it becomes
0
 

Author Comment

by:michalek19
ID: 41848937
I must put there  Excel accidently
0
 

Author Comment

by:michalek19
ID: 41848950
Hi Vitor


What cntr_value and cntr_type means?

Is this a size of storage agains size of against database size?

Can you clarify ?

Thx, Michal
Results.xls
0
 

Author Comment

by:michalek19
ID: 41849038
What OR stands for?  Operational Recovery (How long takes to recover application\db from the time of failure)

How I can calculate I/O disk performance ?

Can I use query to get this information?
0
 

Author Comment

by:michalek19
ID: 41849069
how I can calculate this? Is there a query to generate this data?

BAU Number of transactions per minute

Peak Number of transactions per minute
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41849560
What cntr_value and cntr_type means?
Explanation is in the link I sent you but what really matters is the cntr_value that shows you the current value for the counter. In this case the number of transactions per second.

Is this a size of storage agains size of against database size?
No. Is the number of transactions per second:
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'

Btw, I opened the file you attached and is unreadable (only strange chars).


What OR stands for?  Operational Recovery (How long takes to recover application\db from the time of failure)
That's RTO - Recovery Time Objective.


How I can calculate I/O disk performance ?
 Can I use query to get this information?
You can use a variation of the above query:
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'PhysicalDisk%'

Open in new window

0
 

Author Comment

by:michalek19
ID: 41849905
I will upload attachment again.

How i can generate number transaction per minute against specific database?
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41849913
What OR stands for?  Operational Recovery (How long takes to recover application\db from the time of failure)
That's RTO - Recovery Time Objective.


The RTO is how long it takes the business to recover not how long the IT systems takes to recover - there could be hours and hours of additional work required by the business to check/delete/amend transactions and catch up from the the IT downtime before a BAU situation can be declared.

In todays world you have to think Business Continuity, rather than just IT recovery!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41849919
How i can generate number transaction per minute against specific database?
The counter is per second and not per minute but you can multiply it by 60 to get the minute's value. For specific database just add the necessary filter (instance_name):
SELECT object_name, instance_name, cntr_value*60 trans_minute, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
    AND instance_name = 'TypeYourDatabaseNameHere'

Open in new window

0
 

Author Comment

by:michalek19
ID: 41849927
How I can generate Peak Number of transactions per minute for specific database?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41849936
There's no Peak number counter. Peak is only the higher value within a period so if you keep capturing these values you can put them in a table or an Excel sheet to get the Peak for the period you want.
0
 

Author Comment

by:michalek19
ID: 41849947
In order to run this query for certain numbers of hours to generate list of trans per minut what needs to be added to this query

SELECT object_name, instance_name, cntr_value*60 trans_minute, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
    AND instance_name = 'TypeYourDatabaseNameHere'
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41849963
The query is ok but you need to store the results somewhere and have the query running during some time.
To store the results you can use a database table:
CREATE TABLE TransPerMin
(
	capturedon datetime,
	database_name nchar(128), 
	cntr_value bigint,
	cntr_type int  
)
GO

Open in new window

Then create a job where you run the following step
INSERT INTO TransPerMin (capturedon, database_name, cntr_value, cntr_type)
SELECT GETDATE(), instance_name, cntr_value*60 trans_minute, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'

Open in new window

Set the schedule time as you need and then later you can query the table to get the values:
SELECT *
FROM TransPerMin

Open in new window

or have it exported to an Excel file.
0
 

Author Comment

by:michalek19
ID: 41849968
So this is one query. Or these are separate steps I need to follow.

How I can set time

SELECT *
FROM TransPerMin
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41849971
So this is one query. Or these are separate steps I need to follow.
This is a 3 steps set up:
  1. Creation of the structure (table);
  2. Capturing of data with a SQL Agent job;
  3. Reading of the captured information.

SELECT *
 FROM TransPerMin
Date and time are stored in the capturedon (Captured on) column so you can easily filter it:
SELECT *
FROM TransPerMin 
WHERE capturedon >= '20161016 08:00:00' AND captured on <= '20161016 12:00:00'

Open in new window

0
 

Author Comment

by:michalek19
ID: 41850074
When I ran this query

1:SELECT *
2:FROM TransPerMin
3:WHERE capturedon >= '20161016 08:00:00' AND captured on <= '20161016 12:00:00'

I got errors
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'on'.

How I can export results to csv file
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850078
My fault. There's a typo (extra space). Just removed it:
SELECT *
FROM TransPerMin 
WHERE capturedon >= '20161016 08:00:00' AND capturedon <= '20161016 12:00:00'

Open in new window

NOTE: Mind that the period is only an example and you'll need to provide the correct period time from your own data.
0
 

Author Comment

by:michalek19
ID: 41850083
there was misspelling "captured on"

1:SELECT *
 2:FROM TransPerMin
 3:WHERE capturedon >= '20161016 08:00:00' AND captured on <= '20161016 12:00:00'

I run this and I have no results even though, query executed successfully
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850089
NOTE: Mind that the period is only an example and you'll need to provide the correct period time from your own data.
0
 

Author Comment

by:michalek19
ID: 41850104
So,  If I need results from last 5 days I need to change date

  3:WHERE capturedon >= '20161015 08:00:00' AND captured on <= '20161019 12:00:00'

Or, I am wrong
0
 

Author Comment

by:michalek19
ID: 41850108
Here is the spread sheet with trans per minute
What cntr_value and cntr_type means?
Trans-per-minute.xlsx
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850109
You're right but you can have a wide period to fully cover 24h of each day:
WHERE capturedon >= '20161015 00:00:00' AND capturedon <= '20161019 23:59:59'
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:michalek19
ID: 41850122
what I need to change in the query to get only # of trans per minute in certain databases?

Right now I see all databases

1:INSERT INTO TransPerMin (capturedon, database_name, cntr_value, cntr_type)
2:SELECT GETDATE(), instance_name, cntr_value*60 trans_minute, cntr_type
3:FROM sys.dm_os_performance_counters
4:WHERE counter_name = 'transactions/sec'
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850132
What cntr_value and cntr_type means?
You didn't read the article, right? You can find the explanation there.
  • cntr_value is the value for the counter. In this case the number of transaction.
  • cntr_type is the counter type. In this case will be always PERF_COUNTER_BULK_COUNT, meaning that the cntr_value is an average number of operations completed during each second of the sample interval.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850137
what I need to change in the query to get only # of trans per minute in certain databases?
What's your knowledge level of SQL commands? You just need to add the necessary filter:
SELECT *
FROM TransPerMin 
WHERE capturedon >= '20161016 08:00:00' AND capturedon <= '20161016 12:00:00'
    AND database_name='AEEbre'

Open in new window

0
 

Author Comment

by:michalek19
ID: 41850170
This mean that AEEBRE database has 1,745,812,500 per minute. Is this correct? Am I reading this correctly
Is this even possible, this number looks very High
Unless, I am reading this incorrectly

Result
capturedon                             database_name      cntr_value              cntr_type
2016-10-19 09:10:35.540      AEEBRE                       1745812500           272696576
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850195
Let me review this.
Looks like the value is since SQL Server has started and not really per minute.
0
 

Author Comment

by:michalek19
ID: 41850201
I run these queries. What can be the problem

Step 1

CREATE TABLE TransPerMin
(
      capturedon datetime,
      database_name nchar(128),
      cntr_value bigint,
      cntr_type int  
)
GO


Step 2

INSERT INTO TransPerMin (capturedon, database_name, cntr_value, cntr_type)
SELECT GETDATE(), instance_name, cntr_value*60 trans_minute, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'


Step3.

FROM TransPerMin
WHERE capturedon >= '20161019 08:00:00' AND capturedon <= '20161019 12:00:00'
AND database_name='AEEBRE'
0
 

Author Comment

by:michalek19
ID: 41850241
Do you know what kind of value (  1745812500 ) is this ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850370
Try this version:
;WITH BeginData AS
(
	SELECT capturedon BeginDate, database_name, cntr_value
	FROM TransPerMin
	WHERE capturedon=(SELECT MIN(capturedon) FROM TransPerMin)
),
EndData AS
(
	SELECT capturedon EndDate, database_name, cntr_value
	FROM TransPerMin
	WHERE capturedon=(SELECT MAX(capturedon) FROM TransPerMin)
)
SELECT B.database_name, E.cntr_value-B.cntr_value, DATEDIFF(second,B.BeginDate,E.EndDate) Seconds, (E.cntr_value*1.0-B.cntr_value*1.0)/DATEDIFF(second,B.BeginDate,E.EndDate)*60 TransMinute
FROM BeginData B 
	INNER JOIN EndData E ON B.database_name=E.database_name

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41850375
By the way, is better to perform a cleaning before you run the above code.
Start by recreating the table:
DROP TABLE TransPerMin
GO
CREATE TABLE TransPerMin
(
	capturedon datetime,
	database_name nchar(128), 
	cntr_value bigint
)
GO

Open in new window


Then have the following running few times (preferable to have a SQL Agent job running this every minute or so):
INSERT INTO TransPerMin (capturedon, database_name, cntr_value)
SELECT GETDATE(), instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'

Open in new window


Finally run the query to get the information you want. Last column is number of transactions by minute:
;WITH BeginData AS
(
	SELECT capturedon BeginDate, database_name, cntr_value
	FROM TransPerMin
	WHERE capturedon=(SELECT MIN(capturedon) FROM TransPerMin)
),
EndData AS
(
	SELECT capturedon EndDate, database_name, cntr_value
	FROM TransPerMin
	WHERE capturedon=(SELECT MAX(capturedon) FROM TransPerMin)
)
SELECT B.database_name, E.cntr_value-B.cntr_value TransElapsed, DATEDIFF(second,B.BeginDate,E.EndDate) SecondsElapsed, (E.cntr_value*1.0-B.cntr_value*1.0)/DATEDIFF(second,B.BeginDate,E.EndDate)*60 TransMinute
FROM BeginData B 
	INNER JOIN EndData E ON B.database_name=E.database_name

Open in new window

1
 

Author Comment

by:michalek19
ID: 41850513
I got this message

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41851276
@mickalek19 - Not very helpful

What did you get this error from?  maybe a screenshot would help?
0
 

Author Comment

by:michalek19
ID: 41851278
When I ran last Vitor''s query that's what I got.
Nothing else?
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41851321
But he gave you 3, which one gave you the error, and what was the context, thats why i suggested a screenshot, so we can see what you are doing
0
 

Author Comment

by:michalek19
ID: 41851333
You hAve to run all of them but the last one gAve me an order

;WITH BeginData AS
(
      SELECT capturedon BeginDate, database_name, cntr_value
      FROM TransPerMin
      WHERE capturedon=(SELECT MIN(capturedon) FROM TransPerMin)
),
EndData AS
(
      SELECT capturedon EndDate, database_name, cntr_value
      FROM TransPerMin
      WHERE capturedon=(SELECT MAX(capturedon) FROM TransPerMin)
)
SELECT B.database_name, E.cntr_value-B.cntr_value TransElapsed, DATEDIFF(second,B.BeginDate,E.EndDate) SecondsElapsed, (E.cntr_value*1.0-B.cntr_value*1.0)/DATEDIFF(second,B.BeginDate,E.EndDate)*60 TransMinute
FROM BeginData B
      INNER JOIN EndData E ON B.database_name=E.database_name
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41851556
Divide by zero error can only be raised by this part of the last SELECT:
(E.cntr_value*1.0-B.cntr_value*1.0)/DATEDIFF(second,B.BeginDate,E.EndDate)*60

I think that you didn't add the 2nd query to a SQL job so it can capture more than one line. You should have that query running for some minutes so the last query can work properly.
0
 

Author Comment

by:michalek19
ID: 41851867
Let me check that. I thought I have to run each query individualy.
0
 

Author Comment

by:michalek19
ID: 41851879
I ran them all almost one after another.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41851883
I thought I have to run each query individualy.
Not for the 2nd one. Second one need to be run periodically so you can capture the counter values. You don't need to run it every minute since the counter is incremental.
For testing purposes you can run the 2nd query few times with some seconds or minutes of interval and then run the 3rd query to check the results. If you only run the 2nd query once it will return the divide by zero error.
0
 

Author Comment

by:michalek19
ID: 41851890
I will run it again.
0
 

Author Comment

by:michalek19
ID: 41852169
What does it mean ?

TransElapsed :?  
SecondsElapsed : ?
TransMinute  : Tras/per minute

How to find out number of transactions per minute comparing these two spread sheets ?
Database names:
ABE.ABEcom                                                                                                                  
ABE.MIL.Import                                                                                                            
ABE..MIL.Model                                                                                                      
ABE.DERT                                                                                                                
ABE.System

BAU Number of transactions per minute for
                                                                                                                 
Peak Number of transactions per minute for
Metrics-test.xlsx
Metrics-test-2.xlsx
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41852301
TransElapsed: Number of transactions during the elapsed period
SecondsElapsed: Elapsed period in seconds

How to find out number of transactions per minute comparing these two spread sheets ?
Trans/Minute is always depending on the period that you're analyzing but I would say it shouldn't be much different from periods. If you find some big difference that means in the specific period you're having more activity that the average.


BAU Number of transactions per minute for
Peak Number of transactions per minute for
Those values are within a period and depends on how many samples you got during that period.
0
 

Author Closing Comment

by:michalek19
ID: 41852388
Thank you very much
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41853264
Your original post is split into 3 sections - by EE rules they should have been 3 questions!

But the allocation of points doesnt seem to have followed the sub-questions

Section 1 - RTO/RPO - Answered by Me (but 0 points)
Section 2 - DB Metrics - Answered by Vitor (but given all the points!)
Section 3 - Storage - Answered by Vitor (by 0 Points)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now