SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Category Archives: SQL Server

How to validate if SQL FILESTREAM document was modified outside SQL

Leave a comment Posted by on March 25, 2012

Recently, I came across a question about validating if the SQL FileStream document has been modified outside of SQL or not.

My immediate reaction was one of shock. If a FileStream document has been modified outside of SQL, then we have bigger problems at hand. We have serious issues with SQL Server security and data consistency is at risk.

FileStream storage, is not supposed to be modified outside of SQL. Though we can have Win32 streaming of these documents(documents created on the File System), but these has to be done in the context of a SQL Transaction and only within the context of a SQL Transaction. FileStream was not created for external modifications.

If the documents are modified outside of SQL, then the database is risk. For example, if we by mistake delete the files from the file system, SQL Would treat it as a database corruption and would throw errors during the next Check DB run.

Msg 7904, Level 16, State 2, Line 1
Table error: Cannot find the FILESTREAM file "00000020-00000146-000a" for column ID 2 (column directory ID d35bf83a-99c0-4a7d-ac24-e9f7cf15a54b) in object ID 101575400, index ID 1, partition ID 72057594038910976, page ID (1:170), slot ID 0.
There are 4 rows in 1 pages for object "DocumentStore".
CHECKDB found 0 allocation errors and 3 consistency errors in table ‘DocumentStore’ (object ID 101575400).

But the question is, how do we figure out if a File Stream document was modified outside of SQL or not.

Short answer is there is NO way to do it. There is no inbuilt function or mechanism to figure that. The simple reason why this cannot be done is because the path to store the File Stream data is not exposed for Direct Access. Also the file names which are visible in the File System are not the same which SQL Server maintains. They are different and there is no way to correlate the SQL Names with the File system Name.

As a SQL Server DBA, you need to make sure that the File Stream files are not get modified outside of SQL.

FILESTREAM, SQL Engine, SQL Server , ,

Creating a Report Subscription using .Net

Leave a comment Posted by on March 20, 2012

In order to create a report subscription, we can use any of the following Reporting Services endpoint.

  • ReportService2005 -  If Reporting Services (SSRS 2008 or earlier) is running in Native Mode
  • ReportService2006 – If Reporting Services (SSRS 2008 or earlier) is running in SharePoint Integration Mode.
  • ReportService2010 – For Reporting Services 2008 R2.

Each of these endpoint, expose a Method for the creation of a Report Subscription. In this example, I am creating a Report Subscription for windows File Share. Same techniques could be used for creating a email based subscription.

Steps to create a Report subscription

Declare an object of the ReportService2005 (or any of the above mentioned endpoints) and bind the object to the Report Server.

   1: ReportingService2005 rs = new ReportingService2005();

   2: //            rs.GetExtensionSettings("

   3:             rs.Url = "http://localhost/reportserver/ReportService2005.asmx";

   4:             rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

 

Provide the path for the report, for which the subscription needs to be made and define a schedule for the Subscription. In this example, I created a Subscription schedule to run at 5PM every Thursday.

   1:         string report =  "/PowerManagementSolution/MachineTypeInformation";

   2:             string desc = "Created using .Net";

   3:             string eventType = "TimedSubscription";

   4:             string scheduleXml =

   5:                @"<ScheduleDefinition>" +

   6:                 "   <StartDateTime>2012-02-16 T17:00:00" +

   7:                 "   </StartDateTime>" +

   8:                 "   <WeeklyRecurrence>" +

   9:                 "      <WeeksInterval>1</WeeksInterval>" +

  10:                 "      <DaysOfWeek>" +

  11:                 "         <Thursday>True</Thursday>" +

  12:                 "      </DaysOfWeek>" +

  13:                 "   </WeeklyRecurrence>" +

  14:                 "</ScheduleDefinition>";

Next, I defined the parameters for the Subscription. For example, the Format, the location where the rendered report needs to be created, using a ParameterValue collection.

   1:     ParameterValue[] extensionParams = new ParameterValue[7];

   2: // file name for Report rendering.

   3:             extensionParams[0] = new ParameterValue();

   4:             extensionParams[0].Name = "FILENAME";

   5:             extensionParams[0].Value = "Sales Order Detail";

   6:  

   7:             extensionParams[1] = new ParameterValue();

   8:             extensionParams[1].Name = "FILEEXTN";

   9:             extensionParams[1].Value = "True";

  10:  

  11: // File Share and format for Rendering Report

  12:             extensionParams[2] = new ParameterValue();

  13:             extensionParams[2].Name = "PATH";

  14:             extensionParams[2].Value = @"\\myfileshare\MyReports";

  15:             

  16:             extensionParams[3] = new ParameterValue();

  17:             extensionParams[3].Name = "RENDER_FORMAT";

  18:             extensionParams[3].Value = "EXCEL";

  19:  

  20: // The login account, which has permissions to write to the file share.

  21:             extensionParams[4] = new ParameterValue();

  22:             extensionParams[4].Name = "USERNAME";

  23:             extensionParams[4].Value = @"<NetworkLogin>";

  24:  

  25:             extensionParams[5] = new ParameterValue();

  26:             extensionParams[5].Name = "PASSWORD";

  27:             extensionParams[5].Value = "<NetworkPassowrd>";

  28:  

  29: // Write Mode for file creation.

  30:             extensionParams[6] = new ParameterValue();

  31:             extensionParams[6].Name = "WRITEMODE";

  32:             extensionParams[6].Value = "None";

once the parameters and the Report Values have been specified, we need to call the CreateSubscription method for creating the subscription.

   1: /// Setting the Report Server Subscription Details. This is a FileShare based Subscription.

   2:  

   3:             string matchData = Convert.ToString(scheduleXml);

   4:             ExtensionSettings extSettings = new ExtensionSettings();

   5:             extSettings.ParameterValues = extensionParams;

   6:             extSettings.Extension = "Report Server FileShare";

   7:  

   8:             try

   9:             {

  10:                 // call the Create Subscription method in the Reporting Services endpoint.

  11:                 rs.CreateSubscription(report, extSettings, desc,

  12:                     eventType, matchData, null);

  13:                 MessageBox.Show("Susbcription Created");

  14:                 Application.Exit();

  15:             }

  16:  

  17:             catch (SoapException E)

  18:             {

  19:                 Console.WriteLine(MessageBox.Show(E.Message));

  20:             }

This code would create the Subscription on the mentioned Report Server instance.

Reporting Services, SQL Server

Render and Email a Reporting Services Report using .Net

2 Comments Posted by on March 19, 2012

In an earlier post, I had described a customer scenario, where they would want to execute and render a report from a .Net application, and then send the rendered report as an email attachment.

In the previous post, I had talked about how to generate a Tree View of the Report Server Items. In this post, I would be talking mainly about the how to execute a report and render it in a required format. And then send the Report as an email attachment.

The Post, assumes the following

  1. The report which needs to be executed is selected on the Tree View and the report path is passed to the function.
  2. The Report runs with default parameters.
  3. The SMTP mail server is preconfigured.

In order to execute a Report, we need to use the ReportExecutionService() Class, defined in the ReportExecution2005 endpoint for Reporting Services.

I created a new class ExecuteAndRender for this functionality.

The ExecuteAndRender Class, defines and object for the ReportExecutionService() class, in order to execute the methods exposed by Reporting Services.

   1: public ExecuteAndRender()

   2:         {

   3:              rs = new ReportExecutionService();

   4:              rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

   5:              rs.Url = "http://localhost/reportserver/ReportExecution2005.asmx";

   6:         }

Next I created a function RenderReport(string ReportName), this function performance the following tasks

  1. Define the Report Rendering and execution settings.
  2. Calls the ReportExecutionService.LoadReport() to load the report definition.
  3. Defines the report parameters. In this case, since all default values would be used, we do not explicitly define the parameters.
  4. Calls the ReportExecutionService.Render() method to render the report in the desired format.
  5. One the report have been rendered, we create a PDF file for the report.
  6. Then call the AttachAndSendEmail() to send the email. 
   1: public void RenderReport (string ReportName)

   2:         {

   3:             byte[] result = null;

   4:             //string reportPath = "/ReportFolder2";

   5:             string format = "PDF";

   6:             string historyID = null;

   7:             string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

   8:  

   9:             //DataSourceCredentials[] credentials = null;

  10:             //string showHideToggle = null;

  11:             string encoding;

  12:             string mimeType;

  13:             string extension;

  14:             Warning[] warnings = null;

  15:             

  16:             //ParameterValue[] reportHistoryParameters = null;

  17:             string[] streamIDs = null;

  18:  

  19:             ExecutionInfo execInfo = new ExecutionInfo();

  20:             ExecutionHeader execHeader = new ExecutionHeader();

  21:  

  22:             rs.ExecutionHeaderValue = execHeader;

  23:             execInfo = rs.LoadReport(ReportName, historyID);

  24:  

  25:             //ReportParameter[] param = execInfo.Parameters;

  26:  

  27:             // Prepare report parameter. Currently the report executes with Default paramerts.

  28:             // Here we retrieve the 

  29:             // But if required the Report Paramaeters can be added.

  30:             

  31:             ParameterValue[] parameters = new ParameterValue[execInfo.Parameters.Length];

  32:  

  33:             rs.SetExecutionParameters(parameters,"en-us");

  34:             String SessionId = rs.ExecutionHeaderValue.ExecutionID;

  35:  

  36:             try

  37:             {

  38:                 result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

  39:                 execInfo = rs.GetExecutionInfo();

  40:             }

  41:             catch (SoapException e)

  42:             {

  43:                 throw e;

  44:             }

  45:             // Write the contents of the report to an PDF file.

  46:             try

  47:             {

  48:                 FileStream stream = File.Create("report.pdf",result.Length);

  49:                 stream.Write(result, 0, result.Length);

  50:                 stream.Close();

  51:                 ///Now Send the email

  52:                 AttachAndSendEmail("report.pdf");

  53:             }

  54:             catch (Exception e)

  55:             {

  56:                 throw e;

  57:             }

  58:         }

Next, we have the AttachAndSendEmail() function to connect to the SMPT server and send the email. This function achieves the following tasks

  1. Create an SMTP client to connect to the SMTP server.
  2. Create the From and To Addresses for the email.
  3. Creates the MailMessage with the attachment.
  4. And Sends the email. 
   1: public void AttachAndSendEmail(string ReportFileName)

   2:         {

   3:  

   4:             SmtpClient client = new SmtpClient("Address of the SMTP Server", 25);

   5:             client.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

   6:  

   7:             MailAddress from = new MailAddress("<from Address here>","<DisplayNamehere>",

   8:                             System.Text.Encoding.UTF8);

   9:  

  10:             MailAddress to = new MailAddress("<To Address here>","<DisplayNamehere>",

  11:                 System.Text.Encoding.UTF8);

  12:  

  13:  

  14:             MailMessage msg = new MailMessage(from,to);

  15:             msg.Subject = "Report Attached " + DateTime.Now.ToString();

  16:             msg.Body = "Report From Report Server";

  17:             msg.BodyEncoding = System.Text.Encoding.UTF8;

  18:             msg.Attachments.Add(new Attachment(ReportFileName));

  19:  

  20:             try

  21:             {

  22:                 client.Send(msg);

  23:             }

  24:             catch (System.Net.Mail.SmtpException e)

  25:             { throw e; }

  26:         }

 

This code sends the messages in an synchronous manner. In case if required, SMTPClient.SendAsync method can also be used.

Reporting Services, SQL Server , , ,

Target_Recovery_Time – Indirect Checkpoint SQL Server 2012

2 Comments Posted by on March 19, 2012

SQL Server 2012, introduces a new Database level option “Target_Recovery_Time” to better control and predict the time it takes during Recovery for the Database. The reason for this option was simple, the “Recovery Interval” option in sp_configure was really not reliable enough.

Before we talk about the new option, lets talk a bit about the older option.

With the Recovery interval option (set in minutes, default is 0), SQL Server would schedule the Database checkpoints in such a way that the time taken to recover the database is similar to what is specified in the Recovery Interval setting. What happens in the background is that SQL Server tries to schedule the checkpoint based on the number of DML operations that are taking place, but fails to take into consideration the number of DB Pages being modified by these operations.

As shown in the Video, SQL Server first calculates the Checkpoint interval for the Database number of DML operations and the value of “Recovery Interval” option.

When the first transaction completes (which modifies about 160 pages) and the subsequent fires, we flush all these 160 pages to the disk an move the Min_Recovery_LSN forward to the checkpoint LSN. This Min Recovery LSN is the what is used during the Database recovery, to Predict what operations are to be repeated in the Redo Phase of Recovery. During the Second Transaction (which is modifying close to 12K pages), if there were no problems, during checkpoint we would have flushed these 12K pages to the disk, which would result in a spike in the Disk Write Activity. Assuming that there was a crash, just before the Checkpoint happened (the transaction did commit), because of the large amount of pages which needs to be loaded and modified, the time to recover the database might vary and would be unpredictable.

The current Checkpoint algorithm induces the following problems, which are indeed the reason why the new Database was introduced.

  • Massive spikes in the Disk Write activity, during checkpoint.
  • Unpredictable recovery times for the Database.

The idea was to smooth out the disk write activity, so that we no longer see the massive spikes and to also have better predictability of the recovery time of a database.

The new database option can be set using the following Command. 

   1: ALTER DATABASE [CheckpointDB] SET TARGET_RECOVERY_TIME = 10 SECONDS 

   2: GO

or it can be set using the Database Properties window in SSMS.

image

When the new database option is set, SQL Server performs two things,

  • First it  introduces a background task to continuously flush the Dirty buffers from the disk. What SQL actually does is it calculates a min number of Dirty buffers it would keep. Every time this threshold is exceeded, the background process (called the background Recovery writer) kicks in and flushes the dirty buffers to the disk.
  • Continously move the Min_Recovery_LSN forwards to the LSN, which was most recently flushed. Since the Min_Recovery LSN is continuously moving forward, in case of a crash happens, the amount of work done during the Recovery would be small.

The video, below illustrates how this is happens.

With the new setting, the writes are no longer in spikes (smoothens out because of the background recovery writer) and the amount of work which needs to be done during the Recovery Phase is also reduced, providing more accurate estimations of the Recovery Time for the Database.

Word of Caution

If you set the Target_Recovery_Time to a very small number (like 1 sec) on an OLTP environment, SQL Server might just throttle the disk with the massive amount of Write operations which needs to be done as part of the Background Recovery Writes. Set this option, only after carefully examining the workload pattern and your SLA commitments. Most often the older “Recovery Interval” option from sp_configure works well.

I would like to thank my friend Parikshit Savjani, for his help with the Videos.

Performance, SQL Engine, SQL Server ,

What’ New SQL Server 2012: Reporting Services Office based Renderers

Leave a comment Posted by on March 18, 2012

With SQL Server 2012, Reporting Services, both Excel and Word rendering now creates a Office 2007-2010 compatible files. These files are also compatible with Excel/Word 2003.

Excel based files have the extension .xlsx, while the word based files have .docx extensions.

In addition, there are several other enhancements with the excel rendering

  1. Max row limitation of excel 2003 (65K) rows is no longer applicable. Now the max number of rows per worksheet can be over 1 million.
  2. Maximum columns per worksheet is 16,384.
  3. Number of colors allowed in a worksheet is approximately 16 million (24-bit color).
  4. ZIP compression provides smaller files sizes.

With word rendering, the .docx files are much smaller than the previously created .doc files.

Reporting Services, SQL Server , , , , ,

← Older posts

Newer posts →