SQL DBA learning curve

how to Slipstream SP2 or SP1 for SQL 2008 R2

June 4, 2013 GS Sohal 1 comment

Hi all,

I was looking for ways to do a slipstream installation for SQL server 2008 R2 SP2 .. I referred to this for some important info .. and below are steps I

followed to achieve the same .. A great THANKS to Peter Saddow (Microsoft) for his blog …

Slipstream allows you to do service pack installation along with the initial installation of SQL server ..

this helps save downtime and avoid any manual installation errors …

Steps as below ..

1> copy your original SQL server setup to a folder named SQL2008R2

2> download the service pack from this location . make sure you download them all 3 (x86,x64 and ia64)

irrespective of the fact you will eventually be using one … but to make sure your SP are complete in every sense ..

3> you need to create a FOLDER named SP2 (or whatever you want to name it ) with in the folder named SQL2008R2

(that is the location where your original installation is placed)

4> extract the 3 downloaded EXE into this newly created FOLDER named SP2 (below are the command)

SQLServer2008R2SP2-KB2630458-IA64-ENU.exe /x:T:\SQL2008R2\SP2

SQLServer2008R2SP2-KB2630458-x64-ENU.exe /x:T:\SQL2008R2\SP2

SQLServer2008R2SP2-KB2630458-x86-ENU.exe /x:T:\SQL2008R2\SP2

remember .. T:\SQL2008R2 contains the original SQL server setup and we created a folder named SP2 within T:\SQL2008R2

5> Robocopy the SETUP.exe file from newly created folder SP2 into original setup .. that we want to replace the setup.exe file of original SQL server setup with SP2 setup.exe

T:\SQL2008R2_SP2>robocopy T:\SQL2008R2\SP2 T:\SQL2008R2 setup.exe

6> Copy all files not the folders, except the Microsoft.SQL.Chainer.PackageData.dll, in T:\SQL2008R2\SP2\<architecture> to T:\SQL2008R2\<architecture> to update the original files. Here is the Robocop command

T:\SQL2008R2_SP2>robocopy T:\SQL2008R2\SP2\x64 T:\SQL2008R2\x64 /XF Microsoft.SQL.Chainer.PackageData.dll

T:\SQL2008R2_SP2>robocopy T:\SQL2008R2\SP2\x86 T:\SQL2008R2\x86 /XF Microsoft.SQL.Chainer.PackageData.dll

T:\SQL2008R2_SP2>robocopy T:\SQL2008R2\SP2\ia64 T:\SQL2008R2\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll

7> check whether you have DefaultSetup.INI .. at this location .. mostly you should I them .. if not we will create it

T:\SQL2008R2\x64

T:\SQL2008R2\x86 and

T:\SQL2008R2\ia64

If you have a DefaultSetup.INI at the above locations, add the following lines to each DefaultSetup.INI:

PCUSOURCE=”.\SP2″

note : SP2 is the folder we created within the original setup files .. if you used a different name .. please use that folder name ..

this tells the installation setup that service patch files are located within this folder ..

8> If you do not find any DefaultSetup.INI files in a rare case , create one with the following content:

;SQLSERVER2008 R2 Configuration File

[SQLSERVER2008]

PCUSOURCE=”.\SP2″

and copy to the following locations

T:\SQL2008R2\x64

T:\SQL2008R2\x86 and

T:\SQL2008R2\ia64

9> run the setup as you would do normally from with in T:\SQL2008R2 ..

below are the indications that you are doing a slipstream installation …

installation1

10> for this … I have only chosen database engine .. in the features ..

11> confirm tht you see “Update Setup Media Language Rule” as PASSED in the installation rules page

12> On the Ready to Install dialog, the Action will indicated (“Slipstream”) … this confirms … that we are going right .. till now ..

13> PCUSource parameters in the summary log .. states that it picked up the SP2 from the path we pointed it to ..

this confirms that our installation has gone right … do confirm the same by connecting using SSMS

*************************************************

Slipstream has reportedly given issues if we try to do installation of reporting services .. I have not selected the same for now ..

refer this for details http://sirsql.net/blog/2012/11/15/issues-with-slipstreaming-service-pack-2-on-to-sql-2008-r2.html

https://connect.microsoft.com/SQLServer/feedback/details/771260/sql-2008-r2-with-slipstreamed-sp2-fails-when-install-includes-reporting-services

I will try to do a reporting service installation in my next post …. ENJOY 🙂

Categories: installation, service pack Tags: service pack, slipstream, sql 2008 R2 SP2

powershell crash course part 2

June 4, 2013 GS Sohal Leave a comment

Hi all ,

part 2 of PowerShell .. took a back stage and I just figured out that I do not need to reinvent the wheel .. because some of the dedicated powershell

sites do a lot better work at it … YES ,, its extremely useful for SQL admins too .. so I will direct you to that sites instead …

SITES –> http://powershell.com/cs/

on this sites .. GO to TAB named EBOOKV2 .. which has 18 lesson online eBook

and it covers most of the basics we need to start with …

–> http://powershell.com/cs/blogs/ebookv2/default.aspx

below I will provide small descriptions of lessons and topics covered in the same ..

Chapter 1. The PowerShell Console

This chapter will introduce you to the PowerShell console and show you how to configure it, including font colors and sizes, editing and display options. Topics Covered: Starting PowerShell First Steps with the Console Incomplete

Chapter 2. Interactive PowerShell

PowerShell has two faces: interactivity and script automation. In this chapter, you will first learn how to work with PowerShell interactively. Then, we will take a look at PowerShell scripts. Topics Covered: PowerShell as a Calculator

Chapter 3. Variables

It is time to combine commands whenever a single PowerShell command can’t solve your problem. One way of doing this is by using variables. PowerShell can store results of one command in a variable and then pass the variable to another command.

and so forth .. you get 18 lessons to get going with basics .. its extremely well explained and of help to ADmins ..

ENjoy 🙂

Categories: Powershell, Powershell Crash course Tags: crash crouse, powershell for DBA, SQL

drop failed for login test or not able to drop a login in SQL server

May 29, 2013 GS Sohal 1 comment

ERROR:

i wanted to drop a TEST login that i have created some days back ..

i was getting below error

i checked and closed all query window connections and also all SSMS object explorer connection from this particular login

but still i got the same error again ….

CAUSE ::

i looked into the results of sysprocesses to see if anything is still connected VIA TEST login ..

i Figured out a program named Microsoft SQL Server Management Studio – Transact-SQL IntelliSense

was connected to Server VIA .. login named TEST .. that is to say intelliSense remains connected to SQL server long after the the query analyzer and SSMS connections are explicitly closed ..

i used below query to kill .. these IntelliSense connections and i was able to drop the TEST login

select ‘KILL’ + ‘ ‘ + cast (spid as char(5)),*
from sysprocesses where spid > 50 and program_name like ‘%IntelliSense%’ and loginame like ‘name of the login you want to drop’

KILL 88
KILL 104

enjoy 🙂

Categories: security Tags: not able to drop a login, Transact-SQL IntelliSense

bangalore-user-group-meeting

May 22, 2013 GS Sohal Leave a comment

Hey guys check out cool coverage by Balmukund on bangalore-user-group-meeting

http://sqlserver-help.com/2013/01/22/coverage-report-sql-server-bangalore-user-group-meeting-3-19-january-2013/

Enjoy 🙂

Categories: General

alter index failed for SCOM OperationsManagerDW

May 22, 2013 GS Sohal 1 comment

Error :: something similar to below

Executing the query “ALTER INDEX [PK__Performa__AFAD0EB40688E6B3] ON [P…” failed with the following error: “Cannot find index ‘PK__Performa__AFAD0EB40688E6B3’.”.
Executing the query “ALTER INDEX [PK__EventSta__95632343599644AB] ON [E…” failed with the following error: “Cannot find index ‘PK__EventSta__95632343599644AB’.”

Cause of error is an in build SCOM functionality that drops and recreates tables named Event Stage (also the associated primary keys) every 60 secs (refer below )

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/04/19/db-maintenance-rebuild-index-task-always-fails-on-operationsmanagerdw-scom-database.aspx

Solution ::

1> you may excluded the database named OperationsManagerDW from re-indexing jobs as this particular DB has its own building maintenance feature that runsEvery 60 secs

http://blogs.technet.com/b/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx

enjoy 🙂

Categories: INDEXES Tags: alter index failed, OperationsManagerDW, SCOM

User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced

May 21, 2013 GS Sohal 3 comments

Error :

User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced

First and foremost ::

please check whether PASSWORD for the ACCOUNT is expired ? if so please reset it and try again (before going ahead with below steps)

Solution::

1. Click the Start button and type gpedit.msc in the Search programs and files bar and hit enter.
2. At the left pane, go to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options
3. Look for “Accounts: Limit local account use of blank passwords to console logon only” and double click on it.
4. By default the Enable option is selected and all you need to do is select “Disable” and click OK.

Now you can connect to the computer using Remote Desktop and login to the user account that don’t have a password. Some Windows such as the XP home doesn’t have gpedit.msc and you will have to make the changes from registry.

1. Type regedit at the Search programs and files bar and hit enter.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Look for LimitBlankPasswordUse name on the right pane, double click on it and set the value data to 0.

REF :: http://www.top-password.com/blog/how-to-fix-logon-failure-user-account-restriction/

OR

go to run type regedit.exe
then go here HKLM\System\CurrentControlSet\Control\Lsa\limitblankpassworduse
change from 1 to 0

REF::http://social.msdn.microsoft.com/Forums/en-US/iewebdevelopment/thread/6b65866a-10cc-42c8-8277-c9d9cd6c8afe

ENjoy:)

Categories: General Tags: Password, User account restriction

An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server

May 20, 2013 GS Sohal Leave a comment

ERROR: while retrieving data over linked server … you receive below error

Msg 7313, Level 16, State 1, Line 2
An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server “LinkedServername.DBName .dbo.tablename”

Cause ::

the database named DBName on target server .. that is the server to which your linked server is pointing contains a SPACE in its name

so in reality the database name is ” DBNAME ” that is it has leading or trailing spaces in its name …

steps to resolve the same

1>USE DBNAME;

GO
2> ALTER database DBNAME set single_user with rollback immediate

3> EXEC sp_renamedb ‘DBNAME ‘, ‘DBNAME123‘

4> EXEC sp_renamedb ‘DBNAME123’, ‘DBNAME‘

above script will resolve the linked server error :

if you try to run below script and by pass step no. 3 .. you will receive an error saying database named DBNAME already exist

EXEC sp_renamedb ‘DBNAME ‘, ‘DBNAME‘ (SQL need the new name to be different from the old name )

so follow step 1 to 4 .. to resolve error similar to below :: enjoy 🙂

An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server

Categories: linked Server

set SQL error log retention period through T-SQL

April 18, 2013 GS Sohal Leave a comment

we all may need to tweak the SQL error log retention period on a single instance or list of SQL server VIA CMS (central management server)

below 2 commands are quite useful for achieving the same

READ the current setting of sql error log retention

/* USE [master] GO EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’ GO */

you may receive below error on few server .. you may want to go ahead with the update command and come back to see the results of xp_instance_regread

possible error message for some server ..

RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’ servername(username): Msg 22001, Level 1, State 1

set the LOG retention sql error to 20 days

/* USE [master] GO EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 20 GO */

above command will set the retention to 20 days and you may rerun EXEC xp_instance_regread to confirm the same setting

Categories: General, T-sql Tags: Error message, Microsoft, sql error log retention period

Property Size is not available for Database ‘DBNAME’. This property may not exist for this object

April 16, 2013 GS Sohal 2 comments

recently i wanted to change the recovery model of a DB to simple recovery model VIA GUI ..

as i right clicked the Database –> property .. i received a strange error as below ..

ERROR message ::

TITLE: Microsoft SQL Server Management Studio

——————————

Cannot show requested dialog.

——————————
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

——————————

Property Size is not available for Database ‘DBNAME’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476

——————————
BUTTONS:

OK
——————————

i was sysadmin on the sql instance .. so it should not have been related to permission
i checked the list of DB on the server with sp_helpdb command and noticed the owner for the concerned DB was set to NULL
so i tried assigning the DB ownership to one of the valid logins using below command
sp_changedbowner [ @loginame = ] ‘login’
[ , [ @map= ] remap_alias_flag ]

above method resolved the issue and i was able to access the property tab ..

other consideration –> i did hear someone receiving similar error when the DB ownership was set to a valid login
i will try to post some workaround for the same ..

ENJOY 🙂

Categories: INDEXES Tags: DB owner not set, not able to access database property tab

what are my privileges or rights on an SQL instance

April 9, 2013 GS Sohal 1 comment

“Boot down pill” / Biohacks / SML.20130205.IPH5.10051 (Photo credit: See-ming Lee 李思明 SML)

whoops!!! it happens we are given certain rights on SQL instance .. we tend to ask “WHAT are MY RIGHTS on THIS instance ??? ”

and you may be said “WELL FIND IT OUT yourSELF”

then we search for a PILL .. which could help us on the spot ..

fn_my_permissions is similar to the PILL .. that could work wonders for you ..

the results you get after running this will vary according to your access level on an instance

select * from fn_my_permissions (NULL,’SERVER’) — gives access details at server level

select * from fn_my_permissions (NULL,’Database”) — gives access details at DB level

list of privileges if your are sysadmin on a SQL server instance

select * from fn_my_permissions (NULL,’SERVER’)

entity_name	subentity_name	permission_name
server		CONNECT SQL
server		SHUTDOWN
server		CREATE ENDPOINT
server		CREATE ANY DATABASE
server		ALTER ANY LOGIN
server		ALTER ANY CREDENTIAL
server		ALTER ANY ENDPOINT
server		ALTER ANY LINKED SERVER
server		ALTER ANY CONNECTION
server		ALTER ANY DATABASE
server		ALTER RESOURCES
server		ALTER SETTINGS
server		ALTER TRACE
server		ADMINISTER BULK OPERATIONS
server		AUTHENTICATE SERVER
server		EXTERNAL ACCESS ASSEMBLY
server		VIEW ANY DATABASE
server		VIEW ANY DEFINITION
server		VIEW SERVER STATE
server		CREATE DDL EVENT NOTIFICATION
server		CREATE TRACE EVENT NOTIFICATION
server		ALTER ANY EVENT NOTIFICATION
server		ALTER SERVER STATE
server		UNSAFE ASSEMBLY
server		ALTER ANY SERVER AUDIT
server		CONTROL SERVER

select * from fn_my_permissions (NULL,’DATABASE’)

entity_name	subentity_name	permission_name
server		CONNECT SQL
server		SHUTDOWN
server		CREATE ENDPOINT
server		CREATE ANY DATABASE
server		ALTER ANY LOGIN
server		ALTER ANY CREDENTIAL
server		ALTER ANY ENDPOINT
server		ALTER ANY LINKED SERVER
server		ALTER ANY CONNECTION
server		ALTER ANY DATABASE
server		ALTER RESOURCES
server		ALTER SETTINGS
server		ALTER TRACE
server		ADMINISTER BULK OPERATIONS
server		AUTHENTICATE SERVER
server		EXTERNAL ACCESS ASSEMBLY
server		VIEW ANY DATABASE
server		VIEW ANY DEFINITION
server		VIEW SERVER STATE
server		CREATE DDL EVENT NOTIFICATION
server		CREATE TRACE EVENT NOTIFICATION
server		ALTER ANY EVENT NOTIFICATION
server		ALTER SERVER STATE
server		UNSAFE ASSEMBLY
server		ALTER ANY SERVER AUDIT
server		CONTROL SERVER

Enjoy 🙂

Categories: General Tags: Authentication, Login, Microsoft SQL Server, SQL

Newer Entries Older Entries

SQL DBA learning curve

how to Slipstream SP2 or SP1 for SQL 2008 R2

powershell crash course part 2

Chapter 1. The PowerShell Console

This chapter will introduce you to the PowerShell console and show you how to configure it, including font colors and sizes, editing and display options. Topics Covered: Starting PowerShell First Steps with the Console Incomplete

Chapter 2. Interactive PowerShell

bangalore-user-group-meeting

alter index failed for SCOM OperationsManagerDW

User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced

An invalid schema or catalog was specified for the provider “SQLNCLI10” for linked server

set SQL error log retention period through T-SQL

Property Size is not available for Database ‘DBNAME’. This property may not exist for this object

Follow Blog via Email

CHECK previous POST

Categories

Disclaimer

Twitter Updates

GS Sohal

Sqlcurve