Caching Options in SSRS

One can enable caching for their reports in SSRS.  When we do that, the data is then retrieved from the ReportServerTempDB instead of real time execution of the query.  But there are things that you need to be aware of before you start caching the reports.   The caches can only be expired based on elapsed time or a schedule i.e. if the underlying data has changed and a request is made to render the report and the elapsed time/schedule has not hit yet, the end users will get the cached data rather than the latest and greatest data from the database.  This may or may not be an issue for your environment(s).  Also, remember that in case the report has parameters, then a cached version of the report is created for each unique set of parameter values.

So, what happens when we enable caching and run the report?  When it is run, then the ReportServerTempDB database is checked first to see if a cached version of the report exists and if it does, the data is then retrieved from the cache otherwise it is retrieved from the database and it is cached for future use.  So, in many situations where the underlying data does not change that often (i.e. not within the elapsed time/schedule), and there are many users of the report, caching can really help to reduce the load on your reporting instance.

Let’s walk through the steps of enabling caching for a particular report.  We will first need to start with the data source.  Any report that needs to have the caching enabled needs to have their credentials stored on the report server.

The windows account that is being used should have all the necessary privileges in place for being able to render the data from the underlying database(s).  Now, in order to enabling caching for a particular report, let’s open up the properties page for the report and then click on Execution – you will see something similar to this screen:

Over here, we can now choose to either:

a) Cache a temp copy of the report and expire it based on a schedule – default is 30 minutes.  Or

b) Create a schedule which could be a shared schedule or a report specific schedule.  When you click on the “configure” button for the specific schedule, the screen for the schedule details is pretty simple to follow and configure a schedule:

So, that is it.  Using these simple steps, we can configure caching for a report in SSRS.  And if you want to see what is going on from the DB side, you can always query the dbo.ChunkData, dbo.ExecutionCache and dbo.SnapshotData tables in the ReportServerTempDB database.  We can join all three tables together using SnapshotDataID column.  There are lots of very good articles on MSDN as well as in BOL which go over caching in great detail.  You can see those in the resources section below.

Resources:

• BOL enteries – here, preloading the cache – here,
• Database Journal article on report caching – here.

iPad is here…

This really puts Kindle sales in danger   But then, that was expected, wasn’t it?  When you have a solid team like Apple’s under the tutelage of a genius like Steve Jobs, would you expect anything less?  Here are some links:

iPad official Page: http://www.apple.com/ipad/

GigaOm article by Stacey Higginbotham: http://gigaom.com/2010/01/27/will-the-ipad-kill-the-kindle-in-a-word-yes/

Apple vs Google Mobile Wars

Here is an excellent article by Peter Burrows in BusinessWeek – he discusses the past, present and the future of this rivalry:

http://finance.yahoo.com/family-home/article/108600/apple-vs-google

Adaptive Cursor Sharing in 11g

A client DBA who had migrated their databases to 11g recently asked about the adaptive cursor sharing feature in Oracle 11g.  We have briefly talked about cursor_sharing before in one of our post.   Here is an excellent article on the topic of Adaptive Cursor Sharing by Oracle Ace Arup Nanda:

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-sqlplanmanagement.html

After reading that article, you would appreciate this new feature and how it is a boon to application developers.  Two take-aways should be:

a) Basic difference is that in prior releases – based on the cursor_sharing setting, the optimizer would optimize the queries for those bind values that are passed in for the first time (via bind variable peeking).  So, if for the very first execution of the query, you passed in a value which made use of a index access path, then it would use that same plan for all the rest of the values that get passed for that query on subsequent executions regardless of whether the next execution should have used a different execution plan.  This can cause issues in case of heavily skewed data values.

With adaptive cursor sharing, if with the first hard parse it used an index access path because the bind variable value that was passed in was very selective it does not necessarily mean that it will use that same path for the subsequent executions of that query with different bind values.  Upon execution on the first time, the optimizer would evaluate whether different bind variable values could lead to different execution plans or not and if it determines that it can, then it will flag the query.  It will mark that cursor to be bind sensitive (you can see that using the is_bind_sensitive and is_bind_aware columns of v$sql).  So, the optimizer will mark a cursor to be bind sensitive if it thinks that the optimal plan might depend and vary based on the bind variable value.

b) You would need to increase your shared pool accordingly since Oracle might need to create some child cursors for the different plans that it would need to create.

Resources:

• New Features for Developers and DBAs in Oracle 11g: The series by Arup Nanda – here.
• Another great article on this topic – here.

Comparison between MS SQL Server and MySQL

Read a good article on mssqltips.com comparing MS SQL Server with MySQL:

http://www.mssqltips.com/tip.asp?tip=1920&ctc

Also, there is good information available at the MSFT SQL Server site comparing MS SQL Server to MySQL, Oracle, DB2 UDB.  You can access it here:

http://www.microsoft.com/sqlserver/2008/en/us/compare-mysql.aspx

Data Driven Decision Making

Read an excellent interview of Tom Davenport in Intelligent Enterprise.  Take a look at the 5 stage DELTA model that he has described…you can read the interview here.

Nexus One is here

Google has now joined the smartphone market race with the launch of Nexus One.  It is also trying to change the market model by offering the phone unlocked so that it can be used with any carrier.  Here are some nice posts about this phone:

Google’s Nexus One Page – here.

CNN: http://www.cnn.com/2010/TECH/01/05/google.nexus.announcement/index.html

Om Malik’s Post: http://gigaom.com/2010/01/05/nexus-one-the-best-android-phone-yet/

A good comparison of Nexus One vs iPhone vs Droid vs Palm Pre: http://mashable.com/2010/01/05/nexus-one-vs-droid-vs-iphone/

The Export/Import Wizard

A client recently migrated from their SQL Server 2000 platform.  Yeah, I know – it took them quite some time to decide to move to SQL Server 2005 but then SQL Server 2008 was just around the corner and things kept getting pushed.  One of the questions that was asked by the client was about the Export/Import Wizard since they did not see that option anywhere in the menu options.  The wizard is still there.  There are a couple of different ways to access it.  The easiest would be to open up SSMS and then right click on the database, select Tasks and under it select the “Import Data” or “Export Data” option depending upon what you want to do:

Once that is done, you will get the wizard.

The steps are fairly straightforward and anyone who had used it in prior versions would be familiar with the steps so we are not covering those in this post.

Another way to start the wizard is to just invoke the exe itself.  If your paths are set up right, then you can go to Start/Run and then type DTSWizard.exe and the Export/Import Wizard will come up.  It’s location is:  X:\Program Files\Microsoft SQL Server\90\DTS\Binn (where X is the drive name where you did the install).

You can also invoke it from BIDS if you want to.  So, the wizard is still there, it is just not accessible in the same way as it used to be in prior versions.  It is a very powerful tool and comes in very handy for getting the data in and out of the system on an adhoc basis.  Also, if you are new to SSIS, this would be a good way to create a package via the wizard, open it up in BIDS and go through the different steps.  Do note that in SQL Server 2008, the Wizard is available from the Start/All Programs/Microsoft SQL Server 2008 option as well.

We have other posts also related to SSIS on our blog including one that discusses the differences between DTS and SSIS – you might find them useful for your work.  We had also compiled the resources for the DTS to SSIS migration – you can access them here.  It also has links to other very useful material available on the net.

SQL Server Express Install via third party products

Got a call a few minutes back from a client of ours to whom we provide remote DBA services.  They had installed a third party product on a test server which installs a SQL Server Express Edition for it’s back-end as part of the install.  The install was not able to progress further because of an error about authenticating a SQL Server login account.  They were using the built in sa (system administrator) login

Couple of things that had to be done in order to get them up and running on this test server:

a) When SQL Server Express gets installed, by default, only the Windows Authentication mode is enabled and not “SQL Server and Windows Authentication” mode.

b) Once you fix the above, you would then need to assign a password for the “sa” builtin account.  By default, in SQL Server Express install, it will be NULL.

c) Once that is done, then you need to enable the “sa” login.

Post that, you can proceed further with creation of additional logins and setting up the security and other configuration options.  Let’s quickly cover the three steps from above.

For #a, you can easily change the authentication mode using the steps that we had outlined in an earlier post – here.

For #b, you can connect to the instance and change the password using the sp_password command.  Or alternatively, you can connect through SSMS and change it (Click on Security/Logins –> Right click on “sa” login and select “Properties” and you can set it up there under the General Page).

For #c, You can simply use the Alter command to enable the login or can also do it from SSMS (Click on Security/Logins –> Right click on “sa” login and select “Properties” and you can set it up there under the Status Page).  We had covered disabling an account in a previous post – you can simply replace “Disable” with “Enable” in that post and the same steps will apply.

Resources:

• sp_password syntax of BOL – here.
• ALTER LOGIN command – here.

Prevent connections from certain IP Address(es)

We have talked about Logon Triggers in SQL Server which were first introduced in SQL Server 2005 Service Pack 2.  We had also covered the usage of the EVENTDATA() function in another blog post here.  One of the questions that a reader had asked recently was to have the capability of preventing any connections coming from a specific IP Address and one of the ways of doing that (if we want to do it from the DB Side) in SQL Server is to make use of the logon triggers and the eventdata() function.  We can use the function to get the IP address of the client machine and then prevent the connection via the trigger logic.  Let’s check this out using an example:

As per the BOL, here is how the event data schema looks like for the logon event:

<EVENT_INSTANCE>
<EventType>event_type</EventType>
<PostTime>post_time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login_name</LoginName>
<LoginType>login_type</LoginType>
<SID>sid</SID>
<ClientHost>client_host</ClientHost>
<IsPooled>is_pooled</IsPooled>
</EVENT_INSTANCE>

The one that we are interested in is “ClientHost” which as per the definition from BOL: “Contains the host name of the client from where the connection is made. The value is ‘<local machine>‘ if the client and server name are the same. Otherwise, the value is the IP address of the client.

So, armed with that information now, we have two ways of achieving our aim:

a) Either we allow connections from specific IP addresses only thus blocking away all the rest of the connections OR,

b) We block only specific connections.

Method (a) is more secure since permissions should be given on an as needed basis in this case.  Regardless of the method, we would need to store that list of IP addresses someplace – can be a configuration file on the DB Server filesystem that we can read via a linked server query or better yet, this information can be stored securely in a table in the schema within the database.   In our database, we have a schema called SecuritySchm and all the security related tables are created in it.  So, assume we have a table in it called Allowed_IP_Address and the column IP_Address is the one where we will check the existence of the incoming IP Address of the client.  Besides the allowed IP addresses, do not forget to put in the <local machine> in it (as mentioned in BOL above since it is needed for local connections).

And here is how the trigger will look like:

create trigger AuditLogin_Demo
/* server means instance level*/
on all server
with execute as 'sa'
/* We specify the logon event at this stage
– If the IP address of the client is not a valid one,
– Issue a rollback*/
for logon
as
begin
/*declare the local variable*/
declare @IP nvarchar(30)
/*Now, get the IP Address value*/
SELECT @IP = (SELECT EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(30)'))

/*Check and rollback if not a valid IP value*/
IF NOT EXISTS (Select 1 from SECURITYSCH.Allowed_IP_Address where IP_Address = @IP)
ROLLBACK;

end
go

Now that we have it, let’s try connecting from a machine who’s IP is not listed in our table and we will get an error like this one:

NOTE:   We wanted to mention here that while playing with the code for this post, in the first iteration we ended up creating a bad logon trigger (oops!) and if you end up in the same situation, then if you already have a valid connection open, you can drop the logon trigger by doing:

drop trigger <trigger_name> On All Server;

And if you do not have any current valid connection open, you can connect through DAC (Dedicated Admin Connection) and then execute the command from above.  We had discussed DAC in one of our previous posts here.

Resources:

• BOL Information on EventData() and Logon Trigger – here.