Preparing scripted setup of ArcSDE in SQL Server 2012

Question

Hello:

I'm setting up a test and development server for ArcSDE with SQL Server 2012. I'm also a total novice DBA. I want to use the SDE_SCHEMA model.

I do not want to use the default storage locations created when using the Create Enterprise Geodatabase tool.

Will the following script be sufficient to use as presetup for the Enable Enterprise Geodatabase tool and ensure that the sde login, user and schema have the required permissions and configuration for proper geodatabase administration?

Also when setting up a connection file to use with Enable Enterprise Geodatabase tool is it best to use sa or sde for username?

USE master;
GO
CREATE DATABASE DEVARCGISSDETEST
ON 
( NAME = DEVARCGISSDETEST,
    FILENAME = 'C:\MSSQLData\DEVARCGISSDE\DEVARCGISSDETEST.mdf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB )
LOG ON
( NAME = DEVARCGISSDETEST_log,
    FILENAME = 'C:\MSSQLLogs\DEVARCGISSDE\DEVARCGISSDETEST_log.ldf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB );
GO
ALTER AUTHORIZATION ON DATABASE :: DEVARCGISSDETEST TO sa;
GO
CREATE LOGIN sde WITH PASSWORD = '<!1Password!>', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
GO
EXEC master..sp_addsrvrolemember @loginame = N'sde', @rolename = N'processadmin'
GO
USE DEVARCGISSDETEST;
GO
CREATE USER sde FOR LOGIN sde;
GO
CREATE SCHEMA sde AUTHORIZATION sde;
GO
ALTER USER sde WITH DEFAULT_SCHEMA = sde;
GO
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO sde;
GO
CREATE FULLTEXT CATALOG SDE_DEFAULT_CAT AS DEFAULT AUTHORIZATION sde;
GO

Thanks.....

MarkWilloughby · Answer

Thanks for the tip re: using the sde account when creating the geodatabase content with the Enable Enterprise Geodatabase tool.

Still looking for feedback on the SQL script I'm using to create the sde login, user and schema.

AsrujitSengupta · Answer

Scripting database and geodatabase creation in SQL Server:
Link for 10.1: http://resources.arcgis.com/en/help/main/10.1/index.html#//002q000000rp000000

Link for 10.2/10.2.1: http://resources.arcgis.com/en/help/main/10.2/index.html#//002q000000rp000000

As William already specified, you need to use the "SDE" user to create an SDE-Schema geodatabase.
If the "SA" user is used, a DBO-Schema geodatabase would be created.

WilliamCraft · Answer

Hello:Also when setting up a connection file to use with Enable Enterprise Geodatabase tool is it best to use sa or sde for username?

I can't answer your first question regarding the script, but the answer to your second question about the user account is to use the SDE user.  Do not use the 'sa' account for things like this; it's just bad practice since the account is too powerful.  The Create Enterprise Geodatabase tool will actually create the SDE user if you pick the SDE_SCHEMA (which you said you are doing), so use that same SDE user when running the Enable Enterprise Geodatabase tool.