In the list below i have listed some of the preferences of SQL Server 2008, work that we come across we need to change, enabling or disabling these options or opportunities of SQL Server 2008.
- SQL Server Service must be started with the Local User Account, with particular accounts(SQL Server Agent, SSIS, Reporting Service, Analyse Service)
- sp_configure ‘show advanced options’, 1
shows to you 68 options to enable or disable
- SQL Server Enterprise & Developer support TDE
- BULK INSERT only imports large quantities of data.
- BCP data transfer in file import or export
- SSIS service is SQL Server Integration services 10.0
- SSIS can be stored Integration Service as file on File System and row in MSDB
- OPENROWSET & OPENDATASOURCE these are AdHoc Distributed query
- Peer-to-Peer replication topology has no filtering
- -m- SQL Server start in single user mode be a member of sysadmin role
- Size limit of XML data type is 2GB
- Import/Export Wizard allows up to level Database, Table, View
- Database Copy Wizard allows copying the database only at the level of database.
- Dynamic Management View DMV
- Management Data Warehouse MDW the real benefit is Reporting!
- DDL Data Definition Language you can create object table, constraints, stored procedure with CREATE, ALTER, DROP.
- DML Data Manipulation Language allow you to INSERT, UPDATE, DELETE, MERGE. DML statements commit as a whole or not at all.
- SQL Server Profiler provides recorded this video of the processes occurring in a certain time and that enables their analysis later.
- Query menu à Design Query in Editor enables the use of Query Designer to write T-SQL
- New feature in SQL Server 2008:
Compressed backups save time, disk space!
CDC Change Data Capture used to capture insertions, updates, deletes in a SQL Server table in database and place the changes in another table!
FILESTREAM limitations Database Mirroring, Database Snapshots, Native Encryption
Sparse Column allow for the optimized storage of NULL columns, TableàColumn PropertiesàIs Sparse = Yes/No
TDE encrypt the contents of your database and protection to the entire database, you need first to create master key and certificate!
High Availability Mirroring, Hot Add Memory, Hot Add CPU, New Failover clustering
Resource Governor help manage workload by limiting the resources available to a process.
DMF very similar to Windows Policy, components are policies, conditions and facets, ManagementàPolicy Management.
- Partitioning data: split rows – horizontal split columns – vertical
- When we are executing a bulk operation, is useful to disable Trigger.
- Save Points these markers allow you to rollback from the current location to the named save point.
I hope that you have find this list helpful and have it placed on your desk.