Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral

 

At times it can be necessary to move the data and or log files from one location to another on the same SQL Server. There are two ways to go about doing this task, detaching the database from the SQL Server Instance, moving the files to the new location in the operating system, and then reattaching the database to the SQL Server Instance, and using ALTER DATABASE with the MODIFY FILE option to move the files through a metadata switch, taking the database offline, moving the file in the operating system and then bringing the database back online. Both accomplish the same task, but there are a number of reasons why the ALTER DATABASE method can make more sense for doing this kind of task.

Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral

Windows 7 and Windows Application Compatibility : Boot from Windows 7 VHD Boot without having any native Operating System

 

Boot from Windows 7 VHD Boot without having any native Operating System

VHD boot is a new feature of Windows 7 and can be used in very creative ways. If you have a need for Reimaging a machine (like a testing environment) back to its original configuration then booting from VHD seem to be a very easy way to do it.

Windows 7 and Windows Application Compatibility : Boot from Windows 7 VHD Boot without having any native Operating System

Microsoft SQL Server Development Customer Advisory Team – Mirroring SQL

 

To get more information on SQL Server database mirroring, check these links (Some of these were written for SQL 2005 but still apply to SQL 2008):

· Database Mirroring and Log Shipping Working Together: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Implementing Application Failover with Database Mirroring: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Database Mirroring Best Practices and Performance Considerations: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Database Mirroring FAQ: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

· Performance Boost for Database Mirroring: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx

· Troubleshooting Database Mirroring Deployment: http://msdn.microsoft.com/en-us/library/ms189127.aspx

· How to Avoid Orphaned Users With Database Mirroring:

http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!494.entry

Microsoft SQL Server Development Customer Advisory Team

Halving your delete times with large datasets

Here is info from MySpace and the SQL Performance Team on how to delete large ordered blocks of data.

create view v1 as (select top (10000) * from t1 order by a)

and we can delete the “top” rows using simply

delete from v1

The query plan for this delete is much simpler.

and the I/O and cpu statistics demonstrate the improvement:

Microsoft SQL Server Development Customer Advisory Team

Digital Volcano – Find and Replace multiple files

TextCrawler and Duplicate Cleaner are two useful tools for a DBA working with Reporting Services.

In combination with Reporting Services Scripter, TextCrawler allows you to perform comparisons of reports between servers (by changing the server name in the RDL) and to change the root path for folders in the rss scripts (provided the name is distinct).

Digital Volcano

BI-tch – Dropping the time in datetime

Sometimes door #1 isn’t the best option.

Option 3: DATEADD

DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)

Returns 2009-04-09 00:00:00.000 (shock horror!)

BI-tch

Andrew Fryer's Blog

 

To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:

• DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
• DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
• Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
• Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.
• Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
• Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
• Integration Services Samples – A few sample packages to start auditing and viewing lineage on.

Andrew Fryer's Blog

RamDisk for Performance and Security

This technology was around since the dawn of XT and could be valuable for improving SQL performance.

RamDisk and RamDisk Plus®
(a.k.a Ramdrive)

RamDisk and RamDisk Plus offer dramatic improvement in the storage performance of servers and applications running on Windows Server 2003, Windows XP, and Windows 2000. Intel’s IoMeter benchmark reveals that our ramdrives typically show a 50 times performance gain over a physical hard disk. In many instances, that translates into an improvement of 3 to 10 times in overall application speed.

RamDisk for Performance and Security