April 24, 2009 at 2:17 pm
New to 2005 and in the past always used sa in mixed authentication mode.
While I can see the definitions for domain user, local system and network service, I'm confused as to what will be most appropriate for our environment.
We are a non-profit that is thankfully large enough to put each app on its own server. The app vendor refuses to discuss SQL installation but it is a commercial app intended for non-profits that will reside on the server in question.
We have frequent power outages and I've found that the servers that are required to be logged in as specific users are never logged in that way after a power outage, which messages up various things - wouldn't this be the domain user account that would run the services? And isn't this how Agent could fail to restart so my backups and other jobs wouldn't run?
I do expect to run every service in the same account, but by domain user are we talking about network domain or just this server? I have in the past not used network authentication as I believe it is better to limit server access and let the app handle most of the security. Users should not be accessing the servers directly, only via the apps to which they are granted access.
While security is important here, it's actually more important to have instant recovery as I have no backup and the network guy tends to not read my documentation and often doesn't log into the servers at all, just turns them on.
What would give me the best shot at, um, foolproofedness?
April 24, 2009 at 2:59 pm
If you are running an AD domain - I would recommend using a domain account for your service line account. This will give you the ability to setup processes that can access network resources if needed. For example, if you wanted to backup to a network share - you need the domain account setup with privileges on the network share.
I would also recommend that you make sure you have a solid maintenance plan configured and running. At a minimum, you should be performing daily integrity checks and backups. If you need 'instant' recovery, you are going to need to implement at a minimum:
Daily Full Backups
Differential Backups (at least every 4 hours)
Transaction Log Backups (at least every hour, recommend every 15 minutes)
The above will give you the ability to restore the system with no more than 15 minutes of data loss, as long as the backups are NOT on the same storage array where your database resides. Ideally, you would backup to a local drive and copy the backups to a network share. Then, the files on the network share should be copied to tape and shipped offsite for disaster recovery.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 5:21 pm
I agree with Jeffrey.
AD Service accounts:> for backups and other administrative actions.
the other system level accounts are mostly being created by the OS and other installed systems and applications.. I would not change those.. except for the SQL server services that you probably need another AD service account(s).
IMPORTANT: try not to use one service account for many actions.. that way would be able to tell what is failing in the even logs.
Just a thought.
good luck
Cheers,
John Esraelo
April 27, 2009 at 8:29 am
I apologize, by "no backup" I mean I have no second chair - no one does what I do. Nor understands it, nor cares to. Unfortunate choice of wording.
I always set backups to a share on the local server and nightly net backups copy them to tape. I usually keep 2 weeks locally as that should be enuf depth if either the server or the net backups fail. I'm fully aware that our net guy does not take tapes offsite and I fear the tape recycling is too frequent. However, I can only control my own realm of responsibility. At end of month, I burn DVDs of the backups and take them home with me.
Also always have full maint plans on them, run all that middle of the night (we are not 24x7).
I guess my concern with using a true network domain account is that, in my absence, I cannot trust that someone will log that server in should we lose power, but will merely power it back up and walk away. I also doubt my network guy would willingly create such an account for me. I could prob'ly sell if it's really the way to go, but I'm not sold yet.
Without that user logged in, my services wouldn't run. Which would mean, no backups, no maint, no scheduled jobs. From what I've seen of 2005, Agent does NOT start at box startup tho I have set it to fire automatically. Am I right about that or does that seem like something faulty going on? That's why I'm leaning towards local service account - the individual servers do not need access to the rest of the network - they neither read nor write from anything not local. I was thinking local service is 'always on' if the os has launched.
I'm a recovered workaholic and many of my vacations are out of country. I have to make it easy for someone that would bring my server back online, but I have to assume they would do nothing other than hit the power button.
Thanks so much so far, sorry to be such a dunce on this. 2005 was thrown at me suddenly and I'm scrambling.
April 27, 2009 at 9:32 am
SQL Server and Agent - and any other SQL Server related services are just that - services. There is no reason to log into the server to get them started, they start when the server starts.
If SQL Server Agent is not starting when the server is restarted, there is definitely messed up in the settings.
Whether or not you use a local account or domain account should have no bearing on whether or not the services start. As long as the account has the appropriate permissions to run SQL Server - there should be no problem.
If you make sure you use the configuration manager to change the service account - all privileges will be set appropriately.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2009 at 7:53 am
Excellent, thank you!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply