Tuesday, 18 October 2011

Pre-create SharePoint 2010 databases in SQL Server 2008 using PowerShell

There are some scenarios where it is a good idea to create databases in SQL Server prior to installing SharePoint. Typically, this is where a DBA may want control over the database naming convention and configuration in advance of letting you loose on the install DVD!

There are other benefits with doing this, too. For example, you can set the database and log file location on the file system, configure recovery mode, pre-grow, autogrowth and maximum database sizes in advance, and other Microsoft recommendations listed in the Storage and SQL Server capacity planning and configuration article on TechNet.

The example here lists all the databases and their configuration settings in a CSV file. We then use a PowerShell script to import this data and use the Invoke-Sqlcmd cmdlet to run a .sql file that creates the databases, configures the properties set in the CSV file, and assigns the SharePoint install account database owner permissions.

Before doing this, I recommend that you look at a couple of articles on TechNet in addition to the one listed above. First, the Deploy by using DBA-created databases article, which covers the process of installing SharePoint 2010 on pre-created databases (note that you cannot pre-create the Search service application databases using the method shown in this article), and also the “Adding the SQL Server Snap-ins to Windows PowerShell” section of the Running SQL Server PowerShell article, which goes through how to add the SQL Server Provider to the regular PowerShell or PowerShell ISE consoles – you will need to do this before running the PowerShell script shown in this article. Don’t forget that you will also need to login as an account with the correct permissions in SQL to be able to create the databases and set the permissions required.

CSV File

I am showing an example CSV file below that includes all columns needed for the script used in this article, but of course, you can add or remove any columns required for your deployment:

DBNAME,SETUPUSER,DATAFILEPATH,DATASIZE,DATAMAXSIZE,DATAFILEGROWTH,LOGFILEPATH,LOGSIZE,LOGFILEGROWTH,RECOVERYMODE
SP_Test1,DOMAIN\SP_Admin,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\DATA,1024000KB,1024000KB,102400KB,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\LOGS,1024KB,10%,SIMPLE
SP_Test2,DOMAIN\SP_Admin,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\DATA,512000KB,UNLIMITED,20%,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\LOGS,1024KB,10%,FULL

Admittedly, it doesn’t look the easiest to read here, but if you copy the contents to a CSV file using Notepad it will look a lot clearer. Note that I am not specifying SharePoint databases in the CSV file – I’ll leave that one to you considering your naming conventions and database configuration will vary anyway.

SQL File

Next, we need a .sql file that will be used by the PowerShell script to create the databases. This file was created using the “Script Action” option in the SQL Management Studio console after creating a single database and assigning dbo permissions to the SharePoint install account.

Script Action

I then saved the contents of the script as a “Create SharePoint Database.sql” file and replaced the values configured in the console with variables – e.g., replacing the database name with $(DBNAME). The PowerShell script shown later will replace these variables with values from the CSV file:

/* Create SharePoint database */
CREATE DATABASE [$(DBNAME)] ON  PRIMARY
( NAME = N'$(DBNAME)', FILENAME = N'$(DATAFILEPATH)\$(DBNAME).mdf' , SIZE = $(DATASIZE) , MAXSIZE = $(DATAMAXSIZE) , FILEGROWTH = $(DATAFILEGROWTH) )
LOG ON
( NAME = N'$(DBNAME)_log', FILENAME = N'$(LOGFILEPATH)\$(DBNAME)_log.ldf' , SIZE = $(LOGSIZE) , FILEGROWTH = $(LOGFILEGROWTH))
COLLATE Latin1_General_CI_AS_KS_WS
GO
ALTER DATABASE [$(DBNAME)] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$(DBNAME)] SET ARITHABORT OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$(DBNAME)] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$(DBNAME)] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [$(DBNAME)] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$(DBNAME)] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$(DBNAME)] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$(DBNAME)] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$(DBNAME)] SET  DISABLE_BROKER
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$(DBNAME)] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$(DBNAME)] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$(DBNAME)] SET  READ_WRITE
GO
ALTER DATABASE [$(DBNAME)] SET RECOVERY $(RECOVERYMODE)
GO
ALTER DATABASE [$(DBNAME)] SET  MULTI_USER
GO
ALTER DATABASE [$(DBNAME)] SET PAGE_VERIFY CHECKSUM 
GO
USE [$(DBNAME)]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [$(DBNAME)] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

/* Add SharePoint account as the database owner */
USE [$(DBNAME)]
GO
CREATE USER [$(SETUPUSER)] FOR LOGIN [$(SETUPUSER)]
GO
USE [$(DBNAME)]
GO
EXEC sp_addrolemember N'db_owner', N'$(SETUPUSER)'
GO

PowerShell Script

Finally, we need the PowerShell script. This walks through each line in the CSV file, reads the columns, and uses the Invoke-Sqlcmd cmdlet to run the .sql file - replacing the variables with the CSV column data:

$csvData = @(Import-Csv -Path C:\Scripts\SQL\Databases.csv)
foreach ($line in $csvData)
{
    $parameters = @()
    $line | Get-Member -MemberType NoteProperty | ForEach-Object {
       
        $parameters = $parameters + ($_.Name + "=" + $line.($_.Name))
    }   
    Invoke-Sqlcmd -InputFile "C:\Scripts\Create SharePoint Database.sql" -ServerInstance "SQLINSTANCE" -Variable $parameters
}

If you are using this method to pre-create SharePoint databases, don’t forget to apply the other recommendations listed in the Deploy by using DBA-created databases article.

You can also use the method of replacing variables shown in the PowerShell script above to manage any repeatable action in SQL – not just database creation. For example, you could keep a .sql file handy that adds an AD account as a SQL login and then use the Invoke-Sqlcmd command to add the SharePoint install account to SQL before creating any databases:

SQL File:

USE [master]
GO
CREATE LOGIN [$(USER)] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(USER)', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(USER)', @rolename = N'securityadmin'
GO

PowerShell script:

Invoke-Sqlcmd -InputFile "C:\Scripts\Add Account to Logins.sql" -ServerInstance "SQLINSTANCE" -Variable "USER=DOMAIN\SP_Admin"

48 comments:

  1. Hi Phil, great post!!
    It was really useful as a starting point to what I needed to do: to define some optimal settings on several SharePoint content database and to hand over to our DBA guys.

    Just one small addition: when creating bigger databases(e.g.: for document management systems), the script can take up several minutes to finish its execution. For that I would suggest when calling Invoke-Sqlcmd, to add 2 parameters:
    -ErrorAction Stop -QueryTimeout 600
    If not, it will timeout.
    Again, nice article, thanks for sharing & keep up writing!
    Regards, Reka

    ReplyDelete
  2. Excellent tip, Reka - thanks for sharing

    ReplyDelete
  3. Nice work Phil.

    I'm new to manual deployment of Sharepoint so, I would like to know, what's next?

    Once you have created the DBs, what do you do?

    Can you outline the procedure?

    Regrads
    Gilles

    ReplyDelete
  4. I found this more valuable as a way to variablize an SQL command than as something specific to SharePoint and DBA-created databases. Thanks for sharing!

    ReplyDelete
  5. The blog or and best that is extremely useful to keep I can share the ideas of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    animal jam | five nights at freddy's | hotmail login

    ReplyDelete

  6. شركة كشف تسربات المياه
    البيت السعيد
    شركتنا هي الأفضل دائمًا في مجال كشف تسربات المياه وعلاجها:
    شركة البيت السعيد شركة كشف تسربات المياه لديها فريق ذات كفاءة عالية للغاية، تم تدريبه على العمل في أصعب الظروف وأخطرها على الإطلاق، فهو مدرب على استعمال كافة أنواع المعدات الحديثة المستخدمة في كشف التسربات

    شركة كشف تسربات المياه
    البيت السعيد
    للاستفسار عن جميع خدمات البيت السعيد عزل مائي و عزل حراري و عزل اسطح و عزل خزانات و عزل فوم وجميع انواع العوازل و رش مبيد و مكافحة حشرات و تسليك مجاري و شركة كشف تسربات المياه
    و فحص فلل
    و صيانة مباني و ترميم منازل و نقل عفش و اثاث بالرياض يرجي الاتصال على 0543578920
    او زورو موقعنا على الانترنت


    http://www.elbaytelsaeed.com/2017/08/06/%d9%83%d8%b4%d9%81-%d8%aa%d8%b3%d8%b1%d8%a8%d8%a7%d8%aa-%d9%88%d8%b9%d9%88%d8%a7%d8%b2%d9%84-%d9%88%d8%aa%d9%86%d8%b8%d9%8a%d9%81-%d9%88%d9%86%d9%82%d9%84-%d8%a7%d8%ab%d8%a7%d8%ab-%d9%88%d8%b5%d9%8a/ 

    ReplyDelete
  7. Attend Talent Magnifier’s E-Accounts & Income Tax Corporate certification Practical Training in Delhi .And become a professional and experienced tax consultant will fluent and working knowledge in gst.
    for more details-

    Site

    ReplyDelete
  8. Become a professional SAP FICO Consultant by pursuing high-quality SAP FICO certification training course from talent magnifier. Take a demo class free in Talent Magnifier.

    https://www.talentmagnifier.com/sap-fico-certification-training-course/

    ReplyDelete
  9. _________

    It is great to have visited your website. Thanks for sharing useful information. And also visit my website about health. God willing it will be useful too

    Obat Tumor Jinak diatas Bola Mata
    Penyebab Benjolan dileher
    Obat Penghilang Nyeri Lutut
    Obat Penghilang Nyeri Pada Payudara
    Pengobatan penyakit Meningioma
    Obat Luka Bernanah bekas Caesar

    ReplyDelete
  10. Talent Magnifier advanced excel training courses or MIS Executive Training Certification-Enroll Now Free Demo Classes.
    For more info Visit us : Click Here

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. DIAL +1(844)443-2544 THIS IS FREE GMAIL SUPPORT PHONE NUMBER TO FIX ANY ERROR OR ISSUE IN GMAIL BY DIAL THIS 1844-443-2544 GOOGLE ACCOUNT HELPLINE SUPPORT PHONE NUMBER CALL ANY TIME 24*7 AND GET INSTANT SOLLUTION FOR ANY GOOGLE PROBLEM.

    ReplyDelete
  14. Switch version: Analogue. Quarta movement advancement. Nothing immune pearl very. Through putting up you're offer, You are investing buy them merchandise from the vendor if you're the being successful prospective buyer. You read and sign up for the worldwide transporting provider finer points clears in the most current display on the other hand bill. Importance expense in earlier times estimated have been susceptible to change within the add to you the greatest number of attempt quantities..

    Associated with rate possesses relevant traditions responsibilities, Income income tax, Broker good Maillot De Foot Pas Cher commissions. Until this number is cause to undergo change before make disbursement. The local surf forecast in an western european element tell you aside hummel trikots from british isles, Importance amazon müller trikot value-added Coach Outlet Online Store tax within this maillot foot 2018 acquisition certainly not recoverable.

    Near writing maillot de foot pas cher your individual estimate, You are investing in buy this dfb trikot müller skill model from owner if you are the Camisetas De Futbol Baratas succeeding at prospective buyer. You read and sign the world delivery overseas solution camisetas de futbol baratas requisites starts up in a newer pickup's wind shield properly bill. Significance penalty service fees during the past offered calendario de futbol have been susceptible to change so long as you raise you highest say enterprise.

    ReplyDelete
  15. Thanks a lot friends, because you have written this very good information
    I am really very helpful with this information
    I hope you can release other interesting information
    I will keep waiting for more recent information
    Greetings know and good luck always

    Cara Menurunkan Darah Tinggi Obat Luka Lambung Obat Lutut Bengkak Obat Diabetes Kering Cara Menyembuhkan Nyeri Panggul Obat Ayan Obat Jantung Bengkak Obat Kejang Obat Susah Kencing Obat Nyeri Perut Melilit

    ReplyDelete
  16. If you have a woman in your life, or you are shopping for yourself, then you probably know all about click here. Through the help of some well known celebrity endorsers and word of mouth among fashion conscious women, coach outlet online have become their most popular selling item. Here are just a few places where you can find cheap Coach Outlet Online Store. You can find a plethora of styles and colors of the famous coach outlet store online available to bid on and in some cases. Another fantastic place to look for deals is at site that acts as outlets for Coach Outlet Store Online. These coach factory outlet will be authentic, in brand new or nearly new condition, and will usually be offered at steep discounts. Because of the popularity and value of coach outlet store online, many folks have tried to duplicate them and sell their knockoffs at Coach.com. The coach factory outlet online is one of the most popular in designer purses today. For a lot of people even cheap cheap coach purses are a bit much for their tight budget. Before giving up on owning a designer purse, or settling for a knockoff designer coach outlet sale, it is worth your time to shop around a bit and see what you can find. If you do not want to pay full retail price for a coach factory outlet online login, you could pack up and take a little trip to the nearest coach handbags outlet. There are a lot of people who are wary of shopping on official coach outlet online for various reasons, but this site is one of the best places for grabbing great deals on cheap Coach Factory Outlet Store. With the recession on the horizon, everyone is looking for deals, and cheap coach factory website are no exception. coach factory online sale handbags are perhaps the most sought after of all designer Coach factory outlet website, due to their quality and style. official coach factory outlet online are in demand due this high quality. Flip though any tabloid or rag, and you will see the famous coach factory store online in all the spreads.

    ReplyDelete

  17. Contact With America Online Aol Email Support Phone NumberAol Email Customer Support Phone Number
    To fix Any Troubleshoot Error And Bugs With Expert technicicans
    Who Can Easily Fix All type Of Aol Email Issue

    ReplyDelete
  18. Connect to HP Laptop Technical Support Contact Number +1(888) 963-7228 for get instant service help. Are you Need HP Laptop Customer Service? Driver information, troubleshooting then you can solve the issues remotely by the experts.

    ReplyDelete
  19. This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around routine

    risa herbal
    penyebab sakit pinggang
    obat penyakit mata glaukoma
    obat gagal ginjal
    obat penebalan dinding rahim
    obat penghancur kanker
    obat varikokel

    ReplyDelete
  20. Numpang share Obat Amandel thanks for permission and I hope this can be usefull for every one

    ReplyDelete
  21. También puede visitar una tienda de ladrillo y mortero de Michael Kors o su sitio web y comprar directamente un bolso Michael Kors desde allí. Usar un bolso de Michael Kors les permite a los demás reconocer que el habitante urbano educado toma la moda realmente con seriedad. Los bolsos de hombro son particularmente refinados y elegantes.

    {Bolsas Michael Kors Precios | Bolsos Michael Kors Outlet | Michael Kors Rebajas}

    En vacker konstnärlig skapelse av vävt läder, som ger ett skalskaligt utseende - liknar en snakeskin eller fiskhud, linjer utsidan av påsen. Läderens bältros är små läderringar. Det finns också gyllene accenter på väskan. Slutresultatet är svagt liknar kedjepost.

    {Michael Kors Rea | Michael Kors Väska Rea | Michael Kors Plånbok}

    ReplyDelete
  22. Numpang share this info Obat Stroke and Obat Benjolan di Belakang Telinga and thanks for permission and I hope this can be usefull for every one

    ReplyDelete
  23. Given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

    Obat jantung koroner paling ampuh
    Obat kista bartholin agar cepat pecah

    ReplyDelete
  24. Hp printer error 49.4c02 stops all operations being performed on the printer and asks for restarting the computer.There are certain steps to fix this Hp printer error 49.4c02.Thus to Fix hp printer error 49.4c02 call Hp printer support at +1-888-678-5401

    ReplyDelete
  25. Sometimes while using internet on your system you might face error code 400 that implies Bad request.This error is the most common error over internet.Thus to fix Bad request error code 400 call Our technical support team at +1-888-678-5401

    ReplyDelete