September 18, SQL Learning Basics 1

Database Management and Application

OLTP is a small management, and OLAP is a large-scale management

Development of content such as triggers

The database Management system, or DBMS, is a layer of data management software that is located between the user and the operating system, providing a way for users or applications to access the database, including database creation, querying, updating, and various database controls.

DBMS is always based on some data model, these models can be divided into hierarchical model, network model, relational model, object-oriented model, etc.

The Microsoft SQL Server 2008 System consists of 4 major parts. These 4 sections are referred to as 4 services, namely the database engine, Analysis Services, Reporting Services, and integration services. These services exist and apply to each other, and their relationship is 1-3.

The Microsoft SQL Server 2008 system provides two types of databases, the system database and the user database.

The system database holds system-level information for a Microsoft SQL Server 2008 system, such as system configuration, database properties, login accounts, database files, database backups, alerts, jobs, and so on. Microsoft SQL Server 2008 uses these system-level information to manage and control the entire database server system.

A user database is a database created by a user to hold user data and objects.

System database:

The master database is the most important system database that records all the information at the system level of the SQL Server, including server configuration information, login account information, database file information, SQL Server initialization information, and so on, which affect the entire SQL The operation of the server system.

The model database is a template database. The database stores the database objects and data that can be used as templates. When the user database is created, all the information in the template database is automatically copied to the user's new database, so that the newly created user database has the same object and related data as the model database in its initial state, thus simplifying the initial creation and management of the database.

MSDB is a database related to the SQLServerAgent service. The system database records information about jobs, alarms, operators, schedules, and so on, which can be used to automate the operation of the system.

Tempdb is a staging database that stores intermediate data or results that are used during a query. In fact, it's just a temporary working space for a system.

In the field of database technology, the application environment for organizing data according to the OLTP data storage model is called an OLTP environment. The OLTP data storage model is also known as an OLTP database.

The OLAP data storage model is distinct from the OLTP data storage model. Structurally, the common structure of an OLAP data storage model is a star-shaped structure or an avalanche structure. From the point of use, the main function of OLAP database is to improve the speed of data retrieval and analysis.

Sample database (user database)

AdventureWorks is not a system database, but a sample OLTP database. The database stores the business data of a hypothetical bicycle manufacturing company, indicating the scenarios of manufacturing, sales, procurement, product management, contract management, human resources management, etc. Users can use the database to learn SQL Server operations, or they can emulate the structure of the database to design a user's own database.

AdventureWorksLT is also a sample OLTP database, but its database objects and data are less than the AdventureWorks database and is a simplified, lightweight sample database. For beginners of database technology, you can learn from the AdventureWorksLT database.

AdventureWorksDW is an example OLAP database for online transaction analysis. Users can use the database to learn OLAP operations for SQL Server, or they can emulate the internal structure of the database to design their own OLAP databases.

SQL Server Configuration Manager

In the Microsoft SQL Server 2008 system, you can view and control the services of SQL Server through the Computer Management tool or SQL Server Configuration Manager.

By right-clicking a service name, you can view the properties of the service and start, stop, pause, and restart the appropriate service.

Using a camera, you can record all the processes of a scene and watch them again and again. You can use the SQL Server Profiler tool to complete this recording operation.

You can run SQL Server Profiler from the Tools menu in the Microsoft SQL Server Management Studio window.

In the Microsoft SQL Server 2008 system, not only does it provide a large number of graphical tools, it also provides a number of command-line utilities.

These command-line utilities include BCP, DTA, dtexec, DTUtil, microsoft.analysisservices. Deployment, nscontrol, osql, Profiler90, RS, rsconfig, rskeymgmt, SAC, Sqlagent90, sqlcmd, SQLdiag, sqlmaint, sqlservr, SQLWB, Tablediff and so on.

sqlcmd Utility

In a Microsoft SQL Server 2008 system, the database administrator, or DBA, is the most important role in the administration.

The DBA's goal is to ensure that the Microsoft SQL Server 2008 system runs properly and efficiently.

The DBA's work is also the busiest job, whether it's performance tuning or disaster recovery, without the DBA's support.

DBA's 12 tasks

Task one: Installation and configuration.

Task two: Capacity planning.

Task three: Application architecture design.

Task four: Manage database objects.

Task Five: Storage space management.

Task six: Security management.

Task Seven: Backup and recovery.

Task eight: Performance monitoring and tuning.

Task nine: Schedule the job.

Task 10: Network management.

Task 11: High Availability and high scalability management.

Task 12: Troubleshoot.

Application architecture design includes database design, application design, and corresponding technical architecture design.

Database design should consider the logical requirements of the database, how and how the database is created, the physical location of the database data files and log files, and so on. In general, after a successful installation of the Microsoft SQL Server 2008 system, you can create a database manually, based on your planned goals.

Application design should consider the choice of development tools, API technology, the combination of internal resources and external resources, the distribution of application architectures, etc. It is necessary to emphasize that in the application design, the DBA should work with the developer to ensure that they write optimized code and use the server's resources as much as possible.

The technical architecture design mainly includes the distribution of the presentation layer, the logical layer and the data layer.

Managing database objects is the most basic and important work for using a database. These objects include tables, indexes, views, stored procedures, functions, triggers, synonyms, and so on.

To complete the task of managing database objects, DBAs should be able to answer these questions as well.

What data should the system include?

How should this data be stored?

What tables should be created in the system?

Which indexes should be created in these tables in order to speed up the retrieval?

Should you create a view? Why would you want to create these views?

What stored procedures, functions, CLR objects should be created?

On which tables should I create the triggers? What actions should be created for the trigger?

Should you create synonyms?

September 18, SQL Learning Basics 1