Administering Your Microsoft SQL Server Geodatabase

1
Administering Your Microsoft SQL Server
Geodatabase

• Shannon Shields
• Chet Dobbins

2
Agenda

• News since the last UC
• How do I
• Configure SQL Server to support geodatabases?
• Create geodatabases?
• Control access to my data?
• Choose a spatial data storage option?
• Make sure that my data is safe?
• Maintain good performance?

3
News

• ArcGIS and Microsoft changes since last year

4
Coming at 10.3
5
10.1 users

• SQL Server 2012
• SQL Server 2012 Support Patch
• User names containing dot (.) or hyphen (-)
• SQL Server User Names With Special Characters
  Patch
• Both patches require SP1

6
How do I?

• Common questions when working with SQL Server
  databases and geodatabases

7
How do I configure SQL Serverto support
geodatabases?
8
How do I configure SQL Server to support
geodatabases?

• Install a supported version of SQL Server
• Microsoft SQL Server database requirements for
  ArcGIS 10.2
• Must use a Case-Insensitive (CI) collation
• Can use Windows or Mixed-mode authentication
• SQL Server Browser not required
• Must provide static TCP port on connection

9
What is the SQL Server Native Client?

• Microsoft stand-alone DLL
• Required for connections to SQL Server
• Install on every single client
• Must be same or newer version than SQL Server
• Microsoft ODBC Driver 11 for SQL Server
• Support coming at 10.3

10
SQL Server Native Client

• Demo

• Chet Dobbins

11
How do I create geodatabases?
12
Databases and Geodatabases

• A database is a SQL Server object
• There can be many per SQL Server instance
• A geodatabase is an ArcGIS construct hosted in a
  database
• One allowed in each database
• Options for creating geodatabases
• Use a GP tool to create a new geodatabase from
  scratch
• Use a GP tool to create a new geodatabase in an
  existing database

13
Creating a geodatabase

• Demo

• Chet Dobbins

14
Choosing a geodatabase schema

• DBO
• Owned by the DBO built-in user
• Multiple logins can be DBO
• Easier to use Windows authentication for
  administrator

• SDE
• Owned by a database user (SDE)
• SDE user needs fewer permissions than DBO
• No geodatabase users need to be DBO

15
Special cases

• Use Enable Geodatabase command when
• DBO is database owner, not sysadmin
• SDE user, no sysadmin
• Windows-authenticated SDE user

16
Points to remember

• Use GP Tools to create geodatabases
• Default size of 500MB data file 125MB logfile
• More control over storage?
• Use SQL Server tools to create database first
• Enable geodatabase tool
• Create a geodatabase in an existing database,
  without sysadmin privileges
• Do not rename a database that contains a
  geodatabase

17
How do I control access to my data?
Access to SQL Server objects are managed with
permissions granted to logins, users and roles
Administering Your Microsoft SQL Server
Geodatabase
18
SQL Server Principals

• Logins Authentication
• Who is connecting?
• Users Authorization
• What can this person do in the database?
• Schemas Containers
• What are logical groups of database objects that
  should be managed as a whole?

19
SQL Server Instance
Users
Logins
20
User-schema relationship

• For users that create data, ArcGIS requires that
• user name default schema name
• Not a SQL Server rule
• Users that are DBO all create data in the DBO
  schema
• Data readers editors do not need a same-named
  schema

21
Limit Permissions for Most Users
Admin
22
Who is DBO?
Sysadmin fixed-server role members are DBO in
every database
Database owner Is DBO in single database
Db_owner role members are NOT DBO Have DBO-like
permissions
23

• Demo

Managing permissions
Chet Dobbins
24
Permissions needed to read, edit or create data
Task Required permissions
Creator CREATE TABLE, CREATE PROCEDURE and CREATE VIEW
Editor SELECT, INSERT, UPDATE and DELETE on objects plus EXECUTE on stored procedures used to generate sequential values
Reader SELECT on tables

• Needed to create versioned or archived views
• Including feature, SDEGEOMETRY, versioning and
  archiving support tables, if present

25
Permissions needed to manage users
Task Required permissions
Create geodatabase user Membership in sysadmin fixed server role
Create database role CREATE ROLE
Compress geodatabase SDE-schema SDE user, DBO user or member of DB_OWNERS database role DBO-schema DBO user or member of DB_OWNERS database role
Kill geodatabase connections Membership in the processadmin fixed server role, and granted VIEW DEFINITION database permission
View all geodatabase users VIEW DEFINITION database permission
26
Permissions needed to manage databases
Task Required permissions
Create enterprise geodatabase Membership in sysadmin fixed server role
Upgrade geodatabase DBO user in database OR Member of DB_OWNERS fixed database role
User privileges for geodatabases in SQL Server
27
Password Policies

• SQL Server uses Windows password policy
• Password expiration rules
• Complexity rules for strong passwords
• Can be disabled
• Security policy is set on the domain or local OS
• Policies may vary from site to site

28
User-schema with Windows groups

• A Windows group can be added as a login
• Associate the login with a database user
• Do not create a matching schema
• For logins that create data, SQL Server
  automatically creates matching user and
  schemaRecommendation Data should be created
  through explicit Windows or SQL Server login
• Use group logins for data readers or data editors

29
Points to remember

• Creating a user does not give access to data in
  the database
• It must be granted by the data owner
• ArcGIS tools manage permissions on all parts of a
  feature class
• Creating a user with the Create User tool will
  grant permissions sufficient for creating data

30
How do Ichoose a spatial data storage option?
31
Three spatial data storage options
Similar characteristics
Access using T-SQL
32

• Demo

Spatial data storage
Chet Dobbins
33
Planar measurement
34
Spherical measurement
35
Points to remember

• Three storage types are available SDEBINARY,
  Geometry and Geography
• In Geography, calculations are done using Great
  Ellipse line interpolation, while the others use
  Cartesian
• SQL Server manages spatial indexes on Geometry
  and Geography
• Microsoft spatial data types provide SQL access
  to spatial data

36
How do Imake sure my data is safe?
37
BACKUP YOUR DATA NOW
38
How do I make sure that my data is safe?

• Backups allow you to recover from
• Media failures
• User errors
• Hardware failures
• Natural disasters
• Also, backups can be used for
• Copying or moving data between servers
• Setting up database mirroring or AlwaysOn
• Use BACKUP command or Backup task in Management
  Studio

39
Points to remember

• Backups are the only way to reliably protect your
  data
• Decide how much time you can afford to lose when
  disaster strikes and data must be restored
• Create a restore plan that will achieve that goal
• Create a backup plan that supports your restore
  plan
• Implement your plan
• Test your recovery plan regularly by using real
  backup media to restore to a system capable of
  being used in production

40
How do Imaintain good performance?
41
How do I maintain good performance?

• Standard maintenance
• Reconcile/Post/Compress
• Rebuild Indexes
• Calculate Statistics
• Layer scale dependencies
• Spatial Index
• Is it being used?
• Manual-grid vs autogrid

42

• Demo

Spatial Indexes
Chet Dobbins
43
Thank you

• Please fill out the session evaluation
• Wednesday July 16 Offering ID 1280
• Thursday July 17 Offering ID 1376
• Online www.esri.com/ucsessionsurveys
• Paper pick up and put in drop box

44
(No Transcript)