What does SQL Server Management Studio mean?

Cortex User Group Meeting

Portland – 2012

Todd “The QuadFather” Stabelfeldt206-794-1524

Todd@SolutionsByC4.com

2 quads walk in a bar….

Want to see an impression?

• Suggestions from the front line• GUI Overview• Basic Configuration• Basic Backup & Restore Process• Security Overview• Maintenance Plans• Tips & Tricks

Our Agenda

Suggestions from the Frontline • Say goodbye to Windows 2003!

o Be sure to budget for a Windows 2008 server in 2013

• Confirm appropriate vendor approved service packs & Windows Updates are applied to the server

• Discuss and know your licensing agreements(upgrades)

• Build & understand the plan for upgrading your existing equipment

• Your SQL Server is key, make sure it is –NOT – a Domain Controller, Dynamics, Exchange, FAX, Timesheet, public, or Cortex competitor server

Overview• Location of query tool, stored procedures (programmability), etc.

• How the windows work

• Using the Object Explorero Tab navigationo Using back / forward buttonso Need to use when viewing SP due to amount of items

Basic Configuration• “Mixed Mode” authentication

• Let SQL manage memory

• Individual databases should have compatibility set to level 100o Reporting services will not work on less than 90

SQL 2000 = 80SQL 2005 = 90SQL 2008 = 100

Backup and Restore• Close all windows prior to performing task(s)• Change filter “Files of type” when browsing to see all files

Note: refreshes list so do this first• Use following naming convention when backing up

Name_initials_PreActionTakingPlace• When restoring a database be sure to:

o Check Restore box when performing a restoreo Check Overwrite the existing database under options

Security [Skip slide if NOT comfortable]

• Security Overviewo Windows Server

Server (This Computer) Network (Domain ONLY, current version of Dynamics does NOT support Workgroups)

o SQL Server Server access (using management studio, creating maintenance plans, etc.) Global database(s) access Individual database access Individual object access i.e. Stored Procedures & Views

• Servers running Dynamicso Server Authentication must be in “SQL Server and Windows Authentication mode”o Credentials are individual logins rather than group(s) – More maintenance during initial setup & staff hire / fire

• When creating logins be sure to understand key areas of accesso Server Role – Level of access to Management Studio

Creating plans, adding new users, etc.o User Mapping – Per database(s)

Individual database access with appropriate rights selectedo Object Access

Views, Stored Procedures, etc. may need certain access

Maintenance Plans•Visio look and feel

•Use wizards when creating plans

• A good method to learning the new GUI

Reports•DB has to be in compatibility 90 or higher

•Disk usage and the activity monitor is what I use the most

Tips and Tricks•Databaseso Right Click for New Query

•Querieso Intelisense (sp) when writing querieso Toolbar for options, results format, comments

•Tables / Stored Procedureso Right Click for script optionso Right Click for editing table, only does 200 by default

Questions?