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)

Read More

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 )

Read More

SQL Server # Moving MASTER database in cluster environment

Few months back I have wrote post about moving MASTER and MSDB database to new location in stand alone machine.

In recent past we had a situation where customer asked us to move MASTER database to new location, below are the steps I have taken:

1.     Connect to the Server
2.     Open Configuration Manager -> SQL Server Service
3.     Right Click and say Properties
4.     Click on the Start-up Parameter
5.     Remove start-up parameter (the highlighted one)

	 -dOLDLocation\master.mdf
	 -eOLDLocation\ErrorLog
	 -lOLDLocation\mastlog.ldf

 

      6.     Add new start-up parameters with new values (per your configuration)

   

	 -dNewLocation\master.mdf
	 -eNewLocation\ErrorLog
	 -lNewLocation\mastlog.ldf

      7.    Check and confirm which node is active

      8.    PAUSE current PASSIVE Node  to avoid fail-over


      9.    Take SQL Server resources offline, i.e. SQL Server, SQL Agent, MSDTC, SQLCLUSTER Name (do not take SQL Cluster IP Offline)


    10.    Copy MASTER.MDF and MASTLOG.LDF to NEW Location ( S:\SQLDATA, yours could be different)


    11.    Log into Cluster Administrator and bring SQL Server Resources online


    12.    Resume current PASSIVE Node

 

That's all, you should be able to see your master database on new location now!!!

 

-- Regards,

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

Cross posting: http://www.pythian.com/news/35829/moving-master-database-to-new-location-in-sql-cluster/

Read More

Moving MASTER Database

In my previous post we see how to move MSDB database, today we will see how to move or relocate MASTER database. While moving MASTER database we’ll have to consider few other things like changing start-up parameter for SQL Server Service. I will also mention those stops here for better understanding. Let’s do it step-by-step.

Step 1: Query sys view and note down the existing location for MASTER database

USE MASTER 
GO 
SELECT 
NAME, 
PHYSICAL_NAME AS 'PhysicalFilePath', 
STATE_DESC AS 'DB Status' 
FROM SYS.MASTER_FILES 
WHERE NAME LIKE 'Mast%'

Step 2: Run alter database command and change the location for database files

ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MASTER, 
FILENAME= 'C:\SQLDB\Demo\Master.mdf' 
) 
GO 
ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MastLog, 
FILENAME= 'C:\SQLDB\Demo\MastLog.mdf' 
) 
GO 

Step 3: Stop SQL Server Service and move database files to new location

Step 4: Restart SQL Server Service, surprised ?

 

Step 5: This was expected, let’s see what errorlog has to say about this!

Refer the highlighted section, SQL Server service could not find the files. This is because we have moved that files to new location.

Step 6: Okay, so let’s go and change the start-up parameter. We can do this using Configuration manager.

Step 7: Right click on SQL Server service –> Properties –> Start-up Parameter

Step 8: Make correction in path for Master.mdf and Master.ldf

Step 9: Start SQL Server service, this time it will start.

You are done!!

Note: This is to be done when we have to do relocate databases to new drive, or file organization, or some error which force us to do this.

-- Hemantgiri S. Goswami

Read More

Moving MSDB to new location

In recent past we have a situation where in we required to move MSDB, Model and Master databases to new location, the reason being faulty drive. While moving system databases to new location we need to be extra cautious. Let’s see the process step-by-step.

Step 1: Let’s query sys view and note down the location for database files

SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

Step 2: Run alter database and specify new location for database

SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.ldf'
)
GO

Step 3: Stop SQL Server service

Step 4: Once SQL Server service is stopped move MSDB database to new location
Step 5: Now, start SQL Server service. This time it will use the new path that we have configured in Step 2.
Note: If you have enabled and configure Database Mail, please make sure it works after you moved MSDB to new location.

Tomorrow, I will post about how to relocate Master database.

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

Read More

When DBCC INPUTBUFFER disappoints

Today we have an alert from one of the client server about blocking, I have immediately start looking at it using below statement

 
SELECT * FROM 
MASTER..SYSPROCESSES
WHERE BLOCKED != 0 

Yes, I’ve found the culprit SPID that is occupying more resources, using DBCC INPUTBUFFER(SPID). so, instead of seeing what exactly this SPID is doing I have received SP_EXECUTESQL, surprised!!! No, it’s bound to happen when SPID is running dynamic TSQL (using SP_EXECUTESQL) and/or cursor. But, yes, at the same time I would like to know what it is running behind the scene. There are two options that came to my mind

• Using Profiler
• using function – ::fn_get_sql(@SQLHandle) 

I preferred to use 2nd option as this is one time effort (at least as of now), and, it would be very quick. So, here is what I have used

 

-- Variable that will store the SQLHandle
DECLARE @SQLHandle BINARY(20)

-- Variable that will pass on the culprit SPID
DECLARE @SPID INT

-- value for culprit SPID
SET @SPID = 52

-- this will give you the SQLHandle for the culprit SPID
SELECT @SQLHandle = SQL_HANDLE 
FROM MASTER..SYSPROCESSES 
WHERE SPID = @SPID  

-- this statement will give you the SQL Statement for culprit SPID
SELECT [TEXT] FROM ::FN_GET_SQL(@SQLHandle)

You must be thinking why I have used this function instead sys.dm_exec_sql_text? Any guess??
Yes, you are right, customer is still using SQL server 2000 {Winking smile}
BTW, if you happened to come across something relating but on SQL Server 2005 or SQL Server 2008 and greater I have a reference script for you

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron_Bertrand.
 
--Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

Rebuild System Databases

Yesterday night one of FB Friend have ping me, he has issue with his local SQL Server instance. Our conversation goes like:
FB Friend: Hi
I : Hi
FB Friend: I need your help
FB Friend : My SQL Server is not starting...
I : What is an error you are getting?
FB Friend : Just nothing
I : Ok, check the SQL Server Error Log and EventViewer
I : you can find errologs at C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\Log
I : open it with notepad ....
FB Friend: Ok
FB Friend: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
I : Wow, seems like your master database is corrupt, you will need to run repair or you may want to rebuild master database.
I : Is this a new installation ?
FB Friend : Yes, this is new installation, and, I don't want to re-run the installation again, what is my option ?
I : you will need to rebuild master or restore it
FB Friend: How do I do it?
I: Here are your steps

Rebuild Master:

Step 1: Insert your setup media CD or folder where you have dumped SQL Server Installation
Step 2: Open command prompt
Step 3: Go to location (wherever you have setup, Step 1)
Step 4: setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=MyAdmin /SAPWD=somePwd

Step 5: this will pop up new command window and then disappears

Step 6: you will have to refer logs so as to see whether it was successful completed or exit with error

This is it, you system database has been rebuild successfully!!
Restoring Master Database - An alternative method:
Step 1: Take backup of master database, in your case, I assume, you already have your master database backed up.

Step 2: Stop SQL Server Services * you will need Administrator permission for this operation

Step 3: Start SQL Server using -C and -M parameter; this will start SQL Server in single user mode which is required to restore Master database

Step 4: Restore database using SQLCMD

This is it, you have successfully restore your master database!!!

Is it this simple? No, wait, there are few things we need to be careful with....

The above example have save you because below conditions are true:

1. You have back up of your master database, or
2. This server is fresh installation or
3. there are no user databases or
4. this server is not production box

If the above conditions were not true, you will have to perform below steps:

1. Restore / Rebuild system databases
2. Recreate all the Login that you have on your crashed server - backing up master db helps you here
3. you will have to recreate all the jobs and SSIS/DTS Packages that you have - backing up MSDB database will save you
4. similarly distribution database backup will help you if you have replication configured

Conclusion :  I wish that you have schedule maintenance plan / job to take full backup of your system databases i.e. Master, MSDB, and Distribution(if you have configured replication).
Disclaimer : The example used here is for illustration purpose only.

Read More

Roles and Responsibilty of SQL Server DBA

• What is a role of a DBA in an organization?
• What are the daily activities of a DBA?
• What shall I check on regular basis?
• What all processes shall I automate ?
• What are the precautions, I have to take additionally?

These are the common question being asked in-common on may forums, here is what my listing is

• Design and implement Disaster Recovery

• Design, development, upgrade and Migration of Database Server.

• Providing support and handling most critical situations in vast variety of database systems.

• Database Optimization / Query Performance Tuning.

• Design and implementation Automated Database & Application fail over server setup.

• Capacity Planning, Change Management and Admin. Documentation.

• Review of existing design and specifications of the system.

• Design and documentation of operational specifications of the system.

• Monitoring of the development process, in order to confirm optimum performance of the system.

• Preparation of standards and follow up rules for the back – end system, to ensure a fully secured and a robust system.

• Design and implementation of the logical & physical structure of the database.

• Monitoring of performance of database servers and providing tuning measures.

• Performing database / application wide query tuning operations.

Try automate every process that needs to be performed on daily basis and/or requires manual intervention.

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

Read More

What’s new in SQL Server RC0 setup

1. Datacentre Edition is no longer available as a MS SQL Server 2012 RC0 candidate, Read More
2. MS SQL Server 2012 RC0 includes new edition – SQL Server Business Intelligence,  Read More
3. Service Pack 1 is the minimum requirement for Windows 7 and Windows Server 2008 R2 operating systems, Read more
4. Data Quality Services can be installed using SQL Server 2012 RC0 Setup, Read more
5. Product update is the new feature available with MS SQL Server 2012 RC0, this will integrate latest update with main product including MS Update, WSUS, local folder or UNC. This feature is an extension to Slipstream Functionality which was available in SQL Server 2008 PCU1. We can use command prompt or configuration file to override default values to find updates by specifying values for UpdateSource parameter, Read more
6. We can now install MS SQL Server on Windows Server 2008 R2 Core SP1, not all features are supported though, Read more
7. SQL Server Data Tools (formerly, BIDS) is now part of setup, with this we can carry out all database design work, and can build solutions for SSAS, SSRS and SSIS. Read more
8. Now, Support for SQL Server multi-subnet clustering included, Read more 
9. Databases (systems and users) can now be on UNC path, we may need to make sure that proper NTFS and File Share permissions are assigned, Read more
10. Local disk can be used for Tempdb in cluster, Read more
11. Built-in Administrator and Local system account will not be part of sysadmin role
12. Itanium editions are no longer supported

Read More

Maintenance Plan mystery

Last week I had interesting (or I can say weird ? ) case of Maintenance Plan. One of the server that we monitor has reported job failure – this was a part of maintenance plan. Looking at the job history and log file I came to know that this job was failing because of some database which wasn’t available – this database was dropped few days back, and, the maintenance plan has it’s reference stored somewhere. I have search around system tables etc. but nothing unusual or nothing helpful was available that indicates the source of this database.
Finally, I have edited the maintenance plan which resolved this case, here is what I did:

1. Alter the maintenance plan "some maintenance plan"
2. note down the databases name
3. altered it and selected all the system databases
4. saved the maintenance plan changes and exit
5. re-open the maintenance plan
6. re-selected those databases (from step 2)
7. saved the maintenance plan changes and exit
8. re-invoked the job
9. and, the job completed successfully

And, the mystery persist, can you guys suggest me if I miss something to refer/research ?

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

Read More

Using NOLOCK hint

Use NOLOCK hint to avoid block - this is what I have often heard/see in many forums I participate, during local user group events and meeting. I have always advised that its not that good idea to use hints, as it may cause data corruption and blocking. And, anyways, there are many things that you can do to avoid blocking, like:

1. use sp for everything (almost)  
2. try to avoid using cursor
3. transaction shouldn't be too big etc
4. and, use READPAST hint , I will still say, use this only when you don't have choice

But these all comes from the experience from the field and haven't anything concrete to quote as reference point until last night, I was googling something and this blog article from  Dave, on MSDN Blog showed up.  Now, I can quote SQL Server NOLOCK Hint and Other poor ideas as reference to my peers,and friends at local user group, and I am referencing it here for you to read and make a note.  


I hope this helps.


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

Read More

Download SQL Server 2008 SP3 CU2

MS has recently released SQL Server 2008 Service Pack 3 Cumulative Update 2 Released‏ which has fixes reported after SQL Server 2008 SP 3, this build version is  10.00.5768.00.
Below are the bugs that have been fixed in this CU:

1. FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
2. FIX: CLR stored procedure returns NULL after it is recompiled in SQL Server 2008 R2
3. FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008

There are few more things we have to keep in consideration while applying this CU:
Pre-install:
1) You should be running on SQL Server SP3, if you haven't installed SP3 for SQL Server here is the link from where you can obtain SP3, Link => http://support.microsoft.com/kb/968382
Post-install:
* It is advisable to reboot the system once CU is applied
Please make sure you test the CU in Dev/QA/UAT environment before you apply it in the PROD environment.
Here is the link from where you can download the SQL Server 2008 SP3 CU2, link => http://support.microsoft.com/kb/2633143/en-us

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

Read More

Resource for new SQL Server DBA and beginners in SQL Server

"Give me some good resource that I can refer as new dba or beginner or new learner " this is the question asked by beginners and new learners in various forums I participate, thus I decide to post a blog referring the same, they are:

Here are some good resources for SQL Server beginners and new DBA(s)
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
http://www.academictutorials.com/sql/
http://www.developer.com/db/article.php/718491

SQL Server Administration and best practice
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
http://vyaskn.tripod.com/sql_server_2000_fast_answers.htm
http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm
http://www.sql-server-performance.com/self_taught_dba.asp

Also suggest to keep on reading and posting in technical websites/ web forums like
http://www.sql-server-performance.com/community/forum
http://forum.sqlknowledge.com/
http://www.sqlserver-qa.net/community/forum/
http://forums.microsoft.com/
http://www.experts-exchange.com/
http://www.sqljunkies.com/

Read More