How do you Configure sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How do you Configure sql server

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-06-07 : 09:20:26

Hi,

Am very much confused with the term how do you configure your sql server 2000/2005/2008?

I would like to know what configurations can be made before / after install?

What would a senior DBA would expect if he is asking a new guy getting into SQL Server Administration??

Thanks in Advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-07 : 11:13:30

We enable lock pages in memory for the SQL Server service account. We set maxdop to 4, due to the number of CPUs we have on our servers. We cap the max memory on our production SQL instances. On our servers with 48GB of memory, we cap SQL Server at 40GB. We also move tempdb to its own mount point and add as many data files as there are CPUs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-06-07 : 13:23:39

Loads of stuff ... but not written down

. It would be good to get it into a single thread here

Change MODEL to PAGE_VERIFY CHECKSUM, READ_COMMITTED_SNAPSHOT and any other defaults to force them for new DBs

Change TEMPDB to be sufficiently large initially

Check the location of System databases MDF + LDF files - in case they have been set up in the C:\Program Files folder

Check Collation is what you want (installers something muck that up)

SELECT 'Collation', SERVERPROPERTY( 'Collation' )

Check Service Pack is "latest"

SELECT @@VERSION
EXEC Master..xp_msver

Check backups being made (for us that is the installation of our "Admin" database on the server, and setting up a scheduled task so it gets called regularly; for others that might be to set up a Maintenance Plan)

Enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Change "listening" port from 1433 to a 5-digit port number

Enable SQL Logins; set up accounts for SysAdmin

Set up any Linked Servers
Set up any servers that need to link to the new server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-07 : 14:01:15

Oh yes READ_COMMITTED_SNAPSHOT is an important one for us!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-06-08 : 13:51:41

Thanks for the suggestions!

I would like to know what advantage if we go for READ_COMMITTED_SNAPSHOT.

Also, i would like to know how to deal if my server is having 4 CPU's.

Is that always good to take advantage of multiple CPu's or else any drawbacks or limitations or measures we need to take care while configuring multiple CPU's!!! What is maxdop?

Also, As you said
"On our servers with 48GB of memory, we cap SQL Server at 40GB",
what is meant by " CAP sql server "??? How do you implement this ??
Am sorry if am asking the question in a wrong way or may not put into correct words...

Also, this is what i understood from the below words

"We also move tempdb to its own mount point and add as many data files as there are CPUs."

Tempdb is kept in a separate drive all together.. right?

Kristen
Test

22859 Posts

Posted - 2010-06-08 : 13:57:18

"I would like to know what advantage if we go for READ_COMMITTED_SNAPSHOT."

Should stop READS blocking WRITES.

Dunno if multiple CPUs are a benefit (compared to same number of cores). I expect CPUs increase license cost though ...

"what is meant by " CAP sql server "??? How do you implement this ??"

Configure SQL to only use 40GB of RAM - default is to use all available.

"Tempdb is kept in a separate drive all together.. right?"

Ideally, yes.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-06-09 : 02:02:29

Thanks Kristen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources