User Tools

Site Tools


database:mssql:optimize_instance_shpt

Optimize SQL instance for SharePoint

Alrighty then! You've succesfully installed SQL and now you need to install Sharepoint and “hook it up” to Your fresh new instance. Well STOP here and do continue reading this. It just might save You a lot of fuss later.

Sharepoint is an ecosystem in its own right, and once you've installed and set it up, it tends to take on a life of its own. In order for this to be possible SP has a ton of tools and services that - more or less - take care of just about everything and anything that allows SP to be a self-sustained system. That being said, one of the most important tools in your SP arsenal are the database tools, and every SP install is heavily dependant on them. So what am I talking about? I'm talking about the fact that SP manages its own databases, and does that very well. But in order to make that happen we need to tweak our fresh SQL install prior to installing SP. Once we do that, our SP will have a long and prosperous life.

Jokes aside - do note that what I am recommending / explaining in this tutorial are indeed real life best practices, so keep in mind that if you will be installing SP on someone elses SQL server/s inform them that this settings need be done for SP to function properly. And yes - i know DBAs can be a pain in the bottocks when it comes to making them do something your way, but it has to be what it has to be.

Let us begin

I'm a lasy person so I like to keep things simple and easy. In order to tweak the database for SP you can click on all of this stuff… or… you can just copy paste these query snippets and execute them as a whole, thus having a solution tha you can use while configuring all new SQL instances that you need to prepare for future SP installs.

USE [master]
GO
CREATE LOGIN [test.domain\sp_install] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'test.domain\sp_install', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'test.domain\sp_install', @rolename = N'securityadmin'
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'8192'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
USE [master]
GO
ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

There. Now you can clean up all this and make yourself a cute little soution that will make you a tru

database/mssql/optimize_instance_shpt.txt · Last modified: 2017/08/10 10:59 (external edit)