Moving System Databases

~~ Backup both System Databases and user Databases before starting ~~

Move TempDB

1: Find location of TempDB, Naming of files (This is more for peace of mind than a nessacry step)

use TempDB
go
sp_helpfile
go

2: Run the below code: Changes the location  SQL Server will look for the .mdf and ldf file locations

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\templog.ldf')
Go

Restart SQL Service (Below is the command to stop the Default Instance of SQL Server service. You can also do it via the SQL Server Configuration Manager or Services)

NET STOP MSSQLSERVER

Start the SQL Service up again and check the location of the files using sp_helpfile again to confirm the move worked.

70-450 Design for CPU, memory, and storage capacity requirements

Design for CPU, memory, and storage capacity requirements:
This objective may include but is not limited to

RAID:
http://en.wikipedia.org/wiki/RAID

Calculating table size:
http://msdn.microsoft.com/en-us/library/ms189124(v=SQL.100).aspx

IO throughput & Transaction per second:
Linchi Shea SQL Server Backup IO Performance
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/30/sql-server-backup-i-o-performance.aspx
Brent Ozar - SAN Best practices
http://www.brentozar.com/sql/sql-server-san-best-practices/
Jose Barreto's Blog - IO Performance
http://blogs.technet.com/b/josebda/archive/2009/03/31/sql-server-2008-i-o-performance.aspx
Rod Colledge - High Performance Storage systems for SQL
http://www.simple-talk.com/sql/performance/high-performance-storage-systems-for-sql-server/
Brent Ozar - SAN Performance Tuning with SQLIO
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

Data compression:
http://www.sqlservercentral.com/articles/SQL+Server+2008/65292/
http://sqlblog.com/blogs/linchi_shea/archive/2008/06/20/sql-server-2008-data-compression-impact-of-data-distribution.aspx
http://www.sql-server-performance.com/articles/dba/Data_Compression_in_SQL_Server_2008_p1.aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/11/12/types-of-data-compression-in-sql-server-2008.aspx

Non-uniform memory access (NUMA):
http://msdn.microsoft.com/en-us/library/ms180954.aspx
http://msdn.microsoft.com/en-us/library/ms178144.aspx
http://msdn.microsoft.com/en-us/library/ms345357.aspx
http://blogs.msdn.com/b/manisblog/archive/2007/09/17/scale-up-with-sql-server-2005-sql-server-2008-numa.aspx
http://blogs.technet.com/b/beatrice/archive/2008/04/21/to-numa-or-not-to-numa.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/06/28/how-to-set-soft-numa-for-sql-server-2008-r2.aspx

Tempdb capacity:
http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://www.mssqltips.com/tip.asp?tip=1432
http://sqlcat.com/whitepapers/archive/2007/11/20/tempdb-capacity-planning-and-concurrency-considerations-for-index-create-and-rebuild.aspx
http://sql.dotnetbob.com/?p=140
http://blogs.technet.com/b/josebda/archive/2009/04/03/sql-server-2008-tempdb.aspx
http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

Design SQL Server instances:
This objective may include but is not limited to:

Instance configuration:
http://msdn.microsoft.com/en-us/library/ms143531.aspx

Surface area configuration:

CPU affinity:
http://msdn.microsoft.com/en-us/library/ms187104.aspx
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/85a21a35-99c2-4633-912e-33f5ca1406b1
http://msdn.microsoft.com/es-es/library/ms187104.aspx

Memory allocation
http://msdn.microsoft.com/en-us/library/ms178145.aspx
http://technet.microsoft.com/en-us/magazine/dd370685.aspx
http://msdn.microsoft.com/en-us/library/ms190673.aspx
http://www.mydigitallife.info/2008/06/07/optimize-sql-server-2000-2005-or-2008-in-large-ram-system-by-locking-pages-in-memory-and-awe/
http://netindonesia.net/blogs/kasim.wirama/archive/2009/03/17/sql-server-2008-administration-best-practice.aspx

Max degree of parallelism (MAXDOP):
http://msdn.microsoft.com/en-us/library/ms181007.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/24/sql-university-parallelism-week-introduction.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/26/sql-university-parallelism-week-part-2-query-processing.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/28/sql-university-parallelism-week-part-3-settings-and-options.aspx

Collation
http://msdn.microsoft.com/en-us/library/ms144260.aspx
http://msdn.microsoft.com/en-us/library/ms144250.aspx

Design physical database and object placement.
This objective may include but is not limited to:

Heap and Index Placement:
http://blog.sqlauthority.com/2008/03/30/sql-server-introduction-to-heap-structure-what-is-heap/

http://searchsqlserver.techtarget.com/tip/Clustered-and-non-clustered-indexes-in-SQL-Server

Filestream:
http://technet.microsoft.com/en-us/library/bb933993.aspx
http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx
http://www.sql-server-performance.com/articles/dba/Configure_Filestream_in_SQL_Server_2008_p1.aspx

Summary:
Filestream data is backed up when the database is backed up
DB CANNOT use mirroring when Filestream is enabled on it
Snapshots DO NOT snapshot the Filestream data

Filegroups:
http://msdn.microsoft.com/en-us/library/ms179316.aspx
http://www.sql-server-performance.com/tips/filegroups_p1.aspx

Partition Placement:
http://sqlblog.com/blogs/erin_welker/archive/2008/02/10/partitioning-enhancements-in-sql-server-2008.aspx
http://www.sqlservercentral.com/articles/partition/64740/
http://blogs.technet.com/b/josebda/archive/2009/03/19/sql-server-2008-partitioning.aspx

Full Text Catalog
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
http://www.sql-server-performance.com/articles/per/full_text_search_2008_p1.aspx
http://msdn.microsoft.com/en-us/library/ms142571.aspx
http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx

Design a migration, consolidation, and upgrade strategy:
This objective may include but is not limited to:

Multi-instance Considerations:
http://www.mssqltips.com/tip.asp?tip=1048

SQL Server version upgrade:
http://msdn.microsoft.com/en-us/library/ms143393.aspx

Instance and database collation:
http://msdn.microsoft.com/en-us/library/ms179254.aspx

Server-level and instance-level objects,

???

Service pack application

???