SQL Server开发者入门指南

5星 · 超过95%的资源需积分: 0 182 浏览量更新于2024-07-22 1 收藏 25.6MB PDF 举报

"Apress Beginning SQL Server for Developers 2014 是一本面向开发人员的SQL Server入门指南，涵盖了从安装到高级特性的广泛主题。" 在本书中，作者深入浅出地介绍了SQL Server的基础知识，帮助读者快速上手。以下是书中的关键知识点概览： 1. **概述与安装** (Chapter 1: Overview and Installation)：这部分内容将引导读者了解SQL Server的基本概念，包括其在软件开发中的角色，以及如何安装和配置SQL Server环境。 2. **SQL Server Management Studio** (Chapter 2: SQL Server Management Studio)：SSMS是管理和操作SQL Server的主要工具。此章节会介绍如何使用SSMS进行数据库管理、查询编写、性能监控等任务。 3. **数据库设计与创建** (Chapter 3: Database Design and Creation)：本章涉及数据库设计原则，如正常化、范式，以及如何创建新的数据库和表。 4. **安全与合规** (Chapter 4: Security and Compliance)：讲解SQL Server的安全特性，包括用户权限管理、角色、登录认证、加密，以及如何满足企业级别的数据安全和合规性要求。 5. **定义表格** (Chapter 5: Defining Tables)：详细阐述如何定义表结构，包括数据类型、主键、外键、约束以及列属性。 6. **创建索引与数据库建模** (Chapter 6: Creating Indexes and Database Diagramming)：介绍索引的作用、类型（如聚集与非聚集索引）以及如何创建和优化它们。同时，还涉及数据库建模工具的使用。 7. **内存中表格** (Chapter 7: In-Memory Tables)：讨论SQL Server的In-Memory OLTP技术，提高数据处理速度的方法，以及如何迁移和管理内存中表。 8. **数据库备份与恢复** (Chapter 8: Database Backups and Recovery)：涵盖数据库备份策略，如完整、差异和事务日志备份，以及如何执行恢复操作以确保数据安全性。 9. **数据库维护** (Chapter 9: Database Maintenance)：包括计划维护任务，如索引碎片整理、统计更新和数据库收缩，以保持数据库的最佳运行状态。 10. **数据插入、删除与事务** (Chapter 10: Data Insertion, Deletion, and Transactions—Disk-Based)：讲解如何插入、更新和删除数据，以及事务处理的概念和ACID属性。 11. **从磁盘基表中选择和更新数据** (Chapter 11: Selecting and Updating Data from Disk-Based Tables)：介绍SQL查询语言（SQL）的基本语法，包括SELECT、UPDATE和DELETE语句。 12. **处理内存中表格** (Chapter 12: Working with In-Memory Tables)：更深入地探讨如何在内存中表格中进行数据操作和优化。 13. **构建视图** (Chapter 13: Building a View)：解释视图的概念，如何创建和使用视图来简化查询和提供数据抽象。 14. **存储过程、函数与安全性** (Chapter 14: Stored Procedures, Functions, and Security)：涵盖存储过程和函数的编写，以及如何通过它们来增强安全性。 15. **原生编译的存储过程** (Chapter 15: Natively Compiled Stored Procedures)：讨论SQL Server的原生编译存储过程，提高性能和响应时间。 16. **其他高级主题** (Chapter 16: Es)：可能包含更多的高级特性，如触发器、XML支持、大数据处理等，或者特定场景下的最佳实践。这本书全面覆盖了SQL Server的基础到进阶知识，适合初学者和有一定经验的开发者提升技能。通过学习，读者将能够熟练掌握SQL Server的使用，有效地设计、管理和优化数据库系统。

CHAPTER 1 ■ OVERVIEW AND INSTALLATION

• Master Data Services (MDS): If you have multiple servers used within your organization for

different data repositories, for example, one database for clients, one database for orders,

another database for delivery information, then by using MDS you can bring the data together

in one master data hub. Each of the databases has a valid use in its own right and does belong

as a separate entity; however, an organization’s management or decision makers may need to

see the whole picture to assess how the company is performing.

The locations shown in Figure 1-5 demonstrate the likely setup on a server although if you have a local machine

with multiple drives, placing the data files away from the main system hard drive is preferable. The instance root

directory folder in this example is named based on the next step in the setup process; however, I am using just the

C: drive as this is the likely setup you will be using.

Of these components, Replication, Full-Text Searching, Analysis Services, SQL Server Data Tools, and Master

Data Services fall outside the scope of this book, and I touch upon Integration Services only as mentioned.

Note ■ At this point, SQL Server no longer has the option to install the sample databases. You can download the

samples from www.codeplex.com/SqlServerSamples.

Naming the Instance

After a final check that nothing will block the installation via the Feature Rules check, you are ready to start defining

the the options selected earlier.

It is possible to install SQL Server more than once on one computer. This could happen when you have a

powerful server that has enough resources such as memory and processor to cope with two or three different

applications running. ISPs will have this setup when you request SQL Server, as this is their and your cheapest option

and allows SQL Server to be easily maintained by them. These different applications may be required to have their

own SQL Server databases. Each install is called an instance. You are now at the stage that you can name the instance

of the install. Each instance must have a unique name attached to it, although “no name,” known as a default instance,

is also classified as a unique name.

Naming instances is important as the first step to organizing your environments. For example, you may have

an instance for development, another instance for system testing, and finally one for user testing. It is bad practice

to share production server hardware with anything but production databases. If you share your hardware and if you

have a rogue development action that occurs and crashes the server, you will stop production from running. Although

you would have to make this decision at the start of the install process you are currently going through, it is a useful

second reminder here when naming the instance to give some indication of what environment it is for. I tend to use

DEV1, STG1, UAT1, and PRD1. The reason for the number is to cater for the scenario for replication, if you split audit,

archiving, live data, and so on.

The default instance is available as an option, which is what is selected when you are not giving the install a

specific name. You should avoid this, as it gives you an installation with no name and therefore no hint as to its use.

Even if you believe this will be your only instance or that while you are learning and a name is not required, it is good

practice to name the instance as shown in Figure 1-6. Choose the name you wish to use, and then click Next. Once you

have instances installed on the server and you complete further installations, they will be listed here. You can also see

the path detailed for each of the directories for the three services selected in the previous step if you chose them. Click

Next. The next screen checks the amount of free space on the selected drive. Click Next.

CHAPTER 1 ■ OVERVIEW AND INSTALLATION

Service Accounts

SQL Server and other services, as defined in the Feature Selection screen (shown in Figure 1-5), require you to log on

to Windows before starting, just as you need to log on to Windows before using your system. SQL Server, Reporting

Services, and so on can run without you or anyone being logged in to the computer the install took place on. They can

run just so long as the computer has fired up successfully. This is normal when SQL Server is installed on a server that

is held in a remote location like a server room.

When the computer boots up and loads Windows, not only will Windows be running but also there will be

processes that run in the background before you or any other user has logged in to Windows. The ability to run

services with no user-based account logged in to Windows is the behavior that is required for SQL Server. SQL Server

needs to use a system-based Windows account.

Nothing can run on Windows without having some sort of login. If you do log in to the computer with your

Windows account while working through this book, you can use this Windows user ID for SQL Server to also log in

and start the required services manually. However, if you have your SQL Server running on a separate machine such

as a server in a specialized server room, at your client’s location, or at your ISP, you will not be around to log on to that

machine every time SQL Server is required. In that situation, it is necessary to use a Windows service account, which

is similar to how your virus checker runs without being logged in with your own account.

Each built-in service account, as shown in Figure 1-7, has special rights and privileges within Windows Therefore,

choosing the correct service account for the correct SQL Server service is important. If you are on a network domain,

not normally found in the home environment, it is possible for the domain administrator to create domain accounts

that can be used as service accounts, and this is the option to choose when there is a network domain. For the

moment, although not the best option within a production environment, you will use the built-in service accounts.

In a networked environment, it is best to create new service accounts for SQL Server. It is better and more normal

practice for each instance to have new, specific Windows system accounts so that if an account becomes locked, it

does not affect every SQL Server instance installed.

Figure 1-6. Naming the install instance

CHAPTER 1 ■ OVERVIEW AND INSTALLATION

It is also necessary to define a SQL Server administrator account. This is a special account that you can use to

log on in dire emergencies, such as when SQL Server refuses connections. This special account allows you to log on,

debug the situation, and get SQL Server back up and running. Normally, this administrator account would be a DBA

specific account ID so that any changes that had to be completed would leave an audit trail, but for now, use the

account you have used to log on to your computer.

You will also see a similar screen for Analysis Services if you selected Analysis Services from the features list, and

the settings are the same.

Defining the Data Directories

The Data Directories tab, as shown in Figure 1-9, is where you define where SQL Server stores its data, backup

directories, and temporary database by default. It is normal practice to hold the data on a different disk drive to the

Windows system drive, which is normally the C: drive. For each instance, you define where the relevant folders should

reside. As stated earlier, you can have several installations on one physical or virtual server, and it is not uncommon

for a physical or virtual server to hold more than one production installation of SQL Server. For example, there could

be an installation for accounts, another for product control, and so on. Each instance would have its data held in a

different data directory. This includes any temporary databases that are created and any log files that are generated,

so although the physical server is shared, the installation is isolated. A virtual server is similar to a physical server,

but it can be one large server that has been split, quite often using software, into multiple servers. This is the method

used by many companies, including Microsoft on its Azure platform. The virtuality is dealt with by the Windows

engineering teams rather than the DBMS engineering teams. As you are dealing with only one instance within this

book, leave these settings as they are.

Figure 1-8. Authentication choices, including the SQL Server administrator account

剩余669页未读，继续阅读

DoomLord

粉丝: 114
资源: 1318

SQL Server开发者入门指南

apress.Beginning.SQL.Server.2012.for.Developers.3rd.Edition.May.2012

Apress.Beginning.SQL.Server.2008.for.Developers.From.Novice.to.Professional.Jul.2008.pdf

Beginning SQL Server for Developers(Apress,4ed,2014).

Apress - Beginning SQL Server 2008 for Developers (Jul 2008)

Apress - Beginning Sql Server 2005 For Developers {From Novice To Professional

Apress Beginning SQL Server 2012 for Developers 3rd Edition

Beginning SQL Server 2012 for Developers, 3rd Editiona

Sql server相关资料

SQL Server 2012 开发者入门经典 第3版 (英文版)

Beginning ASP.NET 4.5 Databases

最新资源

SQL Server 2012 开发者入门经典第3版 (英文版)