A lot of people sent me emails asking for a way to get ahold of Quest Software’s new DMV poster.  The poster shows all of the major DMVs in SQL Server 2005/2008, what they’re used for, and how to query them.

You can get the poster (for free, of course) by signing up for and attending the accompanying webcast about DMV’s that I’m doing next Thursday, July 10th.  If you don’t know much about Dynamic Management Views (DMVs), then you’ll want to attend this informative webcast with me and my PASS cohort Tom LaRock.  Get all the details about the webcast at http://www.quest.com/backstage/pow.aspx.

I hope this helps,

-Kevin

The easiest way to do this would be to use the SQLCMD command-line utility to export a query or query file result set, changing the column separator value to a comma.

SQLCMD -S MyInstance -E -d sales -i query_file.sql -o output_file.csv -s ,

Look at the hyperlink listed above for more SQLCMD options that can make automating many of your everyday tasks simple.

You want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint.

From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

From the Oracle side you might want to have a look at the Oracle Migration Workbench.

While I’m recovering from surgery, I’ve been thinking about what makes a technology poster worth putting up on your wall?

As I walked around TechEd last week, there was no shortage of posters available for all sorts of technologies.  Some were mostly diagrams without many words, while others were almost entirely verbal.  Even among the SQL Server vendors and at the Microsoft SQL Server pavilion you could find several different posters.  Quest Software (my employer) had two posters out - one with a quick description of all of the DMVs in SQL Server 2005/2008 and with a description and syntax for all of the system stored procedures.

My main criterion in deciding whether to put up a poster is whether it offers a quick look-up on information that I’d otherwise have to spend a long time thumbing through manuals (either physically or electronically) to find the information I need.  So what’s your main priorities in a) whether you even keep a poster, and b) whether you put one up on the wall?  What are some topics worth putting onto posters that you’ve always wanted to see in print?

Many thanks!  I look forward to your feedback,

-Kev

I just wanted to take a second and point out that SQL Server 2008 RC0 is now available. That means that your going to see a ton of marketing around new features and you’re going to have to decide whether or not those features not only justify an upgrade to SQL Server 2008, but what version, standard or enterprise, you should upgrade to. It’s a good idea to start looking now. If you’re a decision maker in this process carefully analyze what each feature can bring to your environment. You might be able to get away with the standard edition and save a bunch of money for your organization. On the other hand, features like transparent data encryption, table partitioning and the resource governor might be enough to justify the cost difference. Check out thecomparison:

SQL Server 2008: Click here…

For those still moving to SQL Server 2005:

SQL Server 2005: Click here…

-Dave

Be sure to check these out:

White Paper: Security Overview for Database Administrators 2008.  

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-security.aspxSQL Server 2008 is secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important security features in SQL Server 2008. It tells you how, as an administrator, you can install SQL Server securely and keep it that way, even as applications and users make use of the data stored within. White Paper: SQL Server 2008 Compared to Oracle Database 11ghttp://www.microsoft.com/sqlserver/2008/en/us/sql2008-oracle11g.aspxMicrosoft SQL Server has steadily gained ground on other database systems and now surpasses the competition in terms of performance, scalability, security, developer productivity, business intelligence (BI), and compatibility with the 2007 Microsoft Office System. It achieves this at a considerably lower cost than does Oracle Database 11g. White Paper: Running SQL Server 2008 on Windows Server 2008http://www.microsoft.com/sqlserver/2008/en/us/wp-run-sql-2008-on-windows-server-2008.aspxThe combination of SQL Server 2008 and Windows Server 2008 offers enterprise IT administrators and professional developers a superlative platform for mission-critical applications together with enhanced end-to-end security, management, and development capabilities.-Kev

I wanted to take a minute to draw some attention to a few things in this month’s SQL Server Magazine.  If you’ve gotten your copy of SQLMag, you’ll notice poster that’s a really great reference guide for SQL Server system stored procedures.  This outstanding poster can hang in your cube or office and outlines the function and syntax of these stored procs – a great tool for any SQL Server DBA.  Tom LaRock, a friend of mine through PASS and a member of Quest’s Association of SQL Server Experts helped to put this together and it looks fantastic, thanks a lot Tom! 

If you don’t subscribe to SQLMag, you can also pick up this poster at our booth at TechEd this week, as well as another useful poster that shows all of the DMVs in SQL2005/2008 and their uses.  Finally, Quest has also just released the SQL Server Consolidation Guide, a useful handbook for virtualization and consolidation within your SQL Server environment.  Swing by the booth and grab all three. One other item on the stored procedure poster that I want to mention is a link to a great freeware tool which is in beta from Quest called Discovery Wizard for SQL Server (you can get it here:  www.quest.com/sqldiscovery).  We all know about “SQL Server sprawl”, I and many MVP colleagues have written extensively on the subject.  Quest’s Discovery Wizard (we call it Disco! Take that John Travolta!) allows you to detect SQL Server instances and monitor your network for newly added servers. It utilizes a number of discovery methods which helps to reveal even those SQL Server instances that can’t be detected using the native tools.  It’s really neat, it’s free, and I suggest you check it out.Thanks!-Kevin

This week, Bryan Oliver and I are doing a webcast on a topic that always drove me crazy as a DBA: comparing and synchronizing changes between SQL Servers and different databases. Man, it seemed like at least once a week, someone would ask me what had changed in a database, or they’d ask me to promote a development database up to production without giving me a list of changes.

In the webcast, I’m going to refer to a few links that help with this messy chore, and Bryan will cover an option that beats everything I’ve got. <sigh> I can’t be the rock star all the time - sometimes, I’m bringing a kazoo to Battle of the Bands.

• MSDN primer on Data Definition Language triggers - you may have used triggers before to make things happen when rows are inserted, deleted or updated from a table. That’s great when you want to audit data being stored inside the database, but what about auditing the schema itself? Say hello to DDL triggers: they’re fired when the schema is changed, like when someone tries to drop a table.
• MSDN Code Gallery with DDL examples - after you read the first article to understand the basics of how DDL triggers work, this article will give you real-world examples.
• Integration between SQL Server and Visual SourceSafe - I’m linking to this only to show you that it’s out there, but be careful when you implement this. My first problem involved my servers with case-sensitive collations: if you script SQL objects to files, and then check those files into VSS, don’t expect uppercase and lowercase objects to make the round trip without problems. I had several instances where someone would create an object in uppercase, then drop it and recreate it in lowercase to match our naming conventions, and VSS wouldn’t recognize it as a different file. If you have a very simple database environment with case insensitive collation, it may work, but I would only run this from your local workstation and only in interactive mode, not as a scheduled task.
• Performance Dashboard Reports - I’ve mentioned these before in previous webcasts because they’re a great starting point for DBAs wanting to get more information about how database servers are working. The reports include a Schema History Report that identifies recent schema changes for each database. This data doesn’t stick around forever, so you have to check each server’s reports frequently - kind of a hassle.

If this sounds interesting, swing by the Quest Pain of the Week page and sign up for the webcast called “Bring Harmony to Your Databases: Compare and Sync Your Way to a Perfect Tune.”

There is a lot to talk about here…since this is a database blog, I’m going to provide the links to Microsoft’s published documentation so anyone who wants to get some good background information can do so. It’s certainly worth reading if you want to bone up on your Windows internals knowledge:
RAM, Virtual Memory, Pagefile and all that stuff
How to determine the appropriate page file size for 64-bit versions of Windows Server 2003 or Windows XP
Memory Performance Information

Hope that helps!

I would backup the database and restore it using the WITH MOVE option. For the sake of illustration however, you could use SQLCMD to run a script as follows (assuming you are working in an environment that allows xp_cmdshell to be run, AND assuming that the SQL Server service is running under an account with the rights to move the files on the operating system (big IFs in most places))…

Script file contents:

declare @cmd varchar(2000);
select 'drop connected users'
while (select count(*) from [master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’))>0
begin
select @cmd=’kill ‘+CAST(spid as varchar(5)) from [[master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’);
exec [dbo].[sp_executesql] @cmd;
end
select ’setting database to SINGLE_USER mode’
alter database $(database) set SINGLE_USER;
select ‘updating database settings to move file’
alter database $(database) modify file (name=’$(logfilename)’, FILENAME=’$(newlogpath)’);
select ‘detaching database’
exec [master].[dbo].[sp_detach_db] @dbname = N’$(database)’, @keepfulltextindexfile=N’true’;
select ‘moving file’
exec [dbo].[xp_cmdshell] ‘move “$(originallogpath)” “$(newlogpath)”‘;
select ‘re-attaching database’
create database [$(database)] on (filename = N’$(datafilepath)’), (filename = N’$(newlogpath)’) for attach;
go

Execute syntax:

sqlcmd
-S <instance name>
-d master
-E
-i <script name where the code above was saved>
-v database =”<database>” logfilename=”<current log file>” newlogpath =”<new log file path>” originallogpath =”<original log file path>” datafilepath =”<data file path>”
-o <output file>