Training
Certifications
Books
Special Offers
Community




 
Microsoft® SQL Server™ 2000 Resource Kit
Author Microsoft Corporation
Pages 1200
Disk 2 Companion CD(s)
Level Advanced
Published 03/28/2001
ISBN 9780735612662
ISBN-10 0-7356-1266-8
Price(USD) $69.99
To see this book's discounted price, select a reseller below.
 

More Information

About the Book
Table of Contents
Sample Chapter
Index
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books

 

Table of Contents


PART 1 INTRODUCING SQL SERVER 2000 AND THIS RESOURCE KIT 1
CHAPTER 1 Introducing the SQL Server 2000 Resource Kit 3
    Inside the Resource Kit 3
    Additional Sources of Information 10
        SQL Server 2000 Product Documentation 10
        SQL Server 2000 Internet Sites 11
    Conventions Used in This Resource Kit 11
    Resource Kit Support Policy 11
CHAPTER 2 New Features in SQL Server 2000 13
    Relational Database Enhancements 13
    XML Integration of Relational Data 18
    Graphical Administration Enhancements 19
    Replication Enhancements 20
    Data Transformation Services Enhancements 24
    Analysis Services Enhancements 25
        Cube Enhancements 25
        Dimension Enhancements 28
        Data Mining Enhancements 29
        Security Enhancements 31
        Client Connectivity Enhancements in PivotTable Service 32
        Other Enhancements 32
    Meta Data Services Enhancements 34
        Meta Data Browser Enhancement 34
        XML Encoding Enhancements 34
        Repository Engine Programming Enhancements 35
        Repository Engine Modeling Enhancements 37
    English Query Enhancements 40
    Documentation Enhancements 42
PART 2 PLANNING 45
CHAPTER 3 Choosing an Edition of SQL Server 2000 47
    Introduction 47
    SQL Server 2000 Server Editions Explained 48
        SQL Server 2000 Enterprise Edition 48
            Scalability Requirements 49
            Availability/Uptime 49
            Performance 49
            Advanced Analysis 50
        SQL Server 2000 Standard Edition 50
    SQL Server 2000 Editions for Special Uses 51
        SQL Server 2000 Personal Edition 51
        SQL Server 2000 Developer Edition 51
        SQL Server 2000 Evaluation Edition 52
        SQL Server 2000 Windows CE Edition 52
        SQL Server 2000 Desktop Engine 53
    Obtaining SQL Server 2000 54
    Conclusion 55
CHAPTER 4 Choosing How to License SQL Server 57
    Licensing Model Changes 57
    What is a Processor License? 58
    Upgrades 58
    Choosing a Licensing Model 59
        Mixed License Environments 60
        Licensing for a Failover Cluster Configuration 60
        Licensing for a Multi-Instance Configuration 60
            Licensing in Multi-Tier Environments (Including Multiplexing or Pooling) 61
        SQL Server 2000 Personal Edition Licensing 61
        SQL Server 2000 Desktop Engine Licensing 61
    Switching Licenses 62
CHAPTER 5 Migrating Access 2000 Databases to SQL Server 2000 63
    Migration Options 64
    Before You Migrate 64
    Migration Tools 65
        Upsizing Wizard 65
        SQL Server Tools Used in Migrations 66
            SQL Server Enterprise Manager 66
            Data Transformation Services (DTS) 66
            SQL Query Analyzer 67
            SQL Profiler 67
    Moving Data 67
    Migrating Access Queries 68
        Limitations in Upsizing Queries 69
        Migrating Access Queries into User–Defined Functions 71
        Migrating Access Queries into Stored Procedures and Views 71
            Converting Make-Table and Crosstab Queries 72
        Migrating Access Queries into Transact-SQL Scripts 73
    Additional Design Considerations for Queries 73
    Verifying SQL Server–Compliant Syntax 75
        Access and SQL Server Syntax 76
        Visual Basic Functions 78
        Access and SQL Server Data Types 79
    Migrating Your Applications 80
        Creating a Client/Server Application 80
            Converting Code 80
            Forms 81
        Optimizing the Application for the Client/Server Environment 81
        Optimizing Data Structure 82
CHAPTER 6 Migrating Sybase Databases to SQL Server 2000 83
    Why Migrate to SQL Server 2000? 83
    Understanding the Migration Process 86
    Reviewing Architectural Differences 87
    Migrating Tables and Data 90
    Reviewing the Differences Between Sybase T-SQL and Transact-SQL 91
        Transaction Management 91
            ROLLBACK Triggers 91
            Chained Transactions 91
            Transaction Isolation Levels 92
            Cursors 93
            Cursor Error Checking 93
            Index Optimizer Hints 94
            Optimizer Hints for Locking 94
            Server Roles 94
            Raising Errors 96
            PRINT 96
            Partitioned Tables vs. Row Locking 96
        Join Syntax 98
        Subquery Behavior 98
        Grouping Results 99
        System Stored Procedures 99
            DUMP/LOAD 100
    Understanding Database Administration Differences 101
    Migration Checklist 103
CHAPTER 7 Migrating Oracle Databases to SQL Server 2000 105
        Target Audience 105
    Overview 105
        SQL Language Extensions 106
        ODBC 106
        OLE DB 107
        Organization of This Chapter 107
    Architecture and Terminology 108
        Definition of Database 108
        Database System Catalogs 109
        Physical and Logical Storage Structures 110
        Striping Data 110
        Transaction Logs and Automatic Recovery 111
        Backing Up and Restoring Data 112
        Networks 113
        Database Security and Roles 114
            Database File Encryption 114
            Network Security 114
            Login Accounts 114
            Groups, Roles, and Permissions 115
            Database Users and the guest Account 115
            sysadmin Role 116
            db_owner Role 117
    Defining Database Objects 117
        Database Object Identifiers 119
        Qualifying Table Names 119
        Creating Tables 121
        Table and Index Storage Parameters 122
        Creating Tables With SELECT Statements 122
        Views 123
        Indexes 125
            Clustered Indexes 125
            Nonclustered Indexes 127
            Index Syntax and Naming 127
            Index Data Storage Parameters 128
            Ignoring Duplicate Keys 129
            Indexes on Computed Columns 129
        Using Temporary Tables 129
        Data Types 130
            Using Unicode Data 131
            User-Defined Data Types 132
            SQL Server timestamp Columns 132
        Object-Level Permissions 133
    Enforcing Data Integrity and Business Rules 134
        Entity Integrity 135
            Naming Constraints 135
            Primary Keys and Unique Columns 135
            Adding and Removing Constraints 136
            Generating Unique Values 138
        Domain Integrity 139
            DEFAULT and CHECK Constraints 139
            Nullability 140
        Referential Integrity 141
            Foreign Keys 142
        User-Defined Integrity 143
            Stored Procedures 143
            Delaying the Execution of a Stored Procedure 145
            Specifying Parameters in a Stored Procedure 146
            Triggers 146
    Transactions, Locking, and Concurrency 149
        Transactions 149
        Locking and Transaction Isolation 151
        Dynamic Locking 152
        Changing Default Locking Behavior 152
        SELECT…FOR UPDATE 154
        Explicitly Requesting Table-Level Locks 154
        Handling Deadlocks 155
        Remote Transactions 156
        Distributed Transactions 156
        Two-Phase Commit Processing 157
    SQL Language Support 157
        SELECT and Data Manipulation Statements 157
            SELECT Statements 158
            INSERT Statements 159
            UPDATE Statements 160
            DELETE Statements 162
            TRUNCATE TABLE Statement 163
            Manipulating Data in Identity and timestamp Columns 163
            Locking Requested Rows 164
            Row Aggregates and the Compute Clause 164
            Join Clauses 164
            Using SELECT Statements as Table Names 166
            Reading and Modifying BLOBs 166
        Functions 167
            Number/Mathematical Functions 167
            Character Functions 168
            Date Functions 169
            Conversion Functions 170
            Other Row-Level Functions 170
            Aggregate Functions 171
            Conditional Tests 171
            Converting Values to Different Data Types 172
            User-Defined Functions 174
        Comparison Operators 175
            Pattern Matches 176
            Using NULL in Comparisons 177
            String Concatenation 177
        Control-of-Flow Language 177
            Keywords 178
            Declaring Variables 179
            Assigning Variables 179
            Statement Blocks 180
            Conditional Processing 181
            Repeated Statement Execution (Looping) 181
            GOTO Statement 182
            PRINT Statement 182
            Returning from Stored Procedures 182
            Raising Program Errors 183
    Implementing Cursors 184
        Cursor Syntax 184
        Declaring a Cursor 185
        Opening a Cursor 186
        Fetching Data 186
        CURRENT OF Clause 187
        Closing a Cursor 187
        Cursor Example 187
    Tuning Transact–SQL Statements 188
    Using XML 190
    Using ODBC 190
        Recommended Conversion Strategy 191
        ODBC Architecture 191
        Forward-Only Cursors 192
        Server Cursors 193
        Scrollable Cursors 194
        Strategies for Using SQL Server Default Result Sets and Server Cursors 195
        Multiple Active Statements (hstmt) per Connection 196
        Data Type Mappings 196
        ODBC Extended SQL 198
        Outer Joins 198
        Date, Time, and Timestamp Values 199
        Calling Stored Procedures 199
        Native SQL Translation 200
        Manual Commit Mode 200
    Developing and Administering Database Replication 201
        ODBC, OLE/DB, and Replication 202
    Migrating Your Data and Applications 203
        Data Migration Using DTS 203
        Oracle Call Interface (OCI) 204
        Embedded SQL 205
        Developer 2000 and Third-Party Applications 208
        Internet Applications 209
PART 3 DATABASE ADMINISTRATION 211
CHAPTER 8 Managing Database Change 213
    Preparing for a Changing Environment 213
        Conflicting Goals 214
        Managing the Development Environment 215
            Development Database Process 215
            Control: Helping or Hindering? 216
            Duplication of the Production Database 219
            Security 219
            Using Command Line Scripts for Implementation 220
            Expecting the Unexpected During Implementation 224
        Managing the QA Environment 225
            Implementing in QA 225
            QA Administration 226
        Managing Production Implementations 227
            Owning the Change: Production vs. DBA 228
            When a Good Plan Comes Together 229
        Conclusion 231
            Further Reading 231
CHAPTER 9 Storage Engine Enhancements 233
        Storage Engine Enhancements 234
        Interacting with Data 237
            Reading Data More Effectively 238
            Concurrency 239
        Tables and Indexes 241
            In-Row Text 241
            New Data Types 242
            Indexes 242
        Logging and Recovery 244
            Recovery Models 246
        Administrative Improvements 249
            Dynamic Tuning 251
        Data Storage Components 252
            Files, Filegroups, and Disks 253
        Innovation and Evolution 254
CHAPTER 10 Implementing Security 255
    Introduction 255
    New Security Features 255
        Secure Setup 255
        C2 Security Evaluation Completed 256
        Kerberos and Delegation in Windows 2000 Environments 256
        Security Auditing 257
        Elimination of the SQLAgentCmdExec Proxy Account 258
        Server Role Enhancements 259
        Encryption 259
            Network Encryption Using SSL/TLS 259
            Encrypted File System Support on Windows 2000 260
            Server-Based Encryption Enhanced 260
            DTS Package Encryption 261
        Password Protection 261
            Backups and Backup Media Sets 261
            SQL Server Enterprise Manager 261
            Service Account Changes Using SQL Server Enterprise Manager 261
        SUID Column 261
    Security Model 262
        Authentication Modes 263
        Using SIDs Internally 263
        Roles 264
            Public Role 264
            Predefined Roles 264
            User-Defined Roles 266
            Application Roles 266
        Securing Access to the Server 269
        Securing Access to the Database 273
            User-Defined Database Roles 274
            Permissions System 276
            Granting and Denying Permissions to Users and Roles 276
            Ownership Chains 279
    Implementation of Server-Level Security 280
            Use of SIDs 280
            Elimination of SUIDs 280
            Generation of GUIDs for Non-Trusted Users 281
            Renaming Windows User or Group Accounts 281
            sysxlogins System Table 281
    Implementation of Object-Level Security 284
        How Permissions Are Checked 284
            Cost of Changing Permissions 285
            Changes to Windows User or Group Account Names 285
            sysprocedures System Table Removed 286
            WITH GRANT OPTION 286
            sysusers System Table 286
            sysmembers System Table 287
            syspermissions System Table 287
            sysprotects System Table 288
        Named Pipes and Multiprotocol Permissions 288
    Upgrading from SQL Server 7.0 289
    Upgrading from SQL Server 6.5 289
        Upgrade Process 289
            Analyzing the Upgrade Output 290
            Preparing the SQL Server 6.5 Security Environment 291
    Setting Up a Secure SQL Server 2000 Installation 292
        Service Accounts 293
        File System 295
        Registry 296
        Auditing 296
        Profiling for Auditing 297
        Backup and Restore 298
            Security of Backup Files and Media 298
            Restoring to Another Server 298
            Attaching and Detaching Database Files 300
        General Windows Security Configurations 300
            Additional Resources 301
CHAPTER 11 Using BLOBs 303
    Designing BLOBs 304
        BLOB Storage in SQL Server 304
        Learning from the TerraServer Design and Implementation 312
        BLOBs in Special Operations 315
    Implementing BLOBs 316
        BLOBs on the Server 318
        BLOBs on the Client 325
    Working with BLOBs in SQL Server 336


Next




Top of Page


Last Updated: Saturday, July 7, 2001