SSAS– Resolving target machine actively refused

I always wanted to have my hands on SSAS, SSIS and SSRS as I always feel short-hand in these areas. Yesterday I decided to get my hands dirty with one of this – SSAS.  Few month back I have created a virtual lab on my 4 year old Dell Laptop with 3 VMs and about total 4 instances of SQL Server – 2 on Host machine and 2 on Guest, one of them is having SSAS installed on one of the named instance.

With lot of excitement I tried connecting to SSAS – it was unsuccessful attempt!! It shows me an error message which says - A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

As we always do – Googled and it lead me to the connect item filed by Aaron Bertrand ( Blog | Twitter ). Reading through the post I came to know that if I try to connect to the named instance SSAS it’s chance that I get this very error. I also have the similar case, I have renamed my machine hence this error showed up with the named instance.

Workaround:  As suggested in the comment section, I have granted NTFS permission on folder program files (x86)\microsoft sql server\90\shared\asconfig for the SQL Browser service account and I am sorted!!

Reference: Microsoft Connect ID 679792 

photo credit: JLaw45 via photopin cc

—Hemantgiri S. Goswami (http://www.sql-server-citation.com)

SQL Server–Security Risk Assessment PMP Way

Recently I had a chance to speak my friend Ashish Sharma who is my colleague too. We were discussing Project Management topics (he recently passed PMP and ACP). Our discussion ended at very interesting topic – RISK Assessment.

Definition: According to PMBOK, Projects can have unforeseen event or activity that can impact the project’s progress, result or an outcome in negative or positive way. Further, A Risk can be assessed using two factors – impact and probability. Determining the quantitative or qualitative value of risk related to a concrete situation and recognized threats is known as Risk Assessment.

So, coming to the point, how do we do Risk Assessment for Database systems, especially SQL Server ?

I myself have never come to situation where I have created or used Risk Assessment Register for SQL Server. I was thinking is there some readily available Risk Assessment Register / document somewhere? Hence, I have decided to research little more on it. I found absolutely nothing on this very topic. However, searching for different term - SQL Server security, and Risk Assessment – none found.

After spending some time researching I have found a good sample document for Risk Assessment Register that we can use, you can download it from here.

Now, this is what we have is Risk Register, how about threats? Sometime back, in 2009 I have wrote an article on SQL Server Security, using that article I have tried to pen down some threats that I can think off at this moment, they are:

Possible Threats Risk Mitigation Virus Attack Install AV on DB Server however do not forget to exclude SQL server files Unauthorised Logins Always use Audit (C2, Windows, SQL or both) failed login attempts and analyze them regularly. Always, rename SA / Admin account and use strong password mechanism for all the servers. If possible always try to use domain authenticated acconts. MiM Attack To metigate MiM attach do following : Isolate database server from Application / IIS Server Configure SQL Server to use non-default port Use Encryption / SSL Use Firewall inbound / outbound rules Root Access To avoid someone getting root access do following: Disable / Rename in-built Admin account Grant least access to Service Account Use strong / random password SQL Injection Do following: Avoid using Dynamic SQL when you can use parameterized query / stored procedure 3) Always validate the value at form level before it's being passed to database server Refer above items Known Configuration Always try to customize default configuration if you can, for example: Run SQL server on non standard port disable / rename SA account Disable xp_cmdshell and other extended procedures

References :

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

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

http://www.brighthubpm.com/risk-management/3247-creating-a-risk-register-a-free-excel-template/

photo credit: kenteegardin via photopin cc

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

MSDB– cleanup is necessary

Recently I have been asked to work on the task which is to reduce the size of MSDB database. The MSDB has grown to 20 Gigs. Well, you would say, what is the big deal with that? 20 gigs is not considered a big for database. Correct, but for MSDB, yes it is.

So, the question is - why the size has grown this much, and what would be the adverse impact it would have on the performance on my system?

I would say, there could me many reasons, like:

• Usually, we do not create user objects inside MSDB, but it is good to check
• Check if there are multiple SSIS/DTS Packages that are large, check with development team if you can store them in file system. Check the link for the list of tables refer links SQL Server 2005, SQL Server 2008 , SQL Server 2008 R2 , and SQL Server 2012
• There isn’t any CleanUp job configured
• There are several hundreds of jobs running i.e. LS aka Log Shipping
• And, so on….

The case:

Client has a server configured with LS for DR purpose. The LS is configured to sync every 15 minutes for several – hundreds of databases which intern inserting lots of data into the historical tables like backup, restore and log_shipping_monitor_history table –all of them had > 75 Lacks of records.

The issue for us was, that the MSDB is configured on the local drive aka where OS and binaries resides – no RAID. Also, the size of the C drive is nearing to it’s capacity – 30 Gigs and it’s quickly filling up. Well, on top of this, the database is in FULL recovery model. The reason that MSDB grows to 20 Gigs are

1. It never had CleanUp job on it,
2. there are hundreds of databases keep inserting records for backup and restore

Adverse impact:

1. Possibly, your backup would take longer than usual as it would take time to write backup and /or restore history
2. You would see timeout error when you try to dig out the reason for backup job failure

What I did was, I have created a maintenance plan for CleanUp which will call sp_delete_backuphistory which will run cleanup for below tables, per client’s request I have configured job to remove all the older data before 60 days.

• backupfile

• backupfilegroup

• backupmediafamily

• backupmediaset

• backupset

• restorefile

• restorefilegroup

• restorehistory

To complete the cleanup activity job successfully, the MSDB and tempdb will need some space to grow which is not possible in our case since we are left with only 10 Gigs of space.  Hence, I have added an addition log file and data file for tempdb and msdb on another drive where we have ample space. Schedule LOG backup for MSDB to run every 10 minutes.

And, then, I’ve invoked the CleanUp job – it took about 3 hours to finish, but it did what it should.

Took FULL backup for MSDB, change the recovery model to simple and shrink it – we were able to shrunk the MSDB successfully and bring it down to 6.5 Gigs.

Constraints and possible options:

1. We had a limited maintenance window to accomplish a task
2. Another maintenance activity has to be performed once we are done
3. Option: We could have script foreign keys and other constraint, drop constraints and keys, and delete the records. I haven’t opt this method because I personally never did this.

Take out from this post:  Do health check, and, schedule a Cleanup task for MSDB to run on regular basis.

 

 

Not so good news for MCM Aspirants

Few days back, late Friday (30th August 2013) MS has made an announcement – that they are cancelling the MCM Certification, not so good news for MCM Aspirants. This announcement has upset many in the SQL Community including me.

I have my own reasons, and belief about not attending any of the certification exam until few month back. In my 14+ years of career not even a single employer of mine have asked me to go and get certified. I can say, I am lucky, but all I know that I have worked hard – a lot. I have tried to learn a lot, from friends, from colleagues, from online web forums, books online, books and MS Evangelists, and still learning. And, that pays me well, I participate in community activities, speaks, blog and have been able to put my thoughts together to publish a book on SQL Server Cluster – this way I am surviving as a professional.

To be honest, during my career I have interviewed a lot many people and many of them are certified but they don’t have exposure and practical troubleshooting skills which was expected; apart from this, there are dumps available that can made it easy to clear an exam. Please, Neither I am not criticizing those who has passed nor I am saying all who haven’t passed aren’t good as professional, but I am just trying to make a point why I haven’t attempted one. With this kind of experience it never interest me to pass the certification.

And, then, MCM was introduced. I have heard a lot about Oracle exams which has lab test as well and MCM is similar kind of exams. Brent Ozar ( Blog | Twitter ) blogged about What is MCM in 2010. I have followed all his posts about MCM and has developed my interest in attending MCM training followed by an exam. Event though the exam and training fees are way too high for me, I have made my mind that I will utilize the money my employer provide each of us for training + some from my pocket to get train and earn this title – but, sadly, it is gone now.  Since MCITP is a pre-requisite for MCM, I have made my mind, attempted and failed. However, I was sure, I was reading more and will clear it in next attempt. But, I am not sure, whether I will be taking an MCITP now.

I will be more than happiest person, if this decision for MCM exam are reversed or some relevant / at par exams are introduced. In our country, it is said that “whatever happened has happened for the good, and whatever that is going to be happen in future will be for good”.  I am hoping that this saying will hold true.

At the end, I would like to say, if you are believer and aspirant of this exam just like me please take some time and vote on the MS Connect site for reconsideration.

Thank you

Hemantgiri S. Goswami (http://www.sql-server-citation.com)

SSRS – Download all RDL files from Report Server in one go.

Introduction

People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple handy script which would help you to download all the required report files at once.

Implementation

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute  “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -

-- Allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- Update the currently configured value for advanced options.

RECONFIGURE

GO

-- Enable xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1

GO

-- Update the currently configured value for xp_cmdshell

RECONFIGURE

GO

-- Disallow further advanced options to be changed.

EXEC sp_configure 'show advanced options', 0

GO

-- Update the currently configured value for advanced options.

RECONFIGURE

GO

Once successfully executed, the below script with the required changes could be executed to download the files -

--Replace NULL with keywords of the ReportManager's Report Path, 

--if reports from any specific path are to be downloaded

DECLARE @FilterReportPath AS VARCHAR(500) = NULL 

--Replace NULL with the keyword matching the Report File Name,

--if any specific reports are to be downloaded

DECLARE @FilterReportName AS VARCHAR(500) = NULL

--Replace this path with the Server Location where you want the

--reports to be downloaded..

DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'

--Used to prepare the dynamic query

DECLARE @TSQL AS NVARCHAR(MAX)

--Reset the OutputPath separator.

SET @OutputPath = REPLACE(@OutputPath,'\','/')

--Simple validation of OutputPath; this can be changed as per ones need.

IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''

BEGIN

  SELECT 'Invalid Output Path'

END

ELSE

BEGIN

   --Prepare the query for download.

   /*

   Please note the following points -

   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.

   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]

   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.

   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. 

      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. 

      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). 

      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. 

      While it is supported, it can cause problems with the conversion to XML, so it is removed.

   */

   SET @TSQL = STUFF((SELECT

                      ';EXEC master..xp_cmdshell ''bcp " ' +

                      ' SELECT ' +

                      ' CONVERT(VARCHAR(MAX), ' +

                      '       CASE ' +

                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+

                      '         ELSE C.Content '+

                      '       END) ' +

                      ' FROM ' +

                      ' [ReportServer].[dbo].[Catalog] CL ' +

                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +

                      ' WHERE ' +

                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''

                    FROM

                      [ReportServer].[dbo].[Catalog] CL

                    WHERE

                      CL.[Type] = 2 --Report

                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')

                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)

                    FOR XML PATH('')), 1,1,'')

  --SELECT @TSQL

  --Execute the Dynamic Query

  EXEC SP_EXECUTESQL @TSQL

END

Conclusion

Hope, this helps & save a lot of your valuable time.

Happy Reporting!

COLLECTING HOST MACHINE INFORMATION USING TSQL

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created a script that does this work for me, however, I still need to connect to the server – but using SSMS/Query Window and execute the script. And then the script will provide me the details I needed.
This inventory should have details like below for cluster and stand alone instances:
Server Name            
OS Name               
OS Edition   
OS Patch Level   
SQL Server IP       
Is Clustered   
Node1_Name        
Node1_IP           
Node2_Name           
Node2_IP           
SQL Server Edition   
SQL Server Patch Level       
Server Time Zone       
SQL Server Version   
SQL Server Platform   
Processor Core       
Physical Memory       
Service Account Name        
Domain               
Looks good ? Below is the version 1 of this script.

/*
IP address portion using : http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000

Created By : Hemantgiri S. Goswami | http://www.sql-server-citation.com
Date  : 24th March 2013
Version  : 1.0

Tested ON:
Windows Server  >> 2003, 2008, 2008 R2 
SQL Server  >> 2000, 2005, 2008, 2008 R2, 2012 

*/
sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @TimeZone   NVARCHAR(100)
  ,@ProductVersion SYSNAME
  ,@PlatForm  SYSNAME
  ,@Windows_Version SYSNAME
  ,@Processors  SYSNAME
  ,@PhysicalMemory SYSNAME
  ,@ServiceAccount SYSNAME
  ,@IPAddress  SYSNAME
  ,@DOMAIN  SYSNAME
  ,@MachineType  SYSNAME
  ,@SQLServerIP  VARCHAR(255)
  ,@CMD   VARCHAR(100)
  ,@Node1   VARCHAR(100)
  ,@Node2   VARCHAR(100)
  ,@Node1IP  VARCHAR(100)
  ,@Node2IP  VARCHAR(100)
  ,@OSEdition  VARCHAR(100)
  ,@OSVersion  VARCHAR(100)
  ,@OSName  VARCHAR(100)
  ,@OSPatchLevel  VARCHAR(100)
   
CREATE TABLE #TempTable
 (
  [Index] VARCHAR(2000),
  [Name] VARCHAR(2000),
  [Internal_Value] VARCHAR(2000),
  [Character_Value] VARCHAR(2000)
 ) ;

INSERT INTO #TempTable
EXEC xp_msver;

-- Replace @Value_Name to N'TimeZoneKeyName' when running on Windows 2008 
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
       @value_name   = N'StandardName', 
       @value        = @TimeZone output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
       @value_name   = N'ObjectName', 
       @value        = @ServiceAccount output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
       @value_name   = N'ProductType', 
       @value        = @MachineType output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters',
       @value_name   = N'Domain', 
       @value        = @DOMAIN output
       
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'CLUSTER\NODES\1',
       @value_name   = N'NodeName', 
       @value        = @Node1 output
       
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'CLUSTER\NODES\2',
       @value_name   = N'NodeName', 
       @value        = @Node2 output              

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
       @value_name   = N'ProductName', 
       @value        = @OSName output              
       
create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100)) 
insert into #OSEdition 
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
       @value_name   = N'ProductSuite'  
SET @OSEdition = (SELECT TOP 1 OSedition  FROM #OsEdition)       
               
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
       @value_name   = N'CSDVersion', 
       @value        = @OSPatchLevel output  

set @cmd = 'ping ' + @Node1 
create table #Node1IP (grabfield varchar(255)) 
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd 


set @cmd = 'ping ' + @Node2
create table #Node2IP (grabfield varchar(255)) 
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd 
 
set @cmd = 'ping ' + @@servername
create table #SQLServerIP (grabfield varchar(255)) 
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd 

SET  @SQLServerIP=( 
      SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
      from #SQLServerIP  where left(grabfield,7) = 'Pinging' 
    )
SET  @Node1IP    =(
       SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
       from #Node1IP  where left(grabfield,7) = 'Pinging' 
     )
       
SET  @Node2IP  =( 
       SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
       from #Node2IP  where left(grabfield,7) = 'Pinging' 
    )
       
SET  @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2)
SET  @Platform  = (SELECT Character_Value from #TempTable where [INDEX]=4)
SET  @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15)
SET  @Processors  = (SELECT Character_Value from #TempTable where [INDEX]=16)
SET  @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19)


SELECT 
  ServerName   = @@SERVERNAME  
 ,OSName    = @OSName
 ,OSEdition   = @OSEdition
 ,OSPatchLevel          = @OSPatchLevel
 ,SQLServerIP          = @SQLServerIP
 ,IsClustered          = SERVERPROPERTY('IsClustered')
 ,Node1_Name   = @Node1
 ,Node1_IP   = @Node1IP 
 ,Node2_Name   = @Node2
 ,Node2_IP   = @Node2IP 
 ,SQLServerEdition         = SERVERPROPERTY('Edition')
 ,SQLServerLevel          = SERVERPROPERTY('ProductLevel')  
 ,ServerTimeZone          = @TimeZone 
 ,SQLServerVersion         = @ProductVersion
 ,SQLServerPlatform         = @PlatForm
 ,ProcessorCore          = @Processors
 ,PhysicalMemory          = @PhysicalMemory 
 ,ServiceAccountName         = @ServiceAccount 
 ,WKS_Server   = @MachineType
 ,Domain    = @DOMAIN
 
GO 
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition 
GO


sp_configure 'xp_cmdshell',0
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO

Please do post back your feed back for this script, I will try my best to update and post back new version for this script.

- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

SQL Server – Generate Calendar using TSQL

Introduction

Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011. The idea was to show a Calendar for the selected month and each cell of the calendar should display the scheduled events of that day.

Showing the events in the required format in each cell was not a big issue. The main challenge was to generate a dynamic grid of Calendar. Luckily, the CRM was deployed on-premises and I got a chance to use TSQL to generate the Calendar.

Implementation

Below is the TSQL which I came up with to generate the Calendar -

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.

DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.

--Find and set the Start & End Date of the said Month-Year

DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'

DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))

;WITH Dates AS (

  SELECT 

    @StartDate Dt

  UNION ALL

  SELECT 

    DATEADD(DAY,1,Dt) 

  FROM 

    Dates 

  WHERE 

    DATEADD(DAY,1,Dt) <= @EndDate

),Details AS (

  SELECT 

    DAY(Dt) CDay,

    DATEPART(WK,Dt) CWeek,

    MONTH(Dt) CMonth,

    YEAR(Dt) CYear,

    DATENAME(WEEKDAY,Dt) DOW,

    Dt 

  FROM 

    Dates

)

--Selecting the Final Calendar

SELECT

  Sunday,

  Monday,

  Tuesday,

  Wednesday,

  Thursday,

  Friday,

  Saturday

FROM

  (SELECT CWeek,DOW,CDay FROM Details) D

PIVOT

(

  MIN(CDay)

  FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)

) AS PVT

ORDER BY

  CWeek

Output:

Hope, this will help!