Wednesday, April 09, 2014

How to add SQL Login in Secondary server in Log Shipping?

 

For SQL Login, the SID is unique to the SQL Server instance level, so SID of SQL Login of Primary and Secondary are different.
It will result in orphan user.

Whereas Windows Login SID are unique to the Domain level. Hence when we create the Windows Login, SID are matching

Windows Login
SQL Login
If you have Windows logins, the login SID in SQL Server will match the SID from Active Directory. So all you need to do is create the same login on both your primary and secondary server. SQL Server logins are a little more complicated, as the SQL Server assigns the SID when the login is created
All database users are linked to logins by SID; if the SID of a login is different on the secondary server, the database users tied to that login will be orphaned. This can be fixed, but it's better to avoid the problem all together

 Solution :
Manually specifying of SID, while creating login on the Secondary SQL Server will resolve the issues.


Execute the sp_help_revlogin on your primary to generate all the create statements for your logins, both Windows and SQL Server. For each Windows Login you get code like this:


CREATE LOGIN [WinDomain\siva] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

This will allow you to just recreate the login on the secondary server.

For each SQL Server Login you will get something like this statement:

CREATE LOGIN [sql_test1] WITH PASSWORD = 0x0100A7B0020EEAB8D0B17D28883AA11267W7575B2F55CEC9EE HASHED,
SID = 0x7DE18E2C10F4FE4CA127E9837FCE4D3C, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Notice that the statement contains the encrypted version of the password and the SID.
This means that when run on the secondary server, the password will match the SID and we will not have any issues after the failover.

Wednesday, February 19, 2014

HP Data Protector command to backup the SQL Server DB Backup flat file to Tape media

 

First we have to configure the HPDP Backup Specification

1.Login HP Data Protector

2. Create the Backup Specification

3. Source – Select the Server, Folder where the SQL Server Database are backed up

   Destination – Select the Tape Media Library to which the DB Flat files from disk to be baked up

   Options  -- Provide the Description details and  the Protection like 6 Weeks

   Schedule – Skip it

   Click Apply and generate the Backup Specification.

   Use this Backup Specification name on calling the HPDP Backup using

Create the DOS Batch files below

For Full Backup – It can be used weekly when we backup FULL databases

c:
cd\Program Files\OmniBack\bin
omnib -datalist Win-SQL-FS-OnDemand -mode Full -load high

For Incremental Backup – It can be used daily when we take differential backup of databases

c:
cd\Program Files\OmniBack\bin
omnib -datalist Win-SQL-FS-OnDemand -mode incremental1 -load high

On the SQL Server Agent Jobs for the FULL / Differential Backup add another job step to trigger the DB Backup of flat files to the Tape Media by calling the DOS batch file appropriately.

image

image

Omnib usage


Usage synopsis:
 
    omnib -version | -help
 
    omnib -filesystem Host:MountPoint Label -device LogicalDevice
          [Mirror options]...[Mirror options]
          [General options] [Filesystem options] [-public]
 
    omnib -filesystem Host:MountPoint Label -device LogicalDevice
          -ndmp Type [NDMP options] [-public]
 
    omnib -winfs Host:MountPoint Label -device LogicalDevice
          [Mirror options]...[Mirror options]
          [General options] [Filesystem options] [Winfs options]
          [-public]
 
    omnib -winfs Host:MountPoint Label -device LogicalDevice
          -iap [General options] [Filesystem options] [Winfs options]
          [-public]
 
    omnib -NetWare Host:MountPoint Label -device LogicalDevice
          [[Mirror options]...[Mirror options]
          [NetWare options] [General options]
          [Filesystem options] [-public]
 
    omnib -host HostName:/ Label -device LogicalDevice
          [Mirror options]...[Mirror options]
          [General options] [Filesystem options] [-public] [-storedrim]
 
    omnib -rawdisk Host Label SectionList -device LogicalDevice
          [Mirror options]...[Mirror options]
          [General options] [-public]
 
    omnib -omnidb Host:MountPoint Label -device LogicalDevice
          [Mirror options]...[Mirror options]
          [General options]
 
    omnib -restart SessionID [-disk_only]
 
    omnib -datalist Name [Datalist options]
   
    omnib -resume SessionID [-no_monitor]
    
    omnib -sap_list ListName   
          [-barmode SapMode]   
          [List options]
 
    omnib -sapdb_list ListName   
          [-barmode SapDBMode]   
          [List options]
 
    omnib -oracle8_list ListName   
          [-barmode Oracle8Mode]   
          [List options]
 
    omnib -sybase_list ListName   
          [-barmode SybaseMode]   
          [List options]
 
    omnib -informix_list ListName   
          [-barmode InformixMode]   
          [List options]

    omnib -mssql_list ListName   
          [-barmode MSSQLMode]   
          [List options]
 
    omnib -msese_list ListName   
          [-barmode MSExchangeMode]   
          [List options]
    
    omnib -e2010_list ListName   
          [-barmode MSExchange2010Mode]   
    omnib -lotus_list ListName   
          [-barmode LotusMode]   
          [List options]
    
    omnib -msvssw_list ListName   
          [-barmode VSSMode]   
          [List options]
    
    omnib -mbx_list ListName   
          [-barmode MSMailboxMode]   
          [List options]
    
    omnib -vmware_list ListName   
          [-barmode VMwareMode]   
          [List options]
    
    omnib -db2_list ListName   
          [-barmode Db2Mode]   
          [List options]
    
    omnib -mssps_list ListName   
          [-barmode MSSPSMode]   
          [List options]
   
    omnib -mssharepoint_list ListName   
          [-barmode MSSharePointMode]

    omnib -veagent_list ListName   
          [-barmode VEPAMode]
   

    Filesystem options:
 
        -trees TreeList
        -only MatchPattern
        -exclude TreeList
        -skip MatchPattern
        -lock
        -touch
        -[no_]log | -log_dirs | -log_file
        -mode {full | incremental | incremental1 ... | incremental9}
        -enh_incr
        -clp
        -[no_]hlink
        -size FromRange ToRange

    Winfs options:
 
        -no_share_[info]
        -[no_]nthlinks
        -[no_]archatt
        -vss {fallback | no_fallback}
        -no_vss
        -[no_]async

    Mirror options:
 
        -mirror LogicalDevice [-pool MediaPool -prealloc MediaList]

    General options:
 
        -preview
        -pool MediaPool
        -prealloc MediaList
        -protect {none | weeks n | days n | until Date | permanent}
        -report {warning | minor | major | critical}
        -pre_exec Pathname
        -post_exec Pathname
        -compress
        -encode ["aes256"]
        -load {low | medium | high}
        -crc
        -no_monitor
        -keepcatalog {weeks n | days n | until Date}
        -var[iable] <var_name> <var_value>    

    Datalist options:
 
        -select SelectList
        -mode {full | incremental | incremental1 ... | incremental9}
        -protect {none | weeks n | days n | until Date | permanent }
        -keepcatalog {weeks n | days n | until Date}
        -preview
        -load {low | medium | high}
        -disk_only
        -crc
        -no_monitor

    List options:
 
        -barcmnd Command
        -protect {none | weeks n | days n | until Date | permanent}
        -keepcatalog {weeks n | days n | until Date}
        -load {low | medium | high}
        -disk_only
        -crc
        -no_monitor
        -test_bar

    NetWare options:
 
        -[no_]NWuncompress

    NDMP options:

        -ndmp_user UserName
        -ndmp_passwd Password
        -ndmp_env FileName
        -ndmp_bkptype {dump | NVB | SMTape}      
    Type = { Generic | NetApp | Celerra | BlueArc | Hitachi | HP-X9000 }   

    SapMode      = { full | incr }   

    SapDBMode    = { full | diff | trans }    

    Oracle8Mode  = { -full | -incr1 | -incr2 | -incr3 | -incr4 }   

    SybaseMode   = { full | trans }   

    InformixMode = { full | inf_incr1 | inf_incr2 }   

    MSSQLMode    = { full | diff | trans }   

    MSExchangeMode  = { full | incr }   

    MSExchange2010Mode = { full | diff | incr | copy }   

    LotusMode    = { -full | -incremental }   

    MSMailboxMode   = { -full | -incr | -incr1}   

    VMwareMode   = { full | diff | incr}   

    VSSMode      = { full | copy | incr | diff}   

    Db2Mode      = { -full | -incr | -delta}   

    MSSPSMode    = { full | diff | trans }   

    MSSharePointMode = { full | diff | incr }   

    VEPAMode     = { full | diff | incr }   

    Date = [YY]YY/MM/DD   (1969 < [YY]YY < 2038)

Friday, October 18, 2013

Query to get the latest FULL backup details

 

SELECT  
cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION,
SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,  
  b.backup_start_date,b.backup_finish_date,
Round(b.backup_size/(1024*1024),2) as Size_n_MB,
Physical_device_name
  FROM msdb.dbo.backupset b,
  ( select a.DATABASE_NAME, MAX(a.backup_set_id) id
  from msdb.dbo.backupset a 
WHERE a.TYPE = 'D' and 
a.DATABASE_NAME = DATABASE_NAME 
group by a.DATABASE_NAME ) cc,
  msdb.dbo.backupmediafamily f
WHERE TYPE = 'D'
AND b.backup_set_id = cc.id
  AND b.media_set_id = f.media_set_id

Find time difference between two dates

 

SELECT
b.backup_finish_date,
b.backup_start_date,
CAST ((b.backup_finish_date - b.backup_start_date) AS TIME(0))
FROM backupset b
WHERE convert(varchar(12),b.backup_finish_date,101) = '10/13/2013'

Wednesday, September 25, 2013

Update job notification

 

Following Query will update the Existing job name starts with ‘WIN%’ as on complete to notify the email operator 'SQLDBASupport'

select
'EXEC msdb.dbo.sp_update_job @job_name=N'+CHAR(39)+name+CHAR(39)
+' ,
        @notify_level_email=3,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @notify_email_operator_name=N'+char(39)+'SQLDBASupport'+char(39)
from msdb.dbo.sysjobs
where name like 'WIN%'
and notify_email_operator_id =0

Thursday, August 15, 2013

SQL Server: Query to check if given column exists

 



select  
s.[name] 'Schema',
t.[name] 'Table',
c.[name] 'Column',
d.[name] 'Data Type',
d.[max_length] 'Max Length',
d.[precision] 'Precision',
c.[is_identity] 'Is Id',
c.[is_nullable] 'Is Nullable',
c.[is_computed] 'Is Computed',
d.[is_user_defined] 'Is UserDefined',
t.[modify_date] 'Date Modified',
t.[create_date] 'Date created'
from sys.schemas s
inner join sys.tables t
on s.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types d
on c.user_type_id = d.user_type_id
where c.name like 'Person%'


Tuesday, April 30, 2013

How to: search for a word or column name across the Procedure, Function, Trigger, View

 

DECLARE @SearchPhrase nvarchar(1000)
 
SET @SearchPhrase = N'backup'   -- Place your search string
 
SELECT
DISTINCT sysobjects.name AS [Object Name],
CASE
  WHEN sysobjects.xtype = 'P' THEN 'Stored Proc'
  WHEN sysobjects.xtype = 'TF' THEN 'Function'
  WHEN sysobjects.xtype = 'TR' THEN 'Trigger'
  WHEN sysobjects.xtype = 'V' THEN 'View'
END as [Object Type],
(SELECT ParentTable.[Name]
FROM sysobjects ParentTable
WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
FROM sysobjects,
     syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
--AND sysobjects.category = 0  --- To restrict only user defined object
AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

How to: find when the database last got restored?

 

   1:  SELECT destination_database_name,max(restore_date) as restore_date


   2:  FROM msdb.dbo.restorehistory 


   3:  WHERE destination_database_name = 'testDB’  ---- Place your database name here


   4:  GROUP BY destination_database_name




The result will look like;



destination_database_name     restore_date

testDB                        2013-04-29 11:49:19.940


SQL 2008 Management Studio does not allow to save changes to tables

 

In SQL Server 2008 Management Studio,

We can not do

   •Change the Allow Nulls setting for a column

   •Reorder the columns

   •Change the column data type

    •Add a new column

How to fix / Resolution:

    •Go to Tools -> Options -> Designers -> Tables and Designers

     •Uncheck the Prevent Saving Changes that require table re-creation

image