We help IT Professionals succeed at work.

Defind RTO and PTO for one of db instance

450 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
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT

Commented:
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

Author

Commented:
I must put there  Excel accidently

Author

Commented:
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

Author

Commented:
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?

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
I will upload attachment again.

How i can generate number transaction per minute against specific database?
CERTIFIED EXPERT

Commented:
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!
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
How I can generate Peak Number of transactions per minute for specific database?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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'
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
So this is one query. Or these are separate steps I need to follow.

How I can set time

SELECT *
FROM TransPerMin
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
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

Author

Commented:
Here is the spread sheet with trans per minute
What cntr_value and cntr_type means?
Trans-per-minute.xlsx
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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'

Author

Commented:
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'
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Let me review this.
Looks like the value is since SQL Server has started and not really per minute.

Author

Commented:
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'

Author

Commented:
Do you know what kind of value (  1745812500 ) is this ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

Commented:
I got this message

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
CERTIFIED EXPERT

Commented:
@mickalek19 - Not very helpful

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

Author

Commented:
When I ran last Vitor''s query that's what I got.
Nothing else?
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Let me check that. I thought I have to run each query individualy.

Author

Commented:
I ran them all almost one after another.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
I will run it again.

Author

Commented:
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
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you very much
CERTIFIED EXPERT

Commented:
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)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.