March 16, 2009 at 3:41 pm
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.
---------------------------------------------------------------------
March 16, 2009 at 5:05 pm
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.
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]
April 25, 2009 at 4:49 am
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
April 25, 2009 at 10:01 am
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.
---------------------------------------------------------------------
April 26, 2009 at 12:50 am
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
April 26, 2009 at 5:14 am
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
---------------------------------------------------------------------
April 26, 2009 at 11:08 pm
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