Creating a good set of SQL maintenance plans

Here is an example of a good set of SQL maintenance plans for a server hosting the following:

- Dynamics GP production and test databases

- SSRS databases

First, the database properties need to be checked.

Determine which databases you will do log backups on.  These will need the recovery model set to Full.  All other system and user databases needs the recovery model set to Simple to prevent runaway log file growth.

Usually:

DYNAMICS and all production company databases are set to Full.

Mobiletech databases (RESCO databases) should be set to Simple (recommendation from WennSoft)
ReportServer databases are set to Simple

Next, determine which databases need to be backed up and how often

Do not backup tempdb.  It gets rebuilt when SQL Server is restarted, so you would never restore it anyway.  When I refer to system databases, I am only referring to master, model, and msdb.

Remember to backup ReportServerTempDB.  This is not rebuilt like tempdb and you will need it if you ever need to restore ReportServer.  You may not necessarily need the contents, but you will need the structure.

A typical schedule would be:

system databases - monthly - these databases rarely change

SSRS and non-production databases - weekly - SSRS databases do not significantly change unless new reports are deployed and test databases are typically more tolerant of data loss in a recovery situation.  Much of the time, test databases do not need backups as long as they are regularly refreshed from production so the log file does not grow too large.

DYNAMICS and production databases - nightly - these databases should also be Full recovery model with log backups throughout the day.

Counting the logs, this is 4 schedules, so you will need 4 plans.

Next, set up the plans

monthly - scheduled for 1st Sunday of the month at 10pm

1. Check database integrity task - no point in backing up a corrupt database

 - specify all system databases

2. Backup database task

- backup type Full

- specify all system databases

- choose create backup file for each database

- specify the backup folder

- set backup extension to bak

- check verify backup integrity

3. Rebuild Index Task

- specify all system databases

- take the defaults

4. Update Statistics Task

- specify all system databases

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to bak

- check delete files based on age

- set age to 3 months

6. History Cleanup Task

- select all options

- set age to 3 months

weekly - scheduled for Sundays at 11pm

1. Check database integrity task - no point in backing up a corrupt database

 - specify all non-system databases (you may be able to omit test databases)

2. Backup database task

- backup type Full

 - specify all non-system databases  (you may be able to omit test databases)

- choose create backup file for each database

- specify the backup folder

- set backup extension to bak

- check verify backup integrity

3. Rebuild Index Task - we will do this for all databases weekly

 - specify all non-system databases (you may be able to omit test databases)

- take the defaults

4. Update Statistics Task

 - specify all non-system databases (you may be able to omit test databases)

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to bak

- check delete files based on age

- set age to 12 weeks

daily - scheduled for Mon thru Sat at 11pm

1. Check database integrity task - no point in backing up a corrupt database

 - specify all non-system databases that are set to Full 

2. Backup database task

- backup type Differential

 - specify all databases that are set to Full

- choose create backup file for each database

- specify the backup folder

- set backup extension to dif

- check verify backup integrity

3. Reorganize Index Task 

 - specify all  non-system databases (you may be able to omit test databases)

- take the defaults

4. Update Statistics Task

 - specify all  non-system databases (you may be able to omit test databases)

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to dif

- check delete files based on age

- set age to 2 weeks

log backup - scheduled daily every 4 hours from 8am to 8pm

1. Backup database task

- backup type Transaction Log

- specify all databases that are set to Full

- choose create backup file for each database

- specify the backup folder

- set backup extension to trn

- check verify backup integrity

If a database is set to Full recovery model, you NEED TO setup transaction log backups.  If you forget this, your log file will grow until it hits the max size or fills up the drive. If you do not want to do transaction log backups on a database, set it to Simple recovery model. 

Manually run the plans in the following order;

monthly

weekly

daily

logs

This is important, not just to test them, but to establish full backups for the differentials to work off of.

Fun with Pivot tables

Update:

Here is a link to a much better explanation of Pivot Tables

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/

Wow, it has been almost a year since my last post!  Well, back to it.  This is an update to the original post below.  Additional info added to the end:

Original post 2012.04.19:
I continue to get questions on how to generate pivot tables.  Although the concept seems complicated, the process can actually be quite simple.

Say I have data that looks like this:

and I want it to look like this:

You can accomplish with using a pivot query.

First, let's look at the SQL that generates the data set you are working with.
SELECT
  WS_Appointment_ID,
  Service_Call_ID,
  Appointment,
  Technician,
  Appointment_Status,  --this will be out pivot column
  (MODIFDT + Modified_Time) as ModDateTime
FROM
  SV30301
WHERE
  WS_Appointment_ID > 3000
The first step to pivoting the data is to clearly define the base query (above).  Any concatenations or case statements need to be done here.  It is important that you identify which column you will pivot on.  That is the column you want to group horizontally.  In this case, it is the status.  You will then need to identify all possible values in this column.
BROADCAST
RECEIVED
ACCEPTED
IN ROUTE
ON SITE
PARTS HOLD
COMPLETE
These values will become your column names.

Next, you should determine what data you want to aggregate by these groupings.  Any aggregate function applies here, so you can use functions like SUM, AVG, MAX, and COUNT.  (see full list of aggregate functions)  In this case, we want to get the max date for each status.

So we first change our original query to prepare for adding the pivot logic:

SELECT
  WS_Appointment_ID,
  Service_Call_ID,
  Appointment,
  Technician
FROM (  SELECT
    WS_Appointment_ID,
    Service_Call_ID,
    Appointment,
    Technician,
    Appointment_Status,
    (MODIFDT + Modified_Time) as ModDateTime
  FROM
    SV30301
  WHERE
    WS_Appointment_ID > 3000
) AS source

Notice that we did not include the pivot column or aggregate column.  Those two columns will before a single column in the next step.

Next, we add the pivot logic:
SELECT
  WS_Appointment_ID,
  Service_Call_ID,
  Appointment,
  Technician,
  ISNULL(BROADCAST,GETDATE()) AS BROADCAST,
  RECEIVED,
  ACCEPTED,
  [IN ROUTE],
  [ON SITE],
  [PARTS HOLD],
  COMPLETE,
  UNASSIGNED 
FROM (
  SELECT
    WS_Appointment_ID,
    Service_Call_ID,
    Appointment,
    Technician,
    Appointment_Status,
    (MODIFDT + Modified_Time) as ModDateTime
  FROM
     dbo.SV30301

) AS source
PIVOT(
  MAX(ModDateTime)
  FOR Appointment_Status
  IN (
    [BROADCAST],
    [RECEIVED],
    [ACCEPTED],
    [IN ROUTE],
    [ON SITE],
    [PARTS HOLD],
    [COMPLETE],
[UNASSIGNED]
  )
) as pvt

In the pivot code, we first perform the aggregate operation on the aggregate column.  We issue the FOR keyword followed by the pivot column and then the list of all possible values in the pivot column.  Each value should be surrounded by [] (square brackets).  We then list all of those values as column names in the upper select statement.  Standard select logic applies here.  You can rename the columns at this point or perform other operations on them.
--------------------------------------

Update 20150112:

So the above method is great if you already know the values that are in the columns.  But what if you don't know the values and still need to pivot.    In that case, you will need to dynamically build the query.  That means you will need to use a stored proc instead of a view.

--recall that we are pivoting on the appointment status
--first we need to declare a variable and fill it with the possible values from the table
DECLARE @STATUS VARCHAR(MAX)
SELECT @STATUS=(SELECT DISTINCT '[' + RTRIM(Appointment_Status) + '],' FROM dbo.SV30301 WHERE RTRIM(Appointment_Status)!='' FOR XML PATH(''))
SELECT @STATUS=SUBSTRING(@STATUS,1,LEN(@STATUS)-1)

--now @STATUS will replace the "IN" portion and some of the fields in the select
--we must place the query in a variable as a string and then execute it

DECLARE @SQL  VARCHAR(MAX)
SELECT @SQL='SELECT
  WS_Appointment_ID, 
  Service_Call_ID, 
  Appointment, 
  Technician,' + @STATUS + ' FROM (
  SELECT
    WS_Appointment_ID, 
    Service_Call_ID, 
    Appointment, 
    Technician, 
    Appointment_Status,
    (MODIFDT + Modified_Time) as ModDateTime
  FROM
    dbo.SV30301

) AS source
PIVOT(
  MAX(ModDateTime)
  FOR Appointment_Status
  IN (' + @STATUS + ')
) as pvt'

EXEC (@SQL)

--that's it. 



Did this help you?  If so, please leave a comment!

The progression of inefficiency

I have thought about the irony of this from time to time, so I though I would write it down:

1970’s: The promise: Workers save 20 min every morning by driving their own car to work rather than waiting on a bus or walking.
The reality: Workers waste an hour in traffic each morning.

1980’s: The promise:  Computers will save time by letting us type it once and print it multiple times

The reality:  Workers waste an hour a day dealing with paper jams in dot matrix printers

1990’s:  The promise:  Microsoft Office will save us time by eliminating spelling errors and providing all the formatting options available from outside print houses.

The reality:  Workers waste an hour per document trying to fix small formatting errors.  An entire generation is born that will never know how to spell receive or which homonym to use.

2000’s:  The promise:  More features in the OS and larger hard drives will give us a paperless office, saving time looking for lost papers.

The reality:   Workers waste 20 minutes each morning waiting for their bloated OS to boot up and hours each day looking for lost documents in their huge hard drives.

2010’s:  The promise:  The cloud will save us time by allowing us to save our data and use our programs from anywhere.

The reality:  Slow transfer rates make fedex faster than large file transfers.  Losing your internet connection means your entire system is down.

If you think of any others, please leave a comment!

Link to post on SSRS report parameters not working

http://chrisblog.betterithosting.com/?p=38

SSRS divide by zero annoyance

I have this formula in a textbox on my report:

= IIF(Fields!CallInvoiceAmount.Value=0,0.00,
  Fields!CallProfit.Value/Fields!CallInvoiceAmount.Value
 )

Basically, I am just trying to calculate a percentage and I need to handle the cases where the denominator is zero to avoid a divide by zero error.

Now if all of the fields above are int fields, the formula works.  However, they are numeric fields (money values).  SSRS throws a #Error every time the CallInvoiceAmount value is 0.

After much aggravation trying to get this to work, I finally found some info on some message boards that spelled out the problem.  SSRS apparently evaluates the true and false conditions when there are numeric values involved, BEFORE it evaluates the IIF condition.

Now isn't that just illogical enough to raise Spock's eyebrow!

The prevailing suggestion was to embed code in the report a write a custom function to handle it and then call that.  However, there are issues invoved with that when working with aggregates, and I also need the following to work:

= IIF(sum(Fields!CallInvoiceAmount.Value)=0,0.00,
  sum(Fields!CallProfit.Value)/sum(Fields!CallInvoiceAmount.Value)
 )

The answer turned out to be simple, if still a work around.  I added a very tiny amount to the denominator.  This also allows me to remove the iif statement entirely:


= sum(Fields!CallProfit.Value)/(sum(Fields!CallInvoiceAmount.Value)+0.00001)

Of course, if the denominator or really zero you will get a huge percentage, but that is fitting since the real answer is undefined.
 

Did this help you?  If so, please leave a comment!

Converting a string to Numeric in SQL

I have run into this again and had to spend time compiling the answer, so I am posting it for future use.

I needed to convert a string to numeric in SQL.  Seems pretty straightforward, right?

select convert(numeric(19,5),'0.0')

So what happens with my string looks like this?

select convert(numeric(19,5),'abc0.0')

Error converting data type varchar to numeric.
Ok, so I need to make sure it is a number first.

select case isnumeric('abc0.0') when 1 then convert(numeric(19,5),'abc0.0') else 0 end

All done, right?  Not so fast.  All of the following return 1 (true)
select isnumeric('$0.0')

select isnumeric('123,000.0')

But the following result in an error:
select convert(numeric(19,5),'$0.0')

select convert(numeric(19,5),'123,000.0')

So we need to make some basic decisions about what we consider to be valid in our numeric value and replace out those offending characters before evaluating it.

select case isnumeric(replace(replace(replace(replace('123,000.0',',',''),'$',''),'+',''),'-','')) when 1 then convert(numeric(19,5),replace(replace(replace(replace('123,000.0',',',''),'$',''),'+',''),'-','')) else 0 end



Did this help you?  If so, please leave a comment!

Tweaking Office 2013

Ok, time to make the switch to Office 2013.  After installing, the number one complaint (according to hundreds of message boards on the topic) is that the applications seem to have taken a huge step back in the "look and feel" department.  There are even complaints that the white and gray color scheme is causing headaches.

While I agree that the default UI is strangely bland (considering the candy-coated colors of Win8), like any other MS transition, with a little poking around we can find ways to tweak office to make most users happy.

1. Hiding the start page.
The Start page is the page with the really huge icons that displays each time you open word or excel. If you are like me, it would save you a mouse click to just jump to the blank document 99% of the time. So here is how we hide it:
a. Open the office app
b. Click the blank document icon
c. Click File > Options > General
d. Uncheck the "Show the Start screen" option (it is in the same place for excel)

2. Getting your ribbon back
When I first started up word, the ribbons were hidden.  It can be annoying to have to click on the ribbon tab to display it each time.  To make it stick around:
a. First click on the ribbon tab (such as HOME)

b.  Then click the pin on the right bottom of the ribbon

 

 

3. Change the Default Save location

When you click save as, it will default to sharepoint or skydrive.  If you would like to change the default option that comes up and save another mouse click:

a. Go to Options > Save

 

b. Check the box for Save to Computer by Default and set the default file location



4. Remove the all-caps from the ribbon tabs.
If you are tired of the flaming all caps ribbon tabs, here is an easy way to change them.  I was able to change all but the FILE tab.  It is amazing how much more readable the tabs are with this simple change.
a. Go to Options > Customize Ribbon
b. In the right hand pane, right click on each ribbon name you want to change and choose rename
c. The name should already be in upper/lower case.  Type a space after the name and click ok

5. Change the information your status bar displays
Yes, the little bar at the bottom of the your app can display lots of helpful info and functions.
a. Right click on the status bar and choose the options you want.  I like to display line numbers in word and add num-lock and caps lock in excel.

6. Color scheme
Now for the most annoying part.  The extreme white-out color scheme.  The issue is really not the lack of color, but the high contrast color scheme.  No additional color schemes have been offered, so there is not really a good fix, but here are some things you can try that might make it more tolerable for you.
a. Turn down the brightness on your monitor.
b. Go to File > Account and try a different color scheme.  Granted, there is only white, light gray, and dark gray, but pick the one that is best for you.
c. In outlook, select view > View Settings > Other Settings and set the grid line style to solid.
d. In outlook, select view > View Settings > Conditional Formatting.  For the unread messages, change the font to bold italic to help differentiate new messages.

7. Reclaim some screen real estate in outlook.
By default the new outlook places a one line preview on each message in the preview pane.  This greatly reduces the number of messages you can see at once in the list and causes lots of scolling.  To turn this off:
a. Click the View Tab
b. Choose Message Preview
c. Choose Off

Change view to compact
a. Click the View Tab
b. Click Change View
c. Choose Compact

Remove the Profile Picture from the email
a. Click File > Options > People
b. Uncheck show user photographs

Shrink the Task Bar at the bottom
a. Click the ellipse on the task bar and choose Navigation Options

 

 

b. Edit the display order if you like and choose compact navigation.  This option will shrink the task bar and limit it to the left pane, reclaiming almost an inch of screen space in the reading pane.

 

 

Change to Reading Pane on the right layout.

a. Click View > Reading Pane > Right

Being a longtime "reading pane on the bottom" guy, it really ticks me off, but the engineers and testers have admitted that they all use reading pane on the right mode, so they didn't notice the severity to which they screwed up the other options with things like the email header that the user can't remove.  Indeed, when you change it to the right, it is imediately apparent that the entire outlook app layout was redesigned to accomodate only this view.

 

 

Did this help you?  If so, please leave a comment!