Adding a Linked Server with ‘Oracle Provider for OLE DB’ as the provider – Proceed with caution !


Adding a linked server is an easy task which can be done at any point of time. I was under this same assumption until I came across a strange scenario.

CautionHere is the scenario for you all.

Use SSMS to create a New Linked Server and choose provider as ‘Oracle provider for OLE DB’

This one small step can bring the whole SQLServer to grinding halt if certain steps are not followed.

SSMS will be in frozen state during the New linked server creation step and eventually queries againt the databases will run forever.

Detailed explanation of the scenario

You wanted to create a new linked server with provider as Oracle provider for OLE DB.

Step 1 – The initial step to start of the whole process is to install the Oracle client on the SQLServer machine for which you want to add the linked server.

Step 2 – Oracle client installation will not force a reboot after the install is completed.

Step 3 – Without rebooting the server or recycling the SQLServices you will open SSMS and connect to the instance and see that the oracle provider is available.

LinkedServerIssue2

Step 4You will use SSMS wizard to create a new linked server and will choose the provider as Oracle provider for OLE DB.

LinkedServerIssue1

As soon as you select the provider you can notice that new linked server creation window will be in frozen state.

Any queries made against the databases will run indefinitely.

This situation will continue till you force a reboot of the machine. You cannot even recycle SQLServices as the service will fail to restart.

Think about a situation  –  You are doing this on a live production environment during normal business hours. As mentioned earlier this simple task can slowdown everything and can bring the server to a halt.In case the server is business critical, then rest is history(Start updating the resume !)

Right way of doing things

You will need to schedule changes like these ideally during a change window. You will need to recycle the SQL services or even bounce the box soon after the Oracle client is installed. This is a very important step. Once the server is up and running after the reboot you can either use SSMS to create the linked server or can opt to use sp_addlinkedserver system stored procedure. This time there wont be any slowness or performance related issues.

Conclusion

This simple scenario forced me to think twice and made up my mind to stick to one policy ‘Changes to business critical servers occurs only during a dedicated change window period’ no deviations what so ever.

Thanks for reading and keep watching this space for more.

Advertisements

Get Database information in mail using PowerShell !


Would you love to get a weekly status report of all the databases hosted on your mission critical servers? ,Would you love to get a report which will help you maintain and manage your large shared SQLServer environments neat and clean?

If your answer is Yes, then powershell is your solution. Welcome to the world of powerful powershell(Well, that sounds like a good marketing buzz word !).

logo-powershell

Lets’ think about a large enterprise situation where in you are managing a huge SQLServer shared server which has many databases. Each day your DBA team members will be adding new databases to the server, and you would like to get a status report to ensure that the databases are compliant to the regular database ‘rules’ as you are responsible for the well being of the environment.

Example – No database can have a compatibility level which is less than 110,No databases can have owner other than SA etc.

I’m a great fan of the articles written by Microsoft Scripting Guy, Ed Wilson(B) and one of his articles gave me a quick idea of deploying a powershell function which will pull the database details on a weekly basis.

I slightly modified his code mentioned in the article here (This is a very useful/detailed article, and I highly recommend this to everyone ) and my little function will pull database information such as Name,PageVerify,CompatibilityLevel,Size,LastBackupDate,Recoverymodel,Owner etc and will send across an email.

Note – All credits goes to Ed for his original code. I have great respect for Ed,and if you are interested in powershell, then I highly recommend reading his articles.

Here is the powershell function   –

#Remember to make changes for SMTP area

Function Get-DBInfo ($SQLInstance)

{

    if ($SQLInstance.Contains(“`\”))

        { $location = “SQLSERVER:\SQL\$SQLInstance\Databases” }

    else

        { $location = “SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases” }

    $style = ‘<style>’

    $style = $style + ‘BODY{background-color:peachpuff;}’

    $style = $style + ‘TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}’

    $style = $style + ‘TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}’

    $style = $style+ ‘TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}’

    $style = $style + ‘</style>’

        $body=dir -force  $location | where-object {$_.Name; $_.Refresh()} |

        Select Name, CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |ConvertTo-Html -Head $style -Property Name,CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |Out-String

        #Set your smtpserver information

        Send-MailMessage -To “anupsiva.das@abc.com” -Subject “Database Details from SQL2012 – Notification” –From “dba@abc.com” -SmtpServer “mailserver.abc.com” -Body $Body -BodyAsHtml

}

Get-DBInfo SQL2012

<Modified 01/27/2013> Above code was modified to pass the correct variable <Modified>

If you schedule this function as a SQL Job then you will receive automated emails according to your requirements.

Here is a sample email which was generated by the Get-DBInfo function.

Powershell Notification

Conclusion

Automated reports is going to help DBAs a lot and powershell is making life more easier by helping us to create highly flexible functions.

Thanks for reading and keep watching this space for more.

Check the version of Windows Installer !


This post is a quick tip to find the version of Windows Installer installed on your box.

You might wonder why I ever need to check this version, so here is my small story –

Today I was installing SQLServer 2008 Upgrade advisor for a Windows Server 2003(SP2) box(Pretty old one) and right away got this error –

CopyIssue3

The error message was pointing that the server might be running an old version of windows installer.

I decided to check what was the basic system requirements to install SQLServer 2008 Upgrade Advisor. Oh btw, that check should have been done earlier, but you know these things happen sometimes ! [With a promise not to repeat it later on].

SQLServer 2008 Upgrade Advisor needs Windows Installer 4.5 to work and that’s clearly mentioned in the download page.

WindowsInstaller1

Now I was curious to see what is the version of windows installer which is currently running.

How do I do that? I haven’t bothered to check that in the past too.

Confusedboy

After doing some random search I finally got a simple answer to my question, thanks to http://stackoverflow.com/.

The best and easiest way to check the version is by typing msiexec in Run prompt to get

WindowsInstaller2

This quickly tells you about the version.

I did little more checks to find that there is yet another way to check the version details and that’s by directly checking the .DLL files.

Note – System files should never be modified, moved or deleted.

WindowsInstaller3

The above picture shows the details of Windows Installer version once it was updated to 4.5 which was the basic requirement for SQLServer 2008 Upgrade Advisor.

I know you are now thinking of Windows Installer 5.0,here is that information for you.

The Windows Installer 5.0 is part of the Windows Server 2012, Windows 8, Windows Server 2008 R2, and Windows 7. There is no redistributable for Windows Installer 5.0.

SQL Server 2012 Upgrade Advisor also needs Windows Installer 4.5.

Conclusion

One error helped me to learn something more about windows installer and if you are interested too,then here are some links for you

http://msdn.microsoft.com/en-us/library/windows/desktop/cc185688(v=vs.85).aspx

http://en.wikipedia.org/wiki/Windows_Installer

http://www.advancedinstaller.com/user-guide/msiexec.html

Thanks for reading and watch this space for more.

Quick alert on servers which are not compliant for policies set using PBM


Last month I wrote about leveraging policy based management for regular DBA compliance checks. You can read that post here.

Policy based management(PBM) is indeed one of my favorite features. I normally like to play around exploring more and more about it, and eventually end up learning something new(Yay!).

Today’s post is based on that surprise learning. Small but very useful feature.

PBM currently lacks flexible reporting capabilities like sending an automated email to the DBA every week stating compliance status or even a reporting dash-board for the DBA to constantly monitor the details. I hope Microsoft is working on these enhancements, and we can expect some good news when next major bits of SQLServer is out.

Note – Enterprise policy management framework has great enterprise wide policy compliance reporting capabilities, and I’m still exploring this great tool. This is a codeplex project and you can explore the same by following http://epmframework.codeplex.com/

So here is my little story – When I walk in to office on a Monday morning I wanted to know which servers are having compliance issues. To start of I ‘m interested to check my super critical business servers. I used to connect to those servers and do a View History(Under Management->Right click Policy Management)  to get the details of compliance issues.

Note – I evaluate my policies on a schedule.

Recently I observed a quick alert when connected to a  server from SSMS which was telling me that there was a policy compliance issue, and I need to act on it.

The alert looks like – PBM Visual Alert There was indeed a policy violation which was picked by a schedule which ran on the weekend. Monday morning when I connected to that box,SSMS reported me that there is a policy violation via the above described small(but very helpful) graphical alert.

Once we fix the compliance issues, the alert is removed. PBM Visual Alert2 Conclusion

These simple but useful features is indeed helpful for DBA’s to have better control over compliance issues and I’m really looking forward for more and more features and enhancements for PBM from Microsoft SQLServer team.

Thanks for reading and keep watching this space for more.

Leverage Policy Based Management for regular Page Verify =Checksum compliance monitoring


Page Verify = Checksum is one option which I like all my servers to be compliant. By default all the new databases which gets created will have page verify = checksum as this option is inherited from the model database.

NOTE – APPLICABLE FROM SQL2005 AND ABOVE.

SELECT name,page_verify_option_desc FROM sys.databases
WHERE database_id=3

name                                page_verify_option_desc
=======                         ==================
model                               CHECKSUM

So far so good. But what will happen if we restore a database from SQL 2000 or even restore a database which is having a different page verify option other than checksum.

Lets check that out –

For the demo purpose on Server A we already have a database named Check_Page_Verify which is having a different page_verify option set as NONE(Bad idea !).

****Server A****

--Check the page_verify option of the db
SELECT name,page_verify_option_desc FROM sys.databases
WHERE database_id=6

name                         page_verify_option_desc
=====                       ==================
Check_PageVerify     NONE

We will back this database up and will restore it in Server B.

****Server A****

--Backup the database
BACKUP DATABASE Check_PageVerify
TO DISK='I:\Check_PageVerify.bak'
****Server B****

--RESTORE the database
RESTORE DATABASE Check_PageVerify
FROM DISK='I:\Check_PageVerify.bak'
WITH MOVE 'Check_PageVerify' TO 'D:\Check_PageVerify.mdf',
MOVE 'Check_PageVerify_log' TO 'D:\Check_PageVerify_log.LDF'

“Data file and log file in the same drive ! (Bad idea),however as this is a demo we can ignore this.”

In Server B lets check the page_verify option

****Server B****

--Check the page_verify option of the db
SELECT name,page_verify_option_desc FROM sys.databases
WHERE database_id=6

name                              page_verify_option_desc
=====                            ==================
Check_PageVerify          NONE

I started this post by saying Page Verify = Checksum is one option which I like all my servers to be compliant. Now after this restore my server is not complaint anymore for page verify. I will never come to know about this miss until and unless I regularly do compliance checks.

So how do we do that compliance checks ? This is where Policy Based Management (PBM) comes into picture.
We will need to create a condition first using the wizard.

PBM PageVerify1

I’m using facet ‘Database options’ for this demo and added the expression @pageverify= checksum.

Now, lets create a policy for this condition.

PBM PageVerify2

When creating the policy we will need to choose the condition which we created earlier. The evaluation mode for this policy are of 3 types

1. On demand 2.On schedule and 3.On change Log only.

To know more about PBM concepts please ref http://technet.microsoft.com/en-us/library/bb510667.aspx 

We are picking On schedule for this demo. This option needs a schedule to be created. We will create a schedule which will run at 12 AM every Sunday.

PBM PageVerify3

Once the schedule is created we need to ensure that policy is enabled.

PBM PageVerify4

The idea is to have the schedule run every Sunday to check if there are any databases which are violating the rule of page verify = checksum and on Monday morning when we come to work we will just need to check history of the policy to see if there are any violations.

For the demo purpose, lets trigger the schedule to run manually.

PBM PageVerify5

This is the job which was created automatically for the schedule which we created earlier.

After running the job successfully, all we need to do is check the history of the policy

PBM PageVerify6

There you go, we got the first violation details of the policy

PBM PageVerify7

It clearly says database Check_PageVerify is not compliant for the policy which we created.

If you have multiple databases which are not compliant, then you have the option to evaluate the policy manually ,choose the one which are not complaint and force them to be complaint.

PBM PageVerify8

PBM PageVerify9

PBM PageVerify10

PBM PageVerify11

Great, now we have everything under control and all the databases are compliant.

Conclusion

There are great things which we can do with PBM and if you have a better way to achieve this using PBM(Alternate facets,condition etc) or any other methods, then I’m eager to hear from you.

Keep watching this space for more.

Thanks for reading.

DBCC Memorystatus SQL2012 – A quick observation for node Process/System Counts


This post is not a detailed explanation of DBCC Memorystatus for SQL2012,but a quick observation I made recently for the node Process/System counters.

This is one area to look at under DBCC Memorystatus output to get a quick snap shot of the current state of memory and understand if there is a memory pressure.

In SQL 2012 you are provided with this information,ie info from the Process/System counters right away you run DBCC Memorystatus. This is the first set of data which is retrieved by the DBCC command.

DBCCMem1

I really liked the idea of getting information like system physical memory high/low, Process physical/virtual memory low etc right away I run DBCC as this info is one which I look at all the time during initial analysis phase.

In SQL 2008 R2,you still have this data but you will need to scroll all the way down to get this info. Still good, atleast we have this info for a quick ref.

In SQL2005,you don’t have this info at all.Yes,things are not so easy during the early days !

Conclusion

The process and system counters section is very useful and keep watching this space for more.

Thanks for reading.

SQLAgent Job related reports and out of box real time job monitoring tool !


Lets start New Year with a very simple yet useful topic.

I am a big fan of out of box reporting capabilities of SSMS. Some of the reports are really useful.

One of the reports which I really like is the Top Jobs report. This one can be pulled by traversing through –

Job Monitoring 1

The report comes in really neat with a graph and 3 major information related to SQL agent jobs.

Job Monitoring 2

The ones which I’m really interested all the time are 20 Most Frequently Failing Jobs and the 20 Slowest jobs.

This information is very important for me as I can trend the job information over a period of time(Even though its for a weeks time) for the most business critical servers.

Job Monitoring 3

The reports provide lot of useful information such as Avg Run Duration,Total Executions etc which will help you to observe how jobs are behaving under normal circumstances and can even use this as a baseline. Pretty neat stuff out there.

We also have the Job steps execution history report which will provide us with information regarding various steps within the job. Again a useful one if you are having lot of steps which are configured for the same job.

Now lets look at a scenario  – Your server is hitting 100% CPU and you believe that a heavy SQL job running at that point of time might be a reason for this spike.

How do you check which SQL job is running at that very point of time? Well, there are multiple ways to accomplish this. A TSQL code which joins couple of job related MSDB tables can quickly pull this information for you.

What if there is a light weight tool sitting right inside SSMS which can pull that information for you, lets look at that right away

Job Activity Monitor option will help you to monitor SQL jobs real-time.

Job Monitoring 4

If you need to know about jobs which are executing right now, then you will need to use the filter option and apply the required status.

Job Monitoring 5

Job Monitoring 6

Conclusion

There are multiple ways to monitor your jobs and its statistics, however the above mentioned methods are simple and efficient to get a quick view of SQLAgent jobs and related information.