Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

Defind RTO and PTO for one of db instance

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
michalek19
Asked:
michalek19
  • 24
  • 17
  • 5
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Gerald ConnollyCommented:
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
 
michalek19Author Commented:
I must put there  Excel accidently
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
michalek19Author 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
0
 
michalek19Author 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?
0
 
michalek19Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
I will upload attachment again.

How i can generate number transaction per minute against specific database?
0
 
Gerald ConnollyCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
How I can generate Peak Number of transactions per minute for specific database?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author 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'
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
So this is one query. Or these are separate steps I need to follow.

How I can set time

SELECT *
FROM TransPerMin
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
NOTE: Mind that the period is only an example and you'll need to provide the correct period time from your own data.
0
 
michalek19Author 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
0
 
michalek19Author Commented:
Here is the spread sheet with trans per minute
What cntr_value and cntr_type means?
Trans-per-minute.xlsx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author 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'
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Let me review this.
Looks like the value is since SQL Server has started and not really per minute.
0
 
michalek19Author 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'
0
 
michalek19Author Commented:
Do you know what kind of value (  1745812500 ) is this ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
I got this message

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
0
 
Gerald ConnollyCommented:
@mickalek19 - Not very helpful

What did you get this error from?  maybe a screenshot would help?
0
 
michalek19Author Commented:
When I ran last Vitor''s query that's what I got.
Nothing else?
0
 
Gerald ConnollyCommented:
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
 
michalek19Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
Let me check that. I thought I have to run each query individualy.
0
 
michalek19Author Commented:
I ran them all almost one after another.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
I will run it again.
0
 
michalek19Author 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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
michalek19Author Commented:
Thank you very much
0
 
Gerald ConnollyCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 24
  • 17
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now