Overview
This lecture covers how to connect to SQL Server using SQL Server Management Studio (SSMS), explains SSMS and authentication methods, and describes the process for accessing a database server.
Introduction to SQL Server Management Studio (SSMS)
- SSMS is a client tool used to connect to a SQL Server database server.
- The SQL Server database server stores data, tables, triggers, and related objects centrally.
- SSMS must be installed on your computer to connect to a database server.
Opening and Navigating SSMS
- Access SSMS from the Start menu under the relevant SQL Server version folder.
- On launch, the "Connect to Server" window appears for server connection details.
- Developers typically use SSMS from their machines to connect to a centralized database server.
Connection Configuration in SSMS
- Choose the server type, usually "Database Engine," when connecting.
- The server name can be the centralized server’s name/IP, or for local servers, use
127.0.0.1, local, or . (dot).
- SSMS and the SQL Server database engine are separate: SSMS is only the client.
Authentication Methods
- Authentication options depend on how SQL Server was installed: Mixed Mode (Windows and SQL Server) or SQL Server Authentication only.
- Windows Authentication uses your current Windows login for access.
- SQL Server Authentication requires entering a username (e.g., "sa") and password provided during SQL Server setup.
SSMS User Interface Basics
- The Object Explorer window on the left displays database objects and allows exploration.
- Click "New Query" to open a query editor window for writing and executing queries.
- The current database context (e.g., master) is shown at the top of the query window.
Databases in SQL Server
- System databases (master, model, msdb, tempdb) are installed automatically and required for SQL Server functionality.
- User-defined databases can be created through SSMS by right-clicking "Databases" and selecting "New Database".
Key Terms & Definitions
- SQL Server Management Studio (SSMS) — Client tool for managing and querying SQL Server databases.
- Database Engine — Core service for storing, processing, and securing data.
- Object Explorer — SSMS panel displaying databases and server objects.
- Authentication — The process of verifying user identity (Windows or SQL Server methods).
- System Databases — Pre-installed databases needed for SQL Server operation.
Action Items / Next Steps
- Practice connecting to SQL Server using SSMS with both authentication methods.
- Explore Object Explorer and identify system databases.
- Prepare for the next session on creating new user databases and writing queries.