One error which you don’t want to see while doing upgrades – Missing MSI !


The cached MSI file ‘C:\Windows\Installer\<name>.msi’ is missing !!! This is one error which you don’t want to see when you are trying to do a service pack upgrade or a CU update during a critical change window.  Here is what the error looks like  -

Missing MSI1

If you are hit with this error then there is definitely good amount of help out there, but the amount of time it takes to fix the error can vary from case to case. There is a good amount of chance that there are multiple MSIs which are missing, and you will need to take action to replace the missing MSIs before trying to re-run the SP/CU setup again.

So why this error comes up ? The short answer to this question is that the Windows Installer Cache(Under C:\Windows\Installer) on your server is compromised. This folder is used to cache important setup files, and these files are needed during situations like patch upgrades or even for uninstalls.  If this cache is compromised, then you will definitely need to work with your  windows admins to understand why it was compromised.

As mentioned earlier there is a good amount of chance that multiple MSIs are missing, and replacing them is time consuming.

Microsoft has a great KB article http://support.microsoft.com/kb/969052 which will explain the entire process of taking corrective actions to fix this problem, and there is a smart VB script which will tell you the details of files which are missing and it will provide you commands to take corrective action. I would definitely encourage you to read this article once.

In short here are the steps which I followed when faced this error while doing an SP2 upgrade for SQL2008 R2  -

1. Copied the FindSQLInstalls.vbs script from the KB and placed under C:\Script

2. Open a command window(As Admin) and ran the command

Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt

This created a txt file,and that file is your best bet for fixing the missing MSI errors.

3. Opened the txt file to see the amount of details it has on the missing msi files.  I was interested in the Action needed area where it will provide a copy command with the msi details.Something like -

Copy “O:\SQLINSTALL\SQL2008StdRTM\x64\setup\sql_bids.msi” C:\windows\Installer\df2ee59.msi

I ran the command(As Admin) and it copied the files to the cached folder. I scrolled down the txt file to understand that there are multiple MSI files which are missing,and I had to run the copy command multiple times(I counted till 15 !).

4. Once I completed running all the copy commands which were available in the txt file, I ran SP setup again and it was successful this time.

Being proactive

If you have a real tight change window and you don’t want to deal with these errors, then I would recommend running the VB script in advance for your server and see if its reporting any missing MSI for your SQL Server. If there are errors, then you can fix this in advance and ensure that the upgrade will go smooth. If there are no errors after running the VBscript, then there is a level of confidence that you will not hit the missing msi errors for sure.

Thanks for reading and keep watching this space for more !

Setting up the Min and Max Memory options on the fly – Word of caution !


Apparently,setting up the Min and Max Memory setting for SQLServer should be definitely encouraged and no server should be left out with the default settings.But here is a quick word of caution around this -

Do you want to change these numbers on the fly randomly when there is good amount of load on the production system ?

The answer is ‘No’. You shouldn’t be changing these numbers on the fly for your random testing on a production box with good amount of load.

Changing these numbers using sp_configure and hardening the changes using a reconfigure or doing it via GUI will clear the plan cache, and that’s not something which you would like to happen on a busy production box.

Let me show a quick demo to explain this.

Step 1

I will query the employee table which is available for the AdventureWorks2012 database

USE [AdventureWorks2012]
GO
SELECT * FROM [HumanResources].[Employee]

Step 2 

The above query will cause an adhoc plan to be stored in the cache. We can verify this by querying the plan cache

SELECT st.text , qp.query_plan , cp.cacheobjtype , 
cp.objtype , cp.plan_handle 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp 
WHERE objtype = 'Adhoc';

Min Max Cache Relation1

Step 3

I will change the Min Server Memory of the server using sp_configure

sp_configure 'Min Server Memory (MB)',501
RECONFIGURE

As soon as I run this command,I can check the sql server error log to see messages like

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Bound Trees’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Step 4

Query the plan cache again to see that its cleared

Min Max Cache Relation2

 

Conclusion

Changing instance level settings needs to done with care and if possible these should be done during a proper change/maintenance window for production systems.

Thanks for reading and keep watching this space for more.

Prevent creating stored procedures with RECOMPILE hint – Policy Based Management is your friend !


I got motivated to write this post as there was a question on twitter which was like  -

PBM Recompile 1

The user was checking if there was a way to prevent procedures which are created with RECOMPILE hint.

I was pretty sure that Policy Based Management did provide us a Stored Procedure Facet, and there will be some expressions which we can create using the same to ensure that no new stored procedures are created with this hint.

Will I use RECOMPILE hint ever in my stored procedure? That’s a huge ‘It depends’ question. I might not use it within a stored procedure, but at times I might have to use it during run time.

Kendra Little(B/T) has an excellent video on recompile, and you can find it here. [Really nice stuff out there !]

I did reply to the user that I’m positive that there will be some option, but it needs to be tested out .

So, this post is my test case to try and see if we can actually prevent users from creating stored procedures with RECOMPILE hint using PBM .

First of I created a condition using facet stored procedure and added an expression like what is shown in the image below -

PBM Recompile 2

I went ahead and created a policy which used the condition which was created in the first step, and chose evaluation mode as On Change : Prevent

PBM Recompile 3

Note – I’m doing this check for every database and every stored procedures as this is a test case. This can be checked against a single db using another condition and that’s the recommended approach.

Once the policy is created I made sure that its in enabled state.[Apparently nothing will be validated if the policy is not enabled ]

Next step is to try and create a stored procedure with recompile hint. For this purpose I will be using a database called SuperMan (Yes,just for fun).This db got a table called People and it has 3 rows of data.

PBM Recompile 4

I tried to create a simple stored procedure named FindSuperMan which will do a simple SELECT * from the table.This stored procedure will try to force a RECOMPILE hint.

CREATE PROCEDURE dbo.FindSuperMan 
WITH RECOMPILE
AS
SELECT * 
FROM [dbo].[People]
GO

As soon as I ran the command there was an error which said  -

Policy ‘Policy_Prevent_Recompile’ has been violated by ‘SQLSERVER:\SQL\TESTServer\DEFAULT\Databases\SuperMan\StoredProcedures\dbo.FindSuperMan’.
This transaction will be rolled back.
Policy condition: ‘@Recompile != True()’
Policy description: ”
Additional help: ” : ”
Statement: ‘CREATE PROCEDURE dbo.FindSuperMan
WITH RECOMPILE
AS
SELECT *
FROM [dbo].[People]
‘.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

What actually happens here is the transaction will be rolled back as it violated the policy which was defined.

Conclusion

This proves the fact that PBM can indeed be used to prevent some conditions which you don’t want to happen in your db/environment.

Thanks for reading and keep watching this space for more !

Memory Optimization Advisor – SQL Server 2014 CTP2


SQLServer 2014 CTP2 came with an inbuilt tool called Memory Optimization Advisor which will help you in migrating your normal tables to memory optimized tables.

Where can I find this tool ?

All you need to do is right click the table you want to migrate, and choose the option Memory Optimization Advisor.

MOA1

The tool will launch with a detailed description of what its capable of

MOA2

I decided to play around with this tool, and here are my observations -

Note - This is still CTP, so things can change during RTM/GA phase.

I started of with a normal table named Employee which has 3 Col and 3 rows data on it. Nothing big, pretty simple.

Launched the advisor, and the initial checks were all green. [Be sure to carefully analyze the checks, they are all interesting.]

MOA4

The wizard also has the ability to export a report (Who doesn’t like a report these days !)

I clicked next to proceed, and the wizard gave me some information about the limitations of memory optimized object, and a link which will explain the limitations in detail.

MOA5

I liked these warnings, because its telling me well in advance about the limitations so that I will be more careful on what I’m up to.(Everything has a cost associated with it !)

Next up is some interesting stuff. The wizard is forcing me to select the options for memory optimized objects. I have the option to mention memory optimized file group,name and the file path.

I also have the option to re-name the original table, copy data from the original table to the memory optimized table and a check box to mention if the table needs to be moved with no data durability.(Default being both schema/data durability).

I decided to go with all defaults as this was a test case.

MOA6

One of the other cool option which the above wizard window gave is this value -

MOA7

I presume that this value will be the cost of size in memory which will be needed when the table is moved as a memory optimized object. I might not be right at this point, but I will update this post in case this is not true.

Next screen in the wizard talks about primary key and index creation. I decided to make column ID as the primary key with a NON-CLUSTERED HASH Index and a bucket count of 1024.

MOA8

The final screen provided me a summary of my selections.

MOA9

Yet another cool feature in the wizard is that it allowed me to script everything before I finalize my selections.

I decided to hit Migrate, and wanted to see how it goes.

Viola,all clean and green !

MOA10

The wizard was smart enough to rename the old table,and created a memory optimized one for me.

MOA11

MOA12

I had scripted out everything before hitting migrate and that file looked like this -

MOA12_1

Conclusion

This is a very neat feel good to have tool and those warnings and pre-checks will definitely help users to streamline issues well before they are IN MEMORY !

Thanks for reading and keep watching this space for more !

Database Backup Encryption – SQLServer 2014 CTP2


SQL Server 2014 CTP2(All new,shiny shiny !) availability was announced yesterday at #SQLPASS Summit.

CTP2

Native backup encryption is one of the key features which was announced with this release, and I’m sure this feature will be widely used.

Native backup encryption will encrypt the data while creating the backup, and eventually you will end up creating an encrypted backup file. This is one of those features which was only provided by 3rd party tools [Similar to backup encryption].

Now backup encryption is out of the box for SQLServer 2014 !

So,what all are the pre-requisites to get started with this feature -

1.  You will need either Standard,Enterprise or BI edition of SQLServer 2014.

2. You will need to have a Certificate or a Asymmetric key.

3. You will need to choose the required encryption algorithm.

As we now know the pre-requisites ,lets try to create an encrypted backup and follow the process one by one.

First we will create a master key -

-- Creates a database master key. 
-- The key is encrypted using the password "Pa55word"
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa55word';
GO

 Followed by creating a Certificate

--Creates a certificate.
Use Master
GO
CREATE CERTIFICATE BackupCertificate
 WITH SUBJECT = 'Backup Encryption Certificate';
GO

Once all the keys/certificate requirements are taken care, we will proceed to create an encrypted backup file by specifying the certificate and a backup algorithm. The syntax is pretty simple -

--Create an encrypted backup file.
BACKUP DATABASE RockStar TO DISK =
'C:\SQL2014CTP2Backup\RockStar_Encrypted.bak'
WITH COMPRESSION,
 ENCRYPTION 
 (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCertificate);

That’s it ! Now we have an encrypted backup file and the result after running the above statement is   -

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
Processed 296 pages for database ‘RockStar’, file ‘RockStar’ on file 1.
Processed 2 pages for database ‘RockStar’, file ‘RockStar_log’ on file 1.
BACKUP DATABASE successfully processed 298 pages in 0.045 seconds (51.573 MB/sec).

 

You might have noticed the warning, it tells you that the certificate is not backed up. Its always recommend to backup the certificate as soon as you create it. [Be safe always !]

Also the algorithm which I mentioned in the above query, ie AES_256 is one among the 4 options available. The rest 3 are  -

AES 128, AES 192, and Triple DES

That’s a simple and straight forward way for creating an encrypted backup file using T-SQL.

GUI also offers this ability and the encryption options are available in the backup options.

BackupEncryGUI

Conclusion  – 

Native backup encryption is certainly a good to have feature and I’m looking forward to test this really well during the next few days.

Thanks for reading and keep watching this space for more.

Microsoft + Oracle = New opportunity…Windows Azure is a happening place !


Couple of months back(To be specific in June) Microsoft and Oracle announced their partnership to help enterprise customers embrace the cloud.

It was almost clear that Oracle was going to be available in Windows Azure under the IaSS platform(Virtual Machines’),and today during one of the Oracle Open World keynotes this was officially announced to be available.

I guess Brad Anderson(Corporate Vice President, Cloud and Enterprise Engineering, Microsoft) will be the first Non Oracle person to deliver a keynote during an Oracle Conference(Read Cloud is changing the game).

MSORA2

 

MSORA3

 

As of today our Oracle friends can configure and deploy their databases on Windows Azure. Here is what the Azure VM gallery looks like -

MSORA

 

MSORA1

 

Note  – Some of them are on preview though.

This is really an exciting news. Giants like Microsoft and Oracle teaming up is definitely going to benefit enterprise business, and I’m really positive about that.

Are you excited about this ? Please share your thoughts on the comments section.

Thanks for reading and keep watching this space for more !

Windows Azure Service Dashboard – Checkout the health of Azure Services


Windows Azure Service Dashboard is an excellent way to know the health/availability of your Windows Azure Services.

Azure Doctor

You might always want to know if any of the service is down or having some performance degradation, and with windows azure service dashboard this information is just few clicks away.

All you need to do is access http://www.windowsazure.com/en-us/support/service-dashboard/

This portal is available to all, irrespective of your account availability.

To me this is a great initiative by Microsoft to report service availability details as this is super useful from user perspective.

The portal is really impressive with the amount of information it provides. It will help you understand if

1. The service availability is normal

2. There is a performance degradation

3. There is a service interruption

4.There is any specific information(FYI type) regarding a service

WindowsAzureDash1

You can drill down each of the service and see what’s the status for each region

WindowsAzureDash2

Incase there is a problem, then drilling down the problem description will help you understand what is the current status of the fix, and how the support team is acting on the problem.

WindowsAzureDash3

I’m really impressed with this level of details. This will come handy when you have production deployments in azure and you need to keep your business users informed when there is a problem.

Now comes the icing of the cake -

Historical data ! Yes, you have the flexibility to see historical data and understand if there was a problem

WindowsAzureDash4

Conclusion

These are cool facilities made available to you by the Azure team and this will definitely prove helpful to you.

Thanks for reading and keep watching this space for more !