Dettaching Luns from failed active SQL Server to passive

  • Mohit (3/16/2009)


    george sibbald (3/16/2009)


    thats rather a lot for mirroring. Doable with log shipping though.

    I have over 30-40 databases running on my SQL Server 2005 server with no issues; have you seen any issues with high number of databases in mirror state?

    Mohit.

    thats encouraging to hear mohit. It was just early reports I heard about mirroring, maybe I am out of date, and I am a big log-shipping fan. I think I would be wondering about all those ports for endpoints by now. 🙂

    What mode are you running mirroring in? That may have a bearing.

    ---------------------------------------------------------------------

  • Actually I am at 80 databases LOL; urg.. I forgot stuff heh. And we are running high-avaliablity mode. If you like log shipping you should love mirroring too, I likes both :).

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Hi Ed,

    Considering your SRDF scenario (of keeping System DB's in Local drive and keeping only User DB's in SAN in Scenario and replicate only User DB's) Patching wont be a issue since we can install Patches separately on two servers (Primary and DR). This also resolves the query of how dll's, registries, tools and exe in c:\ will be replicated e.t.c

    But I have a query, What about content changes of Systems databases, like New Login, Server option change, New Job, Changes in Job Schedules e.t.c... How you handled that? Did you create some scheduled jobs to script these and apply at DR Server?

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • But I have a query, What about content changes of Systems databases, like New Login, Server option change, New Job, Changes in Job Schedules e.t.c... How you handled that? Did you create some scheduled jobs to script these and apply at DR Server?

    you would have to, much as you would do if you were log shipping or mirroring.

    ---------------------------------------------------------------------

  • Hi George.

    Thanks for your reply.. Please share incase you have any scripts which covers all of the above....

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

  • But I have a query, What about content changes of Systems databases, like New Login, Server option change, New Job, Changes in Job Schedules e.t.c... How you handled that? Did you create some scheduled jobs to script these and apply at DR Server?

    My server pairs are active\active so apart from initial build I do not apply on the other side by automated means, what I do is reverse engineer SQL required to re-create the environment and xcopy those scripts to the standby. I have mostly SQL 2000 as well so not using DMVs or sys.server_principals as yet

    there are almost as many scripts for these processes as DBAs so its worth googling it, but

    Logins - can't beat sp_help_revlogin. there is also the transfer_logins DTS or SSIS task but I do not recommend this for SQL authenticated logins.

    jobs - I use the generate script function in EM or SSMS on an ad-hoc basis. I also back up msdb over the network to the standby box

    default databases (not required 2005):

    set quoted_identifier off

    select 'exec sp_defaultdb ',+"'" +loginname +"'," +dbname from syslogins

    default language:

    set quoted_identifier off

    select 'exec sp_defaultlanguage ',+"'" +loginname +"'," +language from syslogins

    server fixed roles granted:

    set quoted_identifier off

    set nocount on

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'sysadmin'"

    from syslogins where sysadmin = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'securityadmin'"

    from syslogins where securityadmin = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'serveradmin'"

    from syslogins where serveradmin = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'setupadmin'"

    from syslogins where setupadmin = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'processadmin'"

    from syslogins where processadmin = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'diskadmin'"

    from syslogins where diskadmin = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'dbcreator'"

    from syslogins where dbcreator = 1

    union all

    select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"

    +"'bulkadmin'"

    from syslogins where bulkadmin = 1

    Server configs don't change often enough to automate, but this scripts out everything I need to know

    set nocount on

    print 'database information'

    declare

    @name sysname,

    @statement nvarchar(140),

    @mode int,

    @account varchar(50),

    @sp-2 nvarchar(10)

    select getdate()

    declare helpdb_cursor cursor for

    select name from sysdatabases

    open helpdb_cursor

    fetch next from helpdb_cursor

    into @name

    while @@fetch_status = 0

    begin

    --use db statement and other statements need to be in same exec for change of db context to work

    set @statement = N'use [' + @name +']'+char(13)+N'exec sp_helpdb [' + @name +']'

    exec sp_executesql @statement

    fetch next from helpdb_cursor

    into @name

    end

    close helpdb_cursor

    deallocate helpdb_cursor

    /*************************************************************************************/

    print 'configuration options'

    exec sp_configure 'show advanced options',1

    reconfigure with override

    exec sp_configure

    exec sp_configure 'show advanced options',0

    reconfigure with override

    print 'login information'

    exec sp_helplogins

    print 'version information'

    select @@version

    select @sp-2 = convert(nvarchar(10),serverproperty('productlevel'))

    print 'this is ' + @sp-2

    print ''

    exec sp_helpsort

    print 'authentication mode is '

    select @mode = convert(int, serverproperty('isintegratedsecurityonly'))

    if @mode = 1

    print 'authenticated mode'

    if @mode = 0

    print 'mixed mode'

    print ''

    print 'License information '

    select serverproperty('licensetype')

    select @mode = convert(int,serverproperty('numlicenses'))

    select 'number of licenses = ' + convert(varchar(10),@mode)

    print 'MSSQLServer service account '

    exec xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\MSSQLServer', 'Objectname', @account output

    print @account

    print 'SQLServerAgent service account '

    exec xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\SQLServerAgent', 'Objectname', @account output

    print @account

    print ''

    print 'the latest errorlog'

    exec sp_readerrorlog

    If you have DTS\SSIS you should unload those to the filesystem as well and copy over

    ---------------------------------------------------------------------

  • Hi George,

    Many thanks... it will help our SRDF Implementation....

    Regards,
    Sakthi
    My Blog -> http://www.sqlserverdba.co.cc

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply