February 12, 2010 at 6:52 am
I have found a number of articles stating that problems can be resolved by using sqlservr with various Trace Flags.
I have found the parameters for sqlservr by typing sqlservr /? at a dos prompt but am unable to find a list of Trace Flags. Just mention of them to run in various scenarios, none of which fit my issue. - See posting of yesterday.
Guidance/links relaring to Trace Flags would be most welcome.
Thanks
Colin
February 14, 2010 at 5:50 am
Colin Betteley (2/12/2010)
I have found a number of articles stating that problems can be resolved by using sqlservr with various Trace Flags.I have found the parameters for sqlservr by typing sqlservr /? at a dos prompt but am unable to find a list of Trace Flags. Just mention of them to run in various scenarios, none of which fit my issue. - See posting of yesterday.
Guidance/links relaring to Trace Flags would be most welcome.
Thanks
Colin
Hi colin,
What post from yesterday, what problem are you trying to solve with traceflags?
Gethyn Elliswww.gethynellis.com
February 14, 2010 at 7:25 am
Some traceflags are documented in Books online, some are mentioned in kb articles, many are completely undocumented. I don't think there's a list anywhere.
What problem are you trying to solve and why do you think a traceflag is the answer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2010 at 2:12 am
Thanks. Supose I was naive to think that there may be a list. So how do people find out about them?
My problem was originally put in a posting of 11th Feb called System Database Restore, which has been read 15 times, but no replies.
I was looking to restore Master amd msdb to a server with a different disk structure. I have done this in SQL 2000 with the help of Trace Flags and thought that it may be possible with other, unknown, Trace Flags.
I have come to the conclusion that what I want to do cannot be done.
Regards
Colin
February 15, 2010 at 2:19 am
Colin Betteley (2/15/2010)
I was looking to restore Master amd msdb to a server with a different disk structure.
To be quite honest, moving the system DBs from one server to another is a bad idea in the first place, regardless of the disk structure. Master is only really supposed to be restored on the same server it was backed up on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2010 at 2:29 am
Understood. But if that server bursts into flames.......
That, thankfully, is not the case. We are trying out various D.R. scenarios.
February 15, 2010 at 2:32 am
Colin Betteley (2/15/2010)
Understood. But if that server bursts into flames.......
Scripts of logins, jobs, etc.
Bear in mind that the master DB has stuff in it specific to the server it was created on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2010 at 2:44 am
That suggests that in a fast changing environment thatyou must daily script out logins, Jobs, DTS etc in order to maintain a D.R. solution. Hardly efficient.:-)
February 15, 2010 at 7:21 pm
There are jobs in SSIS that can be used to transfer logins, jobs, etc from one instance to another. I have never done them personally, but I do know they are there.
Fraggle
February 16, 2010 at 5:24 am
True, but if your source server no longer exists then you have a problem.
February 16, 2010 at 5:42 am
Not if those tasks have been scheduled nightly.
You can move master from one server to another. It often results in lingering, hard to debug issues, especially around server principals, but if you're happy with the risks, then go ahead. It's just not a good idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2010 at 7:00 am
Colin Betteley (2/16/2010)
True, but if your source server no longer exists then you have a problem.
welcome to the world of SQL2005 where DR is not as easy as it was because restores of msdb are no longer a good idea on a server with a different name, plus problems with orphaned SQL ids created by SQL at install time and encryption keys. Oh for the simple world of SQL2000.
If the contents of your master is changing that much (is it really), or SSIS packages change constantly (do they really) look at clustering as you DR solution (but remember this does not protect you against disk failure or loss of the site where the disks are) or if money no object look at SAN replication with boot from SAN.
Else you just need to script out server level objects on a daily basis and write the output on the standby server, plus implementing proper change control so the servers can be kept in line. SSIS should be put under proper source control using visual studio, IMHO its no longer a DBAs responsibility to provide backups of SSIS packages.
I definitely agree with previous posters that restoring system databases to another server is a bad idea, and if you try to do it without the same directory structure it just won't work.
---------------------------------------------------------------------
February 16, 2010 at 8:08 am
Great reply, lots to think about. Much appreciated
Colin
February 16, 2010 at 11:25 am
The only reason I have found to restore a system database was for a mass restore. If you a complete failure of you old system and you are moving to your new system, the most recent copy of the MSDB backup does have all of the file names listed and the location of them. With this information, you can script out all of the backsup right down to the TL logs.
Please note that I DO NOT restore over the current MSDB database, but rather restore it as a user database to reference.
Fraggle
February 17, 2010 at 1:30 am
Good tip. Thanks.
Colin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply