I'm down on SQL Server Management Studio right now. I basically just spent a day trying to swap out the evaluation version of SQL Server 2008 R2 with the Express version, and SSMS just wouldn't install, apparently because some components of the evaluation version wouldn't uninstall. It's a long, ugly story that I won't bore you with, but a Web search shows that I'm certainly not alone in my frustration, which is cold comfort indeed, as they say.
Graham O'Neale's blog post illustrated problems similar to what I experienced. Note that he starts out with: "Ok, I'm angry...."
So are a lot of other people. Aaron Bertrand wrote a detailed account of his attempt to uninstall the evaluation edition and ultimately had to resort to registry hacks. "That was WAY too painful," he said. I feel that pain. So do dozens--hundreds, thousands?--of others. I wish Microsoft would just fix the damn thing.
Anyway, after many hours of exasperation, I decided to give up and started wondering what alternatives to SSMS were out there for developers wanting to mess around with SQL Server. Being of little coinage, my main requirement was simple: they had to be free.
Turns out, all I had to do was hit the Window key and type "ISE." (Well, Microsoft got THAT right.)
That command brings up the Windows PowerShell Integrated Scripting Environment (ISE). PowerShell is typically described as a tool for automation tasks or a "command-line shell designed especially for system administrators." But it can do much, much more.
Coincidentally, in my day job as technical editor of MSDN Magazine, I'm currently working on an article about PowerShell and how its seamless integration with the .NET Framework allows developers to do some pretty cool things. This article (to be published in the July edition) describes how to use the WPF PowerShell Kit to build a nifty WPF present value calculator.
I asked the author if he thought PowerShell would be of benefit to SQL Server developers. "PowerShell is absolutely, without a doubt, hands down a valuable for tool for Devs to work with SQL Server (and more)," he replied.
So I've been fooling around with it for a few hours now. It looks promising.
Version 2.0 comes with Windows 7 and Windows Server 2008 R2 out of the box, ready to be used. It can be run from the command line or in the ISE, which I prefer. To get started running scripts, you have to change the default execution policy, as explained here.
Then, to work with SQL Server, you need to install a couple "snap-ins," one for the SQL Server Provider and one to run SQL Server Cmdlets (pronounced "command-lets"). With Windows Server 2008 R2 and SQL Server 2008 R2, this is simply a matter of entering the following commands while in PowerShell: Add-PSSnapin SqlServerProviderSnapin and Add-PSSnapin SqlServerCmdletSnapin. You can read more about that in this TechNet article and this MSDN article.
After you're all set up, you're ready to start interacting with your SQL Server databases with regular Transact-SQL commands with the Invoke-Sqlcmd Cmdlet.
For example, here's a query against the Northwind database (Fig. 1 shows the result in PowerShell):
invoke-sqlcmd -query "SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID)
WHERE Orders.orderdate > '5/5/1998'
ORDER BY Orders.OrderDate" -database northwind -serverinstance acer | format-table
Figure 1. A SQL Server query against the Northwind database executed in PowerShell. (Click image to view larger version.)
Fig. 2 is that same query as executed in SSMS (yes, I finally did get it to install).
Figure 2. The same query as executed in SQL Server Management Studio. (Click image to view larger version.)
Obviously, SSMS offers some features that PowerShell doesn't, such as the handy Object Explorer pane. But what I've shown is just the beginning of what you can do with PowerShell in the place of SSMS. I'll be exploring PowerShell more as time allows, and I'd also like to investigate other options, such as LINQPad 4 and the free version of Toad for SQL Server.
But I'm sure there are many readers out there who have already followed this path. So I'd love to have you share your experiences with the rest of us. What problems have you had with SSMS? What free alternatives do you recommend? Any experience with LINQPad or Toad? What do you like/dislike about these or various other options? Please comment here or drop me a line.
Posted by David Ramel on 05/19/2011 at 1:15 PM2 comments
Earlier this month there were several articles published about the term "NewSQL" coined by the 451 Group in regard to a new class of vendors of high-performance, scalable database examined in its new report.
The group explains the meaning of the term in a post hawking the report. And ReadWriteWeb has expounded on the subject.
I wonder, is this stuff really necessary? Or is it just a backlash that can be viewed as part of a repeating pattern concerning traditional, established technologies, as Andrew J. Brust posited in a thoughtful piece about the NoSQL movement earlier this week. He wrote:
"So if older technologies are proven technologies, and if they can be repurposed to function like some of the newer ones, what causes such discomfort with them? Is it mere folly of younger developers? Are older developers building up barriers of vocabulary, APIs and accumulated, sometimes seldom used, features in their products, to keep their club an exclusive one?"
Anyway, the 451 Group noted that "like NoSQL, NewSQL is not to be taken too literally: the new thing about the NewSQL vendors is the vendor, not the SQL."
So, does that help clear things up? Personally, I don't want a new acronym that describes vendors, not technologies.
I hereby officially launch the NoNewSQL movment. It's all about encouraging enterprises to take advantage of cutting-edge, anti-acronym technologies in order to leverage market trends and forces to enhance business value while increasing ROI... .
What do you think? Enough with the SQL acronyms? Comment here or drop me a line.
Posted by David Ramel on 04/28/2011 at 1:15 PM11 comments
Last fall, writing about the developer uproar surrounding the lack of database support in the Windows Phone 7 platform, I noted: "It's pretty obvious what mobile developers want. Is Microsoft listening?"
Well, the answer is: "Yes!"
Microsoft this week announced in a MIX11 keynote address that the next update of Windows Phone 7, called "Mango," will include the lightweight SQL Server Compact Edition. Previously, database options were limited to options such as storing data in XML files, isolated storage or third-party solutions. That caused much developer ire, such as this reader comment: "Why not implement SQL CE Compact? You cannot write real business application without database support."
Microsoft exec Joe Belfiore noted early in his keynote that Mango will add several new features "that you've been asking for."
That's for sure. In fact, when he got around to listing the features, he talked about new core support for things like TCP/IP sockets. But when he mentioned "we have a built-in SQL database that you can use," spontaneous audience applause broke out, causing him to have to pause and acknowledge the cheers. "Yes, thank you," he said, before he could continue on. There was yet more applause after he mentioned more improved data access, to information such as contacts and calendars "so you can more richly integrate your apps with the user's data."
Clearly, data is king in mobile app development, and Microsoft has answered the call to help developers build richer data-centric apps.
So much for the monolithic behemoth out of touch with customers and developers.
You can watch the keynote about Mango, due out later this year, here.
What do you think about the addition of SQL Server support to Windows Phone 7? Just what the developer ordered, or too little, too late? Comment here or drop me a line.
Posted by David Ramel on 04/15/2011 at 1:15 PM2 comments
Microsoft this week released the last cumulative updates for SQL Server 2005 and will end mainstream support for the product in a few weeks.
The Cumulative Update #15 for SQL Server 2005 Service Pack 3 contains six fixes, while the Cumulative Update #3 for SQL Server 2005 Service Pack 4 contains five fixes.
Although mainstream support ends April 12, technical support will continue for another five years for the latter service pack.
And plan ahead: technical support of SQL Server 2008 Service Pack 1 ends Oct. 11.
What version do you run? Do you feel pressured to upgrade? Comment here or send me an e-mail.
Posted by David Ramel on 03/23/2011 at 1:15 PM1 comments
Microsoft announced that developers can get help with building early, real-world projects with the next version of SQL Server, code named "Denali," through the Metro Early Adoption Program.
Denali, expected by many to be released later this year and sometimes unofficially known as SQL Server 2011, was issued last fall as a Community Technology Preview. It features enhancements such as a high-availability component called Always On, a "column-based query accelerator" and the capability to "allow for the creation of so-called columnar indexes over relational databases," as explained by Andrew J. Brust on Redmond Developer News.
And, of special interest to developers: it will provide a new unified Visual Studio IDE that's being called "Juneau."
Microsoft is seeking to get more developers onboard with Denali and started on real projects that will help them put some positive spin on the new version come release time. All you need to apply to the Metro Early Adoption program is a Windows Live ID and willingness to sign a non-disclosure agreement, Microsoft senior technical evangelist Roger Doherty announced in his blog Monday.
"You will be asked to provide some details about your company and your early adoption project," Doherty said. "We review each nomination that comes in and approve the solutions we think will have the best chance of showcasing SQL Server Denali improvements during the launch wave."
He said early adopters would get access to:
- Confidential online meetings with SQL Server teams.
- DeepDive developer labs, with assistance from the SQL Server engineering team.
- Beta support.
- Training events.
- A ready-to-go, dedicated virtual Sandbox learning and testing environment.
You can get more information in a Channel 9 video featuring Doherty and colleague Steve DiMarco or the Microsoft Denali page.
Will you be applying? Are you looking forward to the new SQL Server or are you overwhelmed by a new version too soon after 2008 R2? Comment here or drop me a line.
Posted by David Ramel on 03/23/2011 at 1:15 PM0 comments
Forget all this touchy-feely open-source LAMP stack stuff--throw away your SQL Server and run our MySQL database on Windows.
Okay, maybe the message wasn't quite that stark, but there's no doubt Oracle upped the ante in the database wars today when it launched a comprehensive program to entice SQL Server developers, DBAs and such to run its Linux-borne MySQL database on Windows.
"It's a really nice implementation. I think it exceeds what Microsoft provides in their SQL Server implementation," quipped Rob Young, an Oracle senior product manager, during a three-hour Webcast. Talk about a direct attack on Microsoft's home turf: He was referring to Connector/NET, a tool that helps developers build .NET apps with MySQL. It was even noted that Connector/NET was written entirely in C#!
Oracle devoted an entire presentation to .NET development during its Webcast (but the audio was bad, so I have no idea what they said; the audio was fine before and after that presentation). And in the opening segment it trumpeted MySQL's improved "Windows eco-system support" for Visual Studio, Entity Framework, Windows administration tooling and connector enhancements--all deemed priorities for fiscal year 2011-12.
Connector/NET lets developers create C#, ASP.NET and VB.net apps, Oracle said, while implementing the ADO.NET interfaces, integrating into ADO.NET-aware tools and sporting a fully managed ADO.NET driver.
It almost seemed as if Oracle was adopting a "if you can't beat 'em, join 'em" mentality with MySQL. "Windows is, and will continue to be, No. 1 for development and deployment and we really want to stay focused and make sure that the performance capability [of MySQL development on Windows] is top-notch," said Tomas Ulin, vice president of MySQL Engineering when discussing upcoming enhancements to Oracle's Windows development tools.
Young summarized the state of the latest version of Connector/NET (6.3) and where it's going: "We have Visual Studio 2010 compatibility. We have support for .NET 4.x and we go back to 2.x as well, so if you're using an older version and haven't upgraded yet, our Connector will work back a couple of versions. We've also got this Model First development using the Entity Framework, so Connector 6.x will support that, so you can actually develop objects, tables, that type of thing, within the Entity Framework environment and then forward-engineer those into actual DBL and ultimately into database objects. If you're familiar with the SQL Server editor within Visual Studio, the MySQL editor looks, basically, exactly the same."
He also discussed MySQL compatibility with Visual Studio wizards and Web site wizard integration. "They look and feel and function exactly like they do for SQL Server. So you can use our Connector along with MySQL along with Visual Studio and feel very comfortable if you're already using those things for SQL Server as well. So no surprises here, just complete, simple integration with the Visual Studio environment."
Young also discussed the MySQL Workbench, which he noted was the No. 2 download from the MySQL family and, according to a slide, has a "growing, enthusiastic developer community." He said "it puts a face--a visual, point-and-click face--on the front of MySQL."
He said Workbench has a graphical design aspect that lets users reverse engineer a database, for example, and see it graphically, change things around and roll those changes into a new, physical database.
"It's got the ER design, development aspect to it as well," Young said. "You can work with code, and it's just a very nice, slick implementation. And what we've done is we've gone to great lengths to make sure the features and functionality within Workbench match very closely that of Visual Studio 2010. It very much feels like a Windows product because it is a Windows product. And just know that this is here, specifically, to extend what Visual Studio does for MySQL and provide a lower-level development tool for those that would like to use MySQL in a big way."
So there you have it. MySQL goes Windows. While Microsoft woos the open-source community to embrace SQL Server, Oracle is enticing Windows developers to move from SQL Server to open-source MySQL.
And with that, I pose a few questions: What will developers say about this? What will the Linux fanbois say about this? And why do they pronounce SQL Server as "sequel server" while pronouncing MySQL as "my s-q-l"?
I need answers to these burning questions. Comment here or send me an e-mail.
Posted by David Ramel on 03/16/2011 at 1:15 PM4 comments
No need to wait for that next major .NET Framework upgrade to get Entity Framework improvements, as a stand-alone EF installer is about to be released for the first time.
Entity Framework CTPs have been out for months and have alleviated many user concerns, and Entity Framework 4.1 is due to arrive any week now to address more.
It includes two major components: a DbContext API and Code First capability. The former is basically a simplified and enhanced ObjectContext. Most of the buzz among developers is around the new Code First component.
"Code First alleviates any need for a visual model, you can just create your classes, add any needed configurations to make sure they align with EF rules and go," said EF expert and author Julie Lerman in an interview. "You get the benefits of the ORM [object relational mapping] features offered by EF without the extra layer of having to create your model in a designer and then futz with a code generator to get your classes the way you want them."
Alleviation of the futz factor is a definite plus, indicated Tim Laverty, who as the lead program manager on the Microsoft EF team probably knows as much about Entity Framework as anyone in the world. In an interview, Laverty said Code First "offers a way for 'code-oriented' customers to build EDM [Entity Data Models] models and use EF. Previously, tooling or a command-line interface was required to create your model unless you were willing to create and edit complex XML files."
Laverty also mentioned that EF 4.1 supplies several conventions, such as database creation, to help developers build apps easier, and also has rich ASP.NET MVC integration.
And developers are likely to be excited about the new DbContext API, too, said Lerman, who wrote a preview of EF 4.1 in MSDN Magazine [full disclosure: my day job is features editor for the mag]. "It's not just that the amount of exposed properties and methods has been reduced," she told this site, "but there are a lot of coding tasks that have been simplified … whereas you have to do a little more juggling with ObjectContext to achieve the same ends."
While many developers lauded the new release when it was announced last week, there's still a big list of improvements they want, such as support for stored procedures, type conversions, enums, bulk updates and more.
"Darn it! No SP support? Cached queries? Very disappointing," said a user named Darius in the comment section of the ADO.NET team blog announcing the "Go-Live Release Candidate." "Guess we'll have to wait a bit longer to use this. Please just make sure that when you do implement, you do it right. Needs SPs to map to POCO objects or maybe even dynamic objects. Same goes for an ad-hoc SQL query."
Laverty sounded like he's heard about the enum clamor before. "Enum support!" he exclaimed when asked about future improvements (it's actually No. 1 on the uservoice site list, with more than 1,200 requests). "This is our top ask and is in our product plan for vNext," Laverty said. "After that, there's a large backlog: TVF [Table-Valued Function] support, Spatial support, better integration with database projects … better SQL gen, batch CUD [create, update or delete], second-level cache support, sharding support, migrations/database evolution, better [stored procedures] support" and more.
Laverty noted that disassociating EF from the evolution of Visual Studio and .NET Framework releases will help the EF team get improvements out faster--hopefully once per year. "We don't have a locked date for the release after 4.1 but are working on it now," he said.
He said you can let Microsoft know what features you'd like to see in EF in three main ways. "Give us feedback on our uservoice site, our forums, and our blog, please. We actively use each to build and prioritize our product backlog."
What would you like to see done to Entity Framework? Comment here or drop me a note.
Posted by David Ramel on 03/09/2011 at 1:15 PM11 comments
Here's a roundup of recent product announcements in the database development world.
Devart announced the new version of dbForge SQL Complete, an add-in to enhance code completion for users of Microsoft SQL Server Management Studio.
The company said SQL Complete Version 2.5 comes only a month after version 2.0, incorporating new features requested by users. Some of the main enhancements include:
- Quick object info: Moving the mouse cursor over a database object identifier produces a popup window to quickly show information such as column names and data types, more column properties, different kinds of keys, object types and user descriptions.
- Expanded INSERT statement feature: Users can type a table name in an INSERT statement and then just press the Tab key to expand the statement to the entire template.
- Export/Import setting wizard: Users can export the tool's settings to a file and use it later to import these settings.
The company also listed an improved SQL formatter, a button to reset page defaults and improved logic for JOIN clause suggestions.
Norway-based Norsync announced its U.S. launch with a free trial of its suite of tools for mobile application developers "who want to build, operate and synchronize data-driven mobile applications in a fraction of the time and cost."
The company said programmers often find it too complex to synchronize data between mobile clients and servers or configure a database management system to filter appropriate data to mobile databases.
Norsync Suite lets developers input a SQL-based database schema to generate an entire Visual Studio project with deployable C# applications for Windows and Windows Mobile devices, with support for iPhone and Android devices expected next quarter.
Red Gate released SQL Source Control 2.0, which lets users connect existing source control systems--such as Team Foundation Server or Subversion--to SQL Server. The company said the tool also offers a shared model to let developers on a team see other members' database changes in real time.
The company offers a 28-day free trial of the software that it says can be installed in less than five minutes.
Revelation Software announced a new version of its OpenInsight Development Suite, which provides developers with Windows and Java-based GUI tools for Web and client/server apps that support native and relational technologies such as XML, SQL and Lotus Notes, among others.
Version 9.2.1 provides new components such as:
- OpenInsight for Web: A toolkit that helps OpenInsight and MultiValue developers build Web 2.0 browser-based programs as well as forms, reports, dashboards and menus.
- Source code management: A new feature that lets users group source code into modules and kept as unique versions as it's compiled.
- U2 DotNet Connecter: This has been redesigned in order to use the Uniobjects .NET library in lieu of Intercall libraries; this allows connection pooling.
The company also announced new versions of its Universal Driver.
Posted by David Ramel on 03/08/2011 at 1:15 PM0 comments
Netflix is hiring, but SQL Server gurus need not apply.
The company that transformed from a clunky DVD-by-mail delivery system to cutting-edge video streaming from the cloud has some SQL Server developers worried about their future job prospects as Netflix embraces the NoSQL movement. Few things are scarier than wondering if you backed the wrong horse in your career choice.
"This sort of questions are freaking me out," wrote one blogger about a forum post that expressed concern about the Netflix decision. "Can you imagine how panicked programmers are, knowing that not only tens of programming languages are out there (plus C/C++ is dead, Java is dead, etc.), but also a couple of different programming paradigms?"
The post he was referring to appeared on a SQLServerCentral forum and referenced a Netflix blog discussing the move to the NoSQL camp. "Is this something relational database guys has to be concerned about? I am concerned on what future holds for SQL-Developers and SQL-server DBAs," said forum member sqlcool.
The blog posting that stirred all this up was titled "NoSQL at Netflix" and was written a couple weeks ago by Yury Izrailevsky, director of Cloud and Systems Infrastructure at Netflix.
"While it is not easy to re-architect your systems to not run join queries, or not rely on read-after-write consistency (hey, just cache the value in your app!), we have found ourselves braving the new frontier of NoSQL distributed databases," Izrailevsky said.
The Neflix exec explained: "the reasons behind our choice of three ... NoSQL tools: SimpleDB, Hadoop/HBase and Cassandra."
After discussing each one, he admitted:
Adopting the non-relational model in general is not easy, and Netflix has been paying a steep pioneer tax while integrating these rapidly evolving and still maturing NoSQL products. There is a learning curve and an operational overhead. Still, the scalability, availability and performance advantages of the NoSQL persistence model are evident and are paying for themselves already, and will be central to our long-term cloud strategy.
There has been plenty of other discussion about Netflix and its embrace of NoSQL, including a technical white paper by Netflix software architect "Sid" Anand. He’s going to talk more about it in a meeting next week titled "NoSQL @ Netflix."
Meanwhile, as Izrailevsky concluded: "For those technology superstars out there: Netflix is hiring."
Are you a SQL Server guy worried about this kind of stuff? Please share your thoughts by commenting here or drop me a line.
[Editor's note: This article was updated from its original posting, with corrected information pointed out by the commenter referenced in paragraph 3 and 4.]
Posted by David Ramel on 02/10/2011 at 1:15 PM4 comments
SQL Azure developers are at a disadvantage compared to Windows Azure coders because of a lack of desktop emulators, a Microsoft blog indicated.
The Windows Azure SDK includes emulators that run on your PC so you don't have to subscribe to the online service to code against it, noted Buck Woody in an earlier posting. Yesterday he said that posting brought up the question of whether there was such a thing for SQL Azure.
"The short answer is that there isn't one," Woody said in a Carpe Datum blog posting titled "Where is the SQL Azure Development Environment." (I realize that, being an editor, I'm overly picky about such things, but isn't that headline screaming for a closing question mark?)
He explains how to develop with SQL Azure without emulators ("you can simply treat it as another edition of SQL Server") and it doesn't sound that onerous, but I wonder about the scripting part: "you can script out the database when you're done as a SQL Azure script." Things may well have improved since I tried a SQL Server-to-SQL Azure database conversion script in the early days of the cloud-based offering, but I was pulling my hair out having to make all kinds of manual changes.
Anyway, Woody concluded:
"Will all this change? Will SSMS, 'Data Dude' and other tools change to include SQL Azure? Well, I don't have a specific roadmap for those tools, but we're making big investments on Windows Azure and SQL Azure, so I can say that as time goes on, it will get easier."
What do you think? Are you database guys getting the short shrift, or is it no big deal? Please comment here or drop me a line.
Posted by David Ramel on 02/04/2011 at 1:15 PM0 comments
It's no coincidence that business intelligence is the focus of Microsoft's brand-new update to the SQL Server 2008 R2 training kit.
The very first item listed in the announcement for the SQL Server 2008 R2 Update For Developers January 2011 Update (seriously, guys: get some professional help for naming your products!) is "Build Your First Microsoft BI Solution with SQL Server 2008 R2."
The Redmond software giant has championed "self-service BI" in everything from the Business Decision Appliance to the project "Crescent" data visualization tool to the PowerPivot plug-ins for Excel and SharePoint.
Microsoft data and storage VP Ted Kummert seems to have made it his personal crusade. In case there's any doubt about that, a search for "Ted Kummert" and "self service BI" garners 3,380 hits in Google and and 1,190 in Bing.
That full-court press just continues with the new training kit update. It includes an extremely comprehensive set of offerings with 11 units that feature (as listed in the announcement):
- 31 presentations (PowerPoint slides, videos and transcripts included)
- 27 demos (installer scripts, videos and transcripts included)
- 12 hands on labs (installer scripts included)
Let's hear it from you professional BI gurus and vendors out there: is Microsoft trying to put you out of business, like some are hinting? Comment here or drop me a line.
Posted by David Ramel on 01/26/2011 at 1:15 PM1 comments
Last week the Data Driver wondered if SQL Server was behind the recent Hotmail outage.
The answer is: No.
It was a script error, according to a Windows Blog post by Mike Schackwitz, pointed out to me by a helpful member of Microsoft's PR firm.
To sum up, a script meant to delete temporary accounts used for automated testing accidentally deleted some real users' directory records. This supposedly didn't delete any actual data, but a lot of users responded to the post and disputed that, with many saying they lost years' worth of messages and that some accounts were still unavailable. The complaints were still rolling in as of today.
Microsoft representatives were helping some of those users, but there were many comments along the lines of "Disgusted!!!!" and "Shameful!!" and "IT IS CERTAINLY NOT FIXED."
If you're still missing data, the blog lists several links that might be able to help you recover it.
Are you still having Hotmail data issues? Comment here or drop me a line.
Posted by David Ramel on 01/12/2011 at 1:15 PM7 comments