It's nice to give Microsoft feedback and get the product changed!
I first mentioned this in a post last month and logged a connect.microsoft.com entry. Microsoft checked it out, invesigtaed, and have now put a new property into SQL Server 2008 to resolve it! They write:
Thanks a lot for your feedback. We are taking a look at this issue.
Posted by Microsoft on 2/27/2008 at 8:54 AM
First, let me thank you for your feedback on SQL Server; we really appreciate your willingness to spend time and write up the issue as you have, and to send it to us.
As you've described, the Defaults query that is run when you expand this property is just looking for defaults you've created via CREATE DEFAULT, not the more typical (imo) default constraints on table columns. It does by enumerating the SQL Server Management Object's (SMO) Database.Defaults collection, which in turn uses SQL as you've seen through the profiler. This however does not allow you to choose individual column's DEFAULT constraints to be copied. In fact, the current implementation of the Transfer Objects Task doesn't allow many kinds of column constraints to be copied.
For SQL Server 2008, we've just added a new property to the task, CopyAllDRIObjects, which corresponds to SMO's "DriAll" property. This will cause all data referential integrity objects, including constraints, on objects you've selected, to be included when copying. So for example if you've selected three tables to be copied and they each have columns with DEFAULT (or other types) of constraints, and you enable CopyAllDRIObjects, this will cause their constraints to be copied as well to the destination. DRI objects for objects not selected, i.e. constraints on columns in tables you have not selected, will not be copied.
Thanks again for your post to connect; we look forward to hearing from you in the future. Take care!
Posted by Microsoft on 3/6/2008 at 5:47 PM
I think the key words here are "on objects you've selected" – so that you're not trying to transfer things you don't want.
The fact that they took any kind of action is great, and definitely shows that it's worth jumping onto the connect.microsoft.com/sql site and putting in feedback about SQL Server. If you identify something that should be changed (and it's easy enough!), then you could have a very quick fix!
The morning after the Adelaide launch of the 2008 products, I got to enjoy breakfast with quite a handful other Adelaide community leaders, and few of the Microsoft DPE team. Conversation was remarkably varied, and one of the things that came up was Community Credit and the Second Life .Net User Group. I've never done the Second Life thing, and I'm not sure I want to. It's an interesting concept though – the idea of attending a user group within a game. I guess it's a cross between an online community and a face-to-face one. Perhaps it's onlineface-to-onlineface? It's not my thing, but it might suit people who can't do face-to-face for one reason or another. Is there a Second Life SQL User Group?
A little while back someone asked me how to make a PivotTable in Excel, showing the Min, Max and Avg of a field.
So I recorded a movie. Now, thanks to the wonder of Silverlight, it should be here for your viewing pleasure. No sound, so you can keep listening to whatever's in your ear already.
This is really quite cool, especially since it's a tool feature, and one that will help SQL Server 2005 installs.
Suppose you have many servers, all registered in Management Studio, in the Registered Servers window. Now you can right-click on the Local Server Group, and pick the "New Query" option. Write a query, and it gives you a result set including the results from all the servers, as if you'd done a UNION ALL using linked servers.
So now if you want to do something like
select name, database_id from master.sys.databases;
on a query against multiple servers, you get three columns. Server Name, plus the columns from the query.
Sick of how slow backups can run, even though you're using compression from some tool like Hyperbac?
Greg Linwood's had a good idea to help. A large amount of the data in any database system is the data stored in indexes (copies of the data in tables, just ordered differently) to help your queries run faster, so if you can tell the system to backup the database without that data, then it should work a whole lot faster.
Of course, when you restore a smaller backup, it should a lot quicker – but you won't have the indexes in place yet. But you still have the option of letting them rebuild, so you ought to be able to get your system back up quicker (although potentially doing table-scans for a while).
If you like this idea, let Microsoft know, at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331220
Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!).
But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it's not a SnapIn for PowerShell, but it turns out it is. We ran:
…and got the following results:
Name : SqlServerCmdletSnapin
PSVersion : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
Name : SqlServerProviderSnapin
PSVersion : 1.0
Description : SQL Server Provider
So then I ran:
And now I have the full SQLPS functionality in my usual PowerShell window. Lovely. And by putting this into a PowerShell console file, I can have it load up automagically for me. SqlServerProviderSnapin gives me the SQL: and SQLPolicy: drives, whereas SqlServerCmdletSnapin gives me Invoke-Sqlcmd for running queries.
<?xml version="1.0" encoding="utf-8"?>
<PSSnapIn Name="SqlServerProviderSnapin" />
<PSSnapIn Name="SqlServerCmdletSnapin" />
I'm still not convinced about the merits of using PowerShell to manage SQL Server, but as more Windows Admins adopt PowerShell as their preferred scripting language, I think it's worth having it as an option.
I've written before that SQL Server 2008 (February CTP) gives you SQLPS – a PowerShell interface to SQL Server.
So I was trying to get the ScriptingOptions happening, and this is the only way I've found so far. It's ugly, and if you have a better way of doing this, please post a comment for me.
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.DriAllConstraints = $true
And this gives me the Sales.Store table with all the default constraints thrown in. Much better. Ugly way of doing it, but at least it works.
So I'm at the Heroes launch in Melbourne today. I'm going to hang out at the Experts booth, so if you're here and have a question about SQL Server 2008, why not come over?