SQLAuthority News – 2 Security Updates for SQL Server 2000 SP 4 Users

If you are using SQL Server 2000 still today my very first recommendation to you is to upgrade to SQL Server 2012. SQL Server 2000 is now 12 years old product and since then many new enhancements as well features which are relevant to current growth and progress in Informational Industry. Now is the time to catch up with the latest trends. Here is one more point for you to notice if this helps you consider to upgrade to the latest version. One can’t upgrade directly from SQL Server 2000 to SQL Server 2012. You need to first upgrade to either SQL Server 2005/2008/R2 and then further plan to upgrade to SQL Server 2012. There is no direct upgrade path for SQL Server 2000 to SQL Server 2012. I strongly believe this is the time to upgrade to the latest version.

Well, also there is a rule that to let something continue if it is not broken and working fine. If you are following that rule and still using SQL Server 2000 I strongly suggest that you upgrade your SQL Server 2000 SP4 and update it with latest Security updates.

Here are two important SQL Server Security Updates.

Security Update for SQL Server 2000 Service Pack 4 (KB983811)

Security Update for SQL Server 2000 Service Pack 4 (KB983812)

As we are talking about SQL Server 2000 let me ask you a quick question – how many of you are still using SQL Server 2000 or earlier version in a production system on at least one server?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Follow up on Beginning NuoDB – Who will Benefit and How to Start – Part 2

Earlier I blogged about Beginning NuoDB – Who will Benefit and How to Start, I received a few follow up questions about it so I decided to write a short article as a follow up. One of the questions I received was why I started with this product. Well, the reason is that I decided to learn more in the database field. This product got my attention and would like to explore more. I started to play with the NuoDB beta 7 and I am finding it very interesting. There were a few more questions as well and I decided to write them in the following post to my earlier post.

nuodbconsole SQL SERVER   Follow up on Beginning NuoDB   Who will Benefit and How to Start   Part 2

The power of NuoDB becomes evident when you need more Transaction Engines to handle load.  You can add these to (and take them away from) a running chorus dynamically.  Transaction Engines in a chorus can run on a mixture of networked hosts – on a variety of operating systems, including ones in the cloud – as I mentioned above.  Let me quickly divert here, by saying that the idea that a database can span multiple computers and run across multiple operational systems is mind boggling to me.  You know that I am a geek but this is more than super-cool and a true revolution in database technology and architecture.

During beta 7 (and beta 8 as far as I know), the broker hands out the connections to Transaction Engines in a round-robin fashion.  NuoDB will provide different load balancing mechanisms as needed.  I expect that load balancing algorithm could get very interesting.  To browse NuoDB and test my SQL code, I use DBVisualizer as recommended by NuoDB.  SQuirreL is also supported, and your tool of choice likely works with very little configuration – as long as it uses ODBC or JDBC to connect.  There is also a command line tool called NuoSQL, which can run SQL in interactive or batch mode.  Another tool, NuoLoader, imports your CSV-formatted data.

This should be enough information to be dangerous.  I very much recommend that you give it a try and tell me how it goes.  By beginning to embrace NuoDB’s innovative SQL database concepts, I am sensing that we might be on to something very big that hasn’t happened in the database market in a long time. You can download the NuoDB beta software at this link.

Action Items:

  • Download and try out NuoDB (The same link will work for future versions of the product)
  • Leave a comment with your experiment

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning NuoDB – Who will Benefit and How to Start

nuodb SQL SERVER   Beginning NuoDB   Who will Benefit and How to StartI finally got some time to play around with the beta 7 release of NuoDB that I downloaded a few weeks ago.  Personally I don’t think NuoDB yet gives downloaders enough information on how to get started so I decided to tackle that here myself.

Before I get into the details, why bother?  Who will benefit from this beta?

IMHO, if you are working on developing a web-scale app that will require the supporting database to scale a lot, both out and in, then you should try this software.  In working with the NuoDB team, I haven’t had any issues pushing my test up to 50 nodes.  NuoDB has said that they will be offering even greater elastic scaling out/in in future releases.  If they do, then this software is going to be the answer for many next-generation SQL apps.

Second, developers who need to migrate an existing app onto the cloud will also benefit from NuoDB.   Let me state the fact that NuoDB is a cloud database that’s fully SQL compliant and offers the ACID guarantees we all have come to expect from a transactional database.  I haven’t tried migrating a SQL application myself but I’ve heard from a couple of other beta users who have and they didn’t have issues.  They indicated it was pretty easy.   It really depends on how many vendor-specific SQL extensions you have used.

OK, now that you know you are, or aren’t, someone who could benefit, where to start?

NuoDB seems to be doing a new beta every 4-5 weeks before they release the product later this year; so it may be that beta 8 is due out shortly.  Whatever the version, you can find the latest release here.   You will need to fill out a short form but approval is automatic.

Installation is as simple as it gets.  For Windows, you can download an EXE install. MacOS is a tarball. For Linux platforms, it is a rpm or deb file or a tarball.  Make sure you have at least Java JRE 1.6 installed.  Then add the <NuoDB>/bin directory to your system PATH.

The peer-to-peer, asynchronous nature of NuoDB lends itself to a configuration that is a little bit different from what you are used to with SQL Server or other “client/server” databases.  You provision your system by defining a NuoDB domain – a group of servers that can host NuoDB engine processes (they also call these nodes!) – by running an instance of a Java process called “NuoAgent” on each host.  At least one of these “agents” must be run in ”broker mode”.  A broker is the initial connection point for any application client.  It helps establish direct connections between the clients and Transaction Engines in the domain.

This brings me to the beauty of this database.  NuoDB separates transaction processing from storage by defining two types of database engine processes – the Transaction Engine and the Storage Manager.  If your app requires more throughput from the database, just add another Transaction Engine.  If you want durability of your data, add another Storage Manager.  Each Storage Manager maintains (and keeps in sync) an entire copy of your database.

A minimal system requires at least one NuoDB broker to define a domain.  Start a broker like so:

$ java –jar nuoagent.jar –broker –domain mydomain –password domain_pwd

NuoDB has a nice management GUI called NuoDB Console.  It lets you tap into NuoDB domains and start up (and manage) engine processes.  A collection of these processes is called a “chorus”.  Effectively, a chorus is a database.

Upon starting the Console for the first time, it will ask you which domain you want manage. In our example, that would be ‘mydomain’.  Select the domain and then “add process”.  Step through the wizard to define and start a Storage Manager.  It is here that you will define a chorus (database) name.  Run the wizard again to create a Transaction Engine in the same chorus.    That’s it!  You have a minimal database up and running.

Action Items:

  • Download and try out NuoDB
  • Leave a comment with your experiment

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Services Pack 2 for SQL Server 2008 R2 – Microsoft SQL Server 2008 R2 Service Pack 2

Service packs are very critical and important. In the industry I have seen many people waiting for the first service pack to arrive before moving to opting the product. I often see it as a good practice because there are some unknown bugs or missed enhancements in original product which are later covered in the SP2. I believe it is not limited to SQL Server but pretty much true across most of the softwares. Here is a single suggestion, you may delay adopting the product but must not delay in adopting service packs. As soon as they are released, grab them, test them on your development thoroughly and move to production server after validating.

Microsoft has recently released SQL Server 2008 R2 service packs. Please note these are for SQL Server 2008 R2 and not for SQL Server 2008. They will not work on SQL Server 2008 but will work only on SQL Server 2008 R2. If you have  have a fresh install of SQL Server 2008 R2, you just have to download and install SP2 and there is no need to install SP1 as this service pack is cumulative SP and contains all the previous upgrades of SP1.

Microsoft SQL Server 2008 R2 Service Pack 2

Microsoft SQL Server 2008 R2 Service Pack 2 Express Edition

Microsoft SQL Server 2008 R2 Service Pack 2 Feature Pack

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – 2 SQL Server Documentations Updates

SQL Server is a very fine product and the best part of it is documentation. What I do in weekend is just read the documentation or some weekend project. This weekend I decided to spend on reading documentation. Earlier I downloaded and installed SQL Server 2012 Install Kit during this weekend. Honestly, quite a lot I do not understand as the documentation is quite heavy on terminologies and but that is the best part as it gives us lots of learning!

Microsoft SQL Server Data Portability Documentation

The SQL Server data portability documentation explains the various mechanisms by which user-created data in SQL Server can be extracted for use in other software products. These mechanisms include import/export functionality, documented APIs, industry standard formats, or documented data structures/file formats. There are additional documentations available on Book On Line for further reading. Microsoft is providing access through open connections to its high-volume products—Windows Vista (including the .NET Framework), Windows Server 2008, SQL Server 2008, the 2007 Microsoft Office system, Microsoft Exchange Server 2007, and Microsoft Office SharePoint Server 2007—so that software developers, business partners, and competitors can better interact with these Microsoft products or invent new solutions for customers.

Download SQL Server Data Portability

Microsoft SQL Server Protocol Documentation

The Microsoft SQL Server protocol documentation provides detailed technical specifications for Microsoft proprietary protocols that are implemented and used in Microsoft SQL Server to interoperate or communicate with Microsoft products. The documentation includes a set of companion overview and reference documents that supplement the technical specifications with conceptual background, overviews of inter-protocol relationships and interactions, and technical reference information.

Download SQL Server Protocols

Download SQL Standards Support

Note: Some of the text are courtesy of MSDN.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Download SQL Server 2012 Developer Training Kit – Update July 2012

I just came across newly updated SQL Server 2012 Developer Kit Setup. It is quite convenient and there are many prefers to download setup kit instead of web installer. Developer kit is my favorite feature because it it is a single resource which gives a complete overview of the product in a nutshell. A developer can learn from many places – books, webcasts, tutorials, blogs, etc. However, I have found that developer training kits are the best starting point for any product. Start with them first, see what are the new features as well what is the new message a product is coming up with. The SQL Server 2012 Developer Training Kit includes technical content including labs, demos and presentations designed to help you learn how to develop SQL Server 2012 database and BI solutions. New and updated content will be released periodically and can be downloaded on-demand using the Web Installer.

Here is the updated Download SQL Server 2012 Developer Training Kit (updated in July 2012)

Developer kit also contains video as well details of how to run labs as well. I suggest every SQL Server Enthusiast should download this developer kit and install it. I believe I have tried most of the labs as well watched all the videos of this training kit. However, whenever I watch this video again I learn something new, which I have not come across before. I would like to know your opinion about what is your favorite feature in SQL Server 2012 and what is your preferred method of learning SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – 2 Whitepapers Announced – AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution

Understanding AlwaysOn Architecture is extremely important when building a solution with failover clusters and availability groups. Microsoft has just released two very important white papers related to this subject. Both the white papers are written by top experts in industry and have been reviewed by excellent panel of experts. Every time I talk with various organizations who are adopting the SQL Server 2012 they are always excited with the concept of the new feature AlwaysOn. One of the requests I often here is the related to detailed documentations which can help enterprises to build a robust high availability and disaster recovery solution. I believe following two white paper now satisfies the request.

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

SQL Server 2012 AlwaysOn Availability Groups provides a unified high availability and disaster recovery (HADR) solution. This paper details the key topology requirements of this specific design pattern on important concepts like quorum configuration considerations, steps required to build the environment, and a workflow that shows how to handle a disaster recovery.

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

SQL Server 2012 AlwaysOn Failover Cluster Instances (FCI) and AlwaysOn Availability Groups provide a comprehensive high availability and disaster recovery solution. This paper details the key topology requirements of this specific design pattern on important concepts like asymmetric storage considerations, quorum model selection, quorum votes, steps required to build the environment, and a workflow.

If you are not going to implement AlwaysOn feature, this two Whitepapers are still a great reference material to review as it will give you complete idea regarding what it takes to implement AlwaysOn architecture and what kind of efforts needed. One should at least bookmark above two white papers for future reference.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Weekend Project – Experimenting with ACID Transactions, SQL Compliant, Elastically Scalable Database

Database technology is huge and big world. I like to explore always beyond what I know and share the learning. Weekend is the best time when I sit around download random software on my machine which I like to call as a lab machine (it is a pretty old laptop, hardly a quality as lab machine) and experiment it. There are so many free betas available for download that it’s hard to keep track and even harder to find the time to play with very many of them.  This blog is about one you shouldn’t miss if you are interested in the learning various relational databases.

NuoDB just released their Beta 7.  I had already downloaded their Beta 6 and yesterday did the same for 7.   My impression is that they are onto something very very interesting.  In fact, it might be something really promising in terms of database elasticity, scale and operational cost reduction. The folks at NuoDB say they are working on the world’s first “emergent” database which they tout as a brand new transitional database that is intended to dramatically change what’s possible with OLTP.  It is SQL compliant, guarantees ACID transactions, yet scales elastically on heterogeneous and decentralized cloud-based resources. Interesting note for sure, making me explore more.

Based on what I’ve seen so far, they are solving the architectural challenge that exists between elastic, cloud-based compute infrastructures designed to scale out in response to workload requirements versus the traditional relational database management system’s architecture of central control. Here’s my experience with the NuoDB Beta 6 so far:

First they pretty much threw away all the features you’d associate with existing RDBMS architectures except the SQL and ACID transactions which they were smart to keep.  It looks like they have incorporated a number of the big ideas from various algorithms, systems and techniques to achieve maximum DB scalability. From a user’s perspective, the NuoDB Beta software behaves like any other traditional SQL database and seems to offer all the benefits users have come to expect from standards-based SQL solutions.

One of the interesting feature is that one can run a transactional node and a storage node on my Windows laptop as well on other platforms – indeed interesting for sure. It’s quite amazing to see a database elastically scale across machine boundaries. So, one of the basic NuoDB concepts is that as you need to scale out, you can easily use more inexpensive hardware when/where you need it.  This is unlike what we have traditionally done to scale a database for an application – we replace the hardware with something more powerful (faster CPU and Disks).

This is where I started to feel like NuoDB is on to something that has the potential to elastically scale on commodity hardware while reducing operational expense for a big OLTP database to a degree we’ve never seen before. NuoDB is able to fully leverage the cloud in an asynchronous and highly decentralized manner – while providing both SQL compliance and ACID transactions.

Basically what NuoDB is doing is so new that it is all hard to believe until you’ve experienced it in action.  I will keep you up to date as I test the NuoDB Beta 7 but if you are developing a web-scale application or have an on-premise app you are thinking of moving to the cloud, testing this beta is worth your time. If you do try it, let me know what you think.  Before I say anything more, I am going to do more experiments and more test on this product and compare it with other existing similar products. For me it was a weekend worth spent on learning something new. I encourage you to download Beta 7 version and share your opinions here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Download SQL Server Product Documentation

Today I just returned from Bangalore User Group Meeting. Attending User Group meeting is indeed fun and really great experience. The best part of the User Group is meeting like minded people and have a great conversation with them. During the meeting I was asked why one has to go online to access SQL Server Product Documentation. I can clearly see there can be many reasons for why one wants the documentation to be available offline. The reasons can be anything but not limited to

  • Company Firewall
  • No Internet (power failure, on road or disaster)
  • Internet Bandwidth Limitatoin
  • Company Proxy Issues
  • … and much more…

In this kind of situation one needs documentation which is offline and easily available in local computer. The Microsoft SQL Server 2012 setup installs only the Help Viewer and related components for managing Help settings. Setup does not directly install any documentation. All of the SQL Server documentation is available online.

Well, Microsoft has recently released Production Documentation available for offline users as well. You just have to download the executable file and follow all the instructions. Please follow the link below for download of the file. There is detailed instruction on how to download and install the documentation offline.

Product Documentation for Microsoft SQL Server 2012

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – SQL Server 2012 Upgrade Technical Guide – A Comprehensive Whitepaper – (454 pages – 9 MB)

Microsoft has just released SQL Server 2012 Upgrade Technical Guide. This guide is very comprehensive and covers the subject of upgrade in-depth. This is indeed a helpful detailed white paper. Even writing a summary of this white paper would take over 100 pages. This further proves that SQL Server 2012 is quite an important release from Microsoft. This white paper discusses how to upgrade from SQL Server 2008/R2 to SQL Server 2012. I love how it starts with the most interesting and basic discussion of upgrade strategies: 1) In-place upgrades, 2) Side by side upgrade, 3) One-server, and 4) Two-server. This whitepaper is not just pure theory but is also an excellent source for some tips and tricks. Here is an example of a good tip from the paper:

“If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.”

SQLServer2012big SQLAuthority News   SQL Server 2012 Upgrade Technical Guide   A Comprehensive Whitepaper   (454 pages   9 MB)

upgradenow SQLAuthority News   SQL Server 2012 Upgrade Technical Guide   A Comprehensive Whitepaper   (454 pages   9 MB)

There are so many trivia, tips and tricks that make creating the list seems humanly impossible given a short period of time. My friend Vinod Kumar, an SQL Server expert, wrote a very interesting article on SQL Server 2012 Upgrade before. In that article, Vinod addressed the most interesting and practical questions related to upgrades. He started with the fundamentals of how to start backup before upgrade and ended with fail-safe strategies after the upgrade is over. He covered end-to-end concepts in his blog posts in simple words in extremely precise statements.

A successful upgrade uses a cycle of: planning, document process, testing, refine process, testing, planning upgrade window, execution, verifying of upgrade and opening for business. If you are at Vinod’s blog post, I suggest you go all the way down and collect the gold mine of most important links. I have bookmarked the blog by blogging about it and I suggest that you bookmark it as well with the way you prefer.

Vinod Kumar’s blog post on SQL Server 2012 Upgrade Technical Guide

SQL Server 2012 Upgrade Technical Guide is a detailed resource that’s also available online for free. Each chapter was carefully crafted and explained in detail. Here is a quick list of the chapters included in the whitepaper. Before downloading the guide, beware of its size of 9 MB and 454 pages.

Here’s the list of chapters:

Chapter 1: Upgrade Planning and Deployment
Chapter 2: Management Tools
Chapter 3: Relational Databases
Chapter 4: High Availability
Chapter 5: Database Security
Chapter 6: Full-Text Search
Chapter 7: Service Broker
Chapter 8: SQL Server Express
Chapter 9: SQL Server Data Tools
Chapter 10: Transact-SQL Queries
Chapter 11: Spatial Data
Chapter 12: XML and XQuery
Chapter 13: CLR
Chapter 14: SQL Server Management Objects
Chapter 15: Business Intelligence Tools
Chapter 16: Analysis Services
Chapter 17: Integration Services
Chapter 18: Reporting Services
Chapter 19: Data Mining
Chapter 20: Other Microsoft Applications and Platforms
Appendix 1: Version and Edition Upgrade Paths
Appendix 2: SQL Server 2012: Upgrade Planning Checklist

Download SQL Server 2012 Upgrade Technical Guide [454 pages and 9 MB]

Reference: Pinal Dave (http://blog.sqlauthority.com)