Data Types:
- Filestream - data is actually stored in NTFS NOT in SQL. SQL provides the filestream to access the data. For those that don't have experience programming or with file streams: a when not using file stream, the file must be loaded from the database into SQL memory, then served to the client. If the file is large, this consumes a great deal of memory. With filestreams we essentially stream the file to the client as we can - minimizing the strain on memory. Research shows that > 1 MB is more efficient in NTFS. Also NTFS doesn't share the SQL 2 GB Blob limit. Source
Performance / Monitoring
- Data Collectors can be used to collect data from several sources, for example if you want to correlate performance issues it would be helpful to see what TSQL statements are running (otherwise you would use SQL Profiler), blocking and deadlock (otherwise you would use SQL Profiler or Dynamic Management Views), and system counters like cpu, memory, disk (System Monitor). Using Data Collectors you can capture all of these and correlate the data to determine when the server is slow, what query is running, and why.
- Affinity - is the coupling of a thread to a processor. From an OS perspective - it is ideal to be able to move threads from one processor to another as the OS sees fit. So from an OS perspective affinity is a bad thing. So why would we have affinity - the SQL server can recognize that the data required by the thread is also going to be needed by other threads. So it can queue them together using affinity. When affinity is not set - the OS - not knowing better - will move the thread - then the required data (which is already in the preferred processor cache) will have to also be loaded into that processor's cache. Source 1, 2
- Extended Events - ...
- Partitioning - Vertical partitioning - think one-to-one relationship - which allows us to move a group of columns to another table. This can be very beneficial if we have a group of columns that are often needed together but not very often. Now the queries that happen more often will not have to include these columns (by not joining to this table). Horizontal partitioning - think union - move certain rows to another table (or filegroup). This is often done by moving records(rows) older than X (3 months) to a History/Archive table. This can greatly improve performance as indexes and the like don't have to reconstruct for every record but only those in the current (within 3 months) data. What is even cooler, is that this can be done at a lower layer by using file groups - so that to the client (user / application) it is just one table, but under the covers, using a partition function, old data goes to one file group while new data goes to another file group. Source
High Availability / Disaster Recovery
- Log shipping - allows you to transmit your log (record of every transaction) to another instance - likely far away over serial link - if the primary server goes down - the data is not lost and clients can be pointed to this backup and resume operations
Security
- Trustworthy - Database option - while a database is offline - someone could tamper with the database (ie, execute as SA, etc.). This trustworthy flag allows you to limit what the database can do. By default it is off and the database should not need trust beyond its scope. Source
- EXTERNAL_ACCESS vs SAFE - SAFE is more restrictive as it doesn't allow you to be able to access external like files, networks, environment variables, registry. Source
