Search results matching tag 'New Features'

Is the SAN dying???

RickHeiges — Wed, 12 Dec 2012 00:55:00 GMT

Is the SAN dying?

The reason that I ask this question is that MSFT has unleashed technologies this year that point in that direction

Always ON Availability Groups shuns shared storage
Windows 2012 has Storage Replication Technology that does not require a SAN
Windows 2012 has Hyper-V Replica Technology that does not require a SAN
PDW v2 continues to reinforce the approach to avoid shared storage

I'm not saying that SAN technology does not have its place or does not have benefits inherent to the beast. I'm just pointing out that MSFT has made investments in technology that diminish the need for SANs.

Thoughts?

Comparing Standard Editions of SQL Server

RickHeiges — Tue, 10 Jul 2012 12:00:00 GMT

Recently, I've been speaking with customers about upgrading SQL Server. At times, some customers have a lot of Standard Edition SQL Server 2005 / 2008 / 2008R2 in their organization and they want to see the features they get when upgrading to SQL Server 2012. Last week, I sent out some tweets to the #sqlhelp hashtag to see if someone has already put together a document or blog post about comparing the Standard Editions. I was unable to discover anything out there that really focuses just on Standard Edition. So I decided to put together a small table comparing the features based on the offical Edition features links like this: http://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx

Category / Feature	2008	2008R2	2012	Comments
CPU / Cores	4 CPUs	4 CPUs	16 cores	Based on the assumption of 4 cores per CPU
RAM	64 GB	64 GB	64 GB
Max DB Size	524 PB	524 PB	524 PB
Server Core Support	No	No	Yes	Significantly Reduce Patching
DB Recovery Advisor	No	No	Yes	New Feature in 2012
Failover Clustering	2-node	2-node	2-node
Backup Compression	No	Yes	Yes	New in 2008 R2
Automatic Corruption Recovery from Mirror	Yes	Yes	Yes	New Feature in 2008
Multi-Instance Support	16	50	50	Stand-Alone
Security – Basic Auditing	No	No	Yes	Still no Fine-Grained Auditing
Security – User Defined Server Roles	No	No	Yes	New Feature in 2012
Contained Databases	No	No	Yes	New Feature in 2012
Distributed Replay	No	No	Yes (1 client)	New Feature in 2012
SQL Server Data Tools	No	No	Yes	New Feature in 2012
DTS Runtime	Yes	Yes	No	Removed in 2012
Enhanced Gauges / Charting / Maps in SSRS	Yes	Yes	Yes	New Feature in 2008
Enhanced Extended Events	No	No	Yes	New in 2012

Most of the things that you hear about when a new version of SQL Server is released is in Enterprise (or Data Center) Edition. Standard Edition is very solid. Upgrading from SQL 2005 Standard Edition to SQL 2012 Standard Edition yields a lot of nice features. As you would expect, upgrading to SQL Server 2012 Standard Edition from 2008/2008R2 does not yield as many benefits. Currently, most of the customers that I deal with have approximately half of their installed base on SQL Server 2005. They can capitalize on some nice features by upgrading to SQL Server 2012.

I know that there are probably even more things that I did not cover as benefits of upgrading from earlier Standard Editions to SQL Server 2012 Standard Edition. If so, please leave a comment and I will update the blog post.

Connect Digest : 2009-05-30 : please vote!

AaronBertrand — Sat, 30 May 2009 16:47:00 GMT

Here are some issues I came across recently; most involve SSMS in some way, though Erland's assertion suggestion is a great one. And just to be clear, since I am seeing some accusations of distorting the fairness of Connect by "asking for votes," I am not asking anyone to vote blindly on these items (and it's clear from last week's results that people are NOT doing that). I'm asking you to take a look at these issues and -- if they affect you and you care enough to do so -- vote on them.

====================

ASSERTIONS

Erland brought up a great suggestion to add true assertions to DML statements, which would have several benefits over triggers or rolling your own rollback mechanisms.

#459383 : Add assertion clause to DML statements
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=459383
Current rating: 4.2 (6 votes)

====================

Object Explorer problem during attach operation

Last week I was attaching a largish database (~300GB) using CREATE DATABASE ... FOR ATTACH. Unlike when restoring a database, where you can see the "Restoring..." state next to the database name in Object Explorer (and can still access other online databases), during this time, I would receive an ugly error message if I tried to expand or refresh the database list. I believe this is because the attach operation places an exclusive lock on the table(s) underlying the sys.databases catalog view, and the *ad hoc SQL* that SSMS issues to display the list does not use snapshot or lower isolation. I'm not sure why it has to behave this way.

#459208 : SSMS : Object Explorer fails to show database list during attach
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=459208
Current rating: N/A (1 vote)

====================

Various Activity Monitor issues

Many people have complained about the way the new Activity Monitor replaced the previous version. While there are many new and helpful ways in which the new feature works, a crowd has gathered complaining about some of the things that are still missing (or were taken away).

#423019 : SQL Server 2008 Activity Monitor Enhancements
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=423019
Current rating: 4.7 (13 votes)

#352596 : SSMS: Activity Monitor is no longer in Treeview
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=352596
Current rating: 4.7 (6 votes)

#361102 : [SSMS 2008 RTM] Restore 2000 Activity Monitor
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=361102
Current rating: 3.8 (6 votes)

#350734 : SSMS : Activity Monitor column headers are too fragile
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=350734
Current rating: 4.3 (3 votes)

#350736 : SSMS : Activity Monitor queries replace white space with ugly boxes
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=35073 6
Current rating: 3.9 (2 votes)

#350726 : SSMS : Activity Monitor "Kill Process" more dangerous than it sounds
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=350726
Current rating: N/A (1 vote)

#350729 : SSMS : Activity Monitor process details dialog hard to use
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=350729
Current rating: N/A (1 vote)

============================================================

And here are the changes I've observed over the past 7 days for the entries in my post from last week:

#127219 : CREATE OR REPLACE
Rating last week: 4.7 (84 votes)
Current rating: 4.7 (103 votes) +17

#273938 : sp_helptext : option for generating ALTER
Rating last week: N/A (1 vote)
Current rating: 4.5 (3 votes) +2

#291300 : Option for sp_helptext to add 'GO'
Rating last week: N/A (1 vote)
Current rating: N/A (1 vote) no change

#362453 : SSMS RTM Table Limit
Rating last week: 4.7 (13 votes)
Current rating: 4.7 (17 votes) +4

#453982 : Bizarre XML Performance
Rating last week: 4.7 (3 votes)
Current rating: 4.7 (7 votes) +4

Connect Digest : 2009-05-23 : please vote!

AaronBertrand — Sat, 23 May 2009 16:02:00 GMT

Here are the items from this week that I feel deserve some attention. I'm also adding a little bit of analysis to this. I am always curious if my vote-pimping has any effect, so I am going to start taking a note of the ratings at the time I publish the post, and then compare them when I publish the next post. This will give me some idea about whether my efforts here are worth it.

====================

CREATE OR REPLACE syntax

We've been asking for this syntax for several versions now, and it finally looks like they're seriously considering it. To the point that they are narrowing down which DDL commands to support in the first round (since they don't pretend to be able to support all of them in one go). This item already has a lot of votes, so if you have already voted, you could add comments indicating which items you would prefer be supported initially.

#127219 : CREATE OR REPLACE
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=127219
Current rating: 4.7 (84 votes)

====================

sp_helptext improvements

Because of the missing syntax for create or replace, I filed two items in 2007 that would allow for improved usability with the system procedure sp_helptext. One is to allow an option that would append 'GO' to the end of the script, and the other is to allow an option to script ALTER instead of CREATE. While arguably these options are much less useful if they implement CREATE OR REPLACE functionality, that is still not guaranteed, so some other extensions might be helpful. So far, I am the only person who has voted for either of these items.

#273938 : sp_helptext : option for generating ALTER
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=273938
Current rating: N/A (1 vote)

#291300 : Option for sp_helptext to add 'GO'
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=291300
Current rating: N/A (1 vote)

====================

Object Explorer tree limits

Object Explorer has a known limitation where, if you have too many objects in a single database, expanding a relevant node will yield the error message: "See Object Explorer Details for objects in this folder." Using Object Explorer Details instead of the Object Explorer tree adds some functionality (like sorting and viewing additional properties); however, at the same time, it takes some important functionality away (like expanding multiple items at the same level and dragging column name lists onto query windows). Now it seems that fixing this issue means they will also have to fix the underlying OS, but I say by all means do it. If I want to wait 15 minutes for my crappy desktop PC to display a tree, I should be able to make that choice myself.

#362453 : SSMS RTM Table Limit
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=362453
Current rating: 4.7 (13 votes)

====================

Bizarre XML Performance

Fellow MVP and sqlblog.com blogger Adam Machanic discovered some illogical performance when constructing and slightly changing queries involving XML and TVFs.

#453982 : Bizarre XML Performance
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=453982
Current rating: 4.7 (3 votes)

====================

Connect Digest : 2009-05-16 : please vote!

AaronBertrand — Sat, 16 May 2009 15:33:00 GMT

Here are the items from this week that I feel deserve some attention.

====================

Hotfixes / service packs should leave authentication mode alone

I may have talked about this issue here before, I'm not sure, but I thought I was the only one who saw this happen. This week someone else came across the issue, where they had a SQL Server 2005 SP2 instance in mixed mode authentication, applied SP3, and the server changed to Windows Authentication only. This is really not cool and needs to be fixed! So far I'm the only person who has voted (even the submitter hasn't voted yet).

#450316 : Named instance changed to Windows-Only authentication on SP3 CU1 hotfix
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=450316

====================

Fixing documentation, and really fixing it, in both versions

They keep closing this item as fixed, but I don't think it is. (Louis Davidson pointed this out to me earlier this week.) First of all, they did not fix the 2005 documentation, and I think it should be corrected in the next revision. And the fix to the 2008 documentation has a typo, which should also be corrected.

#264723 : Doc : dm_os_performance_counters should show cntr_types
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=264723

====================

Having samples in the documentation that work

I have pointed out several Books Online topics that have non-functional sample code, and several more that were just bad ideas (like the CREATE DATABASE topic which only used dynamic SQL in the examples). This week someone found a geography sample that yields a .NET Framework error:

#450386 : Non-Functional Example Code: http://msdn.microsoft.com/en-us/library/bb933811.aspx
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=450386

====================

Allow us to specify collation for user-defined types

Just this morning (well, I guess this afternoon for him), fellow MVP Erland Sommarskog filed this issue that suggests we should be able to specify collation for user-defined types. Of course, as he states, real domains would be a preferable enhancement.

#451830 : Should be possible to specify collation when define a type/domain
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=451830

====================

Come on, it's Saturday, get out and vote!

AaronBertrand — Sat, 18 Apr 2009 18:07:00 GMT

I was encouraged by some fellow MVPs (well, mostly Alexander Kuznetsov) to periodically post a set of Connect items to encourage voting for items that I found relevant but that are maybe being largely ignored. Saturday is probably my best opportunity to do this, especially ones like today where the weather is crappy. While I can't guarantee I will do this every week, I did have some time today, so if you're bored and want some pointers to items you might want to vote for, here you are (and, believe it or not, not one of these issues was filed by me):

====================

SSMS : Allow support for AddIns as in Visual Studio

While some vendors have been able to hack their way into the SSMS shell, it is not a trivial exercise. Personally I think this should be easier for the mere peon as well. Simon Sabin created this suggestion over two years ago and it has only received 45 votes to date. Imagine if we could plug in our own UIs for things like Extended Events, Service Broker, Change Data Capture and several other pieces of functionality that are currently only accessible via T-SQL or DDL.

#265567 : SSMS : Allow support for AddIns as in Visual Studio
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=265567

====================

Create globally-scoped metadata views

Adam Machanic created this item recently (and talked about it here), which expresses a need for making DMVs accessible everywhere. Some DMVs are global, with a database_id column, and others are local to the database only. This can make it very cumbersome to get troubleshooting information across a system, when some parts of your query or queries need to reach into each individual database to cull stats and other information that is essentially in memory anyway.

#432689 : Create globally-scoped metadata views
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432689

====================

Metadata functions should follow same isolation semantics as metadata queries

Here is another gem from Adam, where he hopes that the SQL Server team will allow metadata functions like OBJECT_ID() to follow runtime isolation level instead of being stuck in READ COMMITTED.

#432497 : Metadata functions should follow same isolation semantics as metadata queries
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497

====================

Multi-level controls for Max Degree of Parallelism and Cost Threshold of Parallelism

Joe Chang is asking for finer controls and knobs to manage MAXDOP and Cost Threshold of Parallelism. Relatively straightforward and sensible.

#433400 : Multi-level controls for Max Degree of Parallelism and Cost Threshold of Parallelism
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=433400

====================

Allow startup procedures with parameters that have default values

Razvan Socol asks for the ability to use a procedure with default parameters as a startup procedure. We could do this in SQL Server 2000, but it no longer works in 2005 or 2008.

#430630 : Allow startup procedures with parameters that have default values
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=430630

====================

Document the locking behavior of each basic SQL Server construct

Finally, Linchi Shea asks for more information on how locking works for various SQL commands, data which is currently missing from Books Online. I don't really care if it ends up being published in Books Online, or MSDN, or on some MSFT employee's blog, but I agree with Linchi that this information would be very useful.

#430251 : Document the locking behavior of each basic SQL Server construct
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=430251

====================

Late Entry: Mobile version of Books Online should be made available

Jeremiah Peschka posted his very first Connect item today, asking for a way to view Books Online on a mobile device. I think this could be very useful even if it simply becomes a series of PDF documents or some similar distribution format. I think it's unlikely to get serious consideration from the Microsoft folks, but it can't hurt to try, right?

#433498 : Books Online: Mobile version of Books Online should be made available
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=433498

====================

PASS Website Overhaul

RickHeiges — Thu, 25 Sep 2008 00:25:00 GMT

PASS has launched its new website. There has been a lot of hard work behind the scenes over the past year to be ready for this. Nothing ever goes as planned, but I believe that you will agree with me that the improvements made are huge.

If you are not a member, membership is now FREE at www.sqlpass.org. The summit will be held once again in Seattle this November. There are many other SQLBloggers speaking and attending this year. If you are unfamiliar with the caliber of content at the PASS Community Summit, the recorded sessions are now available for PASS Members for the 2005 and 2006 events. Check it out - it's FREE!

Did you know that PASS has SIGs (Special Interest Groups)? The SIGs are also free to join and have monthly webcasts for FREE!

Another area that is new is PASSTips. Here members of the PASS commnity can submit 5 minute videos on a "tip" that you can use the next day at work. Everyone has some little trick/tip that makes their work easier. Share it!

This is just a couple of new items now featured at the new PASS website. I hope that you find it useful!

Filtered Indexes

RickHeiges — Tue, 18 Mar 2008 16:47:00 GMT

In SQL Server 2008, you can have what is called a "Filtered Index". What does this mean?

An index may be created for a subset of rows in your table. For instance you could create a filtered index for a "state" column if the value is "CA" for California; the rest of the states would not have a value. When you create your index, simply add a WHERE clause to activate this feature.

In the state example, it probably does not make sense to have an index just for California, but there are cases where this could save a lot of space. For example, if a table had a column that for most rows had no data, a filtered index could be built on it where the column is not null.

I used FoxPro in a previous life. This was a feature available to us way back in the 90s. We used FoxPro and this feature in DOS, WfW3.11, and SCO Unix. It has been a while, but there was a time when we created an index just for this one particular customer because of the proceessing time it took. Disk space was more scarce back then and filtered indexes helped us squeeze the most out of it. I am happy to see this feature in SQL Server 2008. I have only started to play with this and expect that some limitations will pop-up.

No More HCL - in 2008

RickHeiges — Wed, 19 Dec 2007 14:25:00 GMT

The old Hardware Compatibility List which was renamed the "Windows Catalog" is no more. When creating a Clustered Solution, you need to have a system that is Certified for clustering or there will be no official support. This has been a point of pain in the past as organizations purchased a clustered solution and then one of the nodes has issues after a year or two. Getting an exact replica of that node is often impossible and impractical. Try explaining to the big wigs that you need to purchase a new system that is 2 years old - not very compelling.

In Windows 2008 and SQL 2008, there is no need for a certified solution. There is a program called clusprep.exe that you run on the nodes of the proposed cluster to test/prepare the nodes. If your system passes, it can run on a cluster.

One drawback of this is that when failing over to a node of lesser "girth", the user experience may suffer. Also, with this change, the base of clustering is changing which means that your upgrade to the 2008 flavor will take some more planning.

If you attended the PASS Community Summit in Denver, you can view the streaming session online on Clustering. It was very educational.

Compression in DB Mirroring

RickHeiges — Tue, 20 Nov 2007 11:19:00 GMT

When you start to look at SQL Server 2008, there is a sub-theme of using compression to help drive performance and usefulness. When I hear the word compression, my first thought is "No - I remember Stacker". How many years ago was that obscure reference? Too many... Stacker was a third party product for MS-DOS which basically doubled your disk space. It worked, but it also seemed to increase chances for corruption on those old disks. I can't recall the exact name of the product, but there was also a third-party product which compressed RAM to give you more of that too.

SQL Server 2008 will use compression when transmitting the transaction from the principal to the mirror. Since a majority of DB Servers are not CPU-bound, there are spare cycles to get the compression of this task done quickly. The goal of using this compression technology is to cut down on network latency. We have all seen this when we have a large file to transfer over the network. It is often faster for us to ZIP a file, copy it over the network and unZIP it than it would be to simply copy the file without being zipped. The same logic applies here.

Compression is used in other areas of SQL 2008 as well such as backups! More on that later in a future blog post perhaps.