You may have seen the announcements yesterday, but in case you haven’t, we had a pretty big acquisition announced at Microsoft yesterday that is great news for SQL Server. DATAllegro Inc., a provider of data warehouse appliances. DATAllegro specializes in large-volume, high-performance data warehouses. DATAllegro’s data warehouse appliance installations are some of the largest data volume capacities in BI. We’re using this acquisition to ramp up SQL Server data warehousing to high-scale deployments reaching hundreds of terabytes. Check out more info here:
Official press release: Microsoft to Acquire DATAllegro
More here about SQL Server 2008 Data Warehousing
I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 4:00pm - "Need for Speed":
Now it’s time to tune my systems. Before I can tune them, I need to track them. Sure, I can set up a bunch of Profiler Traces, queries against DMV’s, custom scripts and third-party products to set up a “baseline” DB on each system. But I don’t have to do that any more.
Now I just right-click the Data Collection system, create a central place to store data, and run a couple of wizards to start gathering all the data. And just what can I do with that? Well, based on a collection I’ve taken, I can walk through a problem capture, discovery, fix and source in just a few minutes.
In future posts I’ll detail how to do all this – but for now, it’s pretty cool to have that taken care of automatically.
I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny":
There are a few features in SQL Server that can be complex to implement. That’s often due to the nature of the feature – they are complicated things to do. So in SQL Server 2008 SSMS, we’ve added a few new dialogs and wizards to help you along with some of the more often used features. Two that are of note are the Service Broker and Partitioning. Service Broker gets a new set of dialogs that in essence script out a new SB application, and Partitioning gets its own Wizard to help automate the process.
I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny":
Sometimes it can be, well, a bit challenging to locate the objects you want to work on in SQL Server Management Studio (SSMS). You have to remember where the object is, and then drill down to find it. That can be a bit of a pain.
But with SQL Server 2008 SSMS, you can now click on a database or database object, and then look in the top right-hand corner of the Object Explorer Details (OED) pane. If you don’t see OED, just press F7 (we put that hotkey in as well). You’ll see a “Search” box – type in the name of the object, or even part of it using standard T-SQL wildcards like % and then hit the ENTER key. The object will be located, and you can right-click it to get a limited menu, or select “Synchronize” to get the full right-click menu. The interesting thing is that the search is cross-type – meaning that if you type in buck% you’ll get all of the objects that start with “buck”, no matter what they are.
One final note – if you select an Instance instead of a database, you’ll search all databases. This could take a while, but it can be a lifesaver. Also, you always have the “cancel” button.
If you attended TechEd Developers week you may have encountered the of the SQL Server Data Programmability team were announcing the Microsoft Sync Framework, or Project “Velocity”. As a DBA or developer, you’re likely to run into this technology in the future. Velocity is a distributed in-memory application cache platform for developing scalable, high-performance applications. Velocity can be used to cache any type of data that is accessed frequently online.
Velocity is fully integrated with ASP.NET, allowing developers to cache ASP session data without accessing backend databases. This makes it easy and efficient for developers to store things like shopping cart details, wish list items or user settings associated with each customer for a particular visit.
There are a lot of other great uses for this technology;
- High-traffic online retailers can use Velocity to cache their product catalog information. Caching this information would drastically reduce the number of requests for the same data from the database. In this case, not only would Velocity provide better performance and a better user experience, but it would also reduce the load on the database backend.
- Caching application data across the entire web farm. Velocity allows the information cache to be scaled similarly to how website hosting scales across a large server farm or across a cluster of machines. Velocity’s information cache can be scaled as if it were a single unified entity while providing high availability to the cached data.
- Lessening the development complexities that are often associated with high availability web sites by allowing any serializable CLR object to be cached and by providing access through simple cache APIs. Velocity can be configured to run as a service that can be accessed over the network or it can be run embedded within the application.
Make sure and check out those links to learn more.
It's Friday already - wow, where did the week go? OK, I promised a "Quote of the day" every week, so here it is:
If you think you’re too small to make a difference, try going to bed with a mosquito in the room - African proverb
I answer a lot of questions about SQL Server - on forums, in newsgroups, in presentations and so on - and many are "101", or basic questions. That's great! I don't have a problem with that at all - I enjoy helping someone that is new to SQL Server learn the ropes. Over the years I've collected some really great links that I can share with people about learning to manage SQL Server. I thought I would post those here, in the hopes that you'll pass them around:
Basics Podcast: http://edge.technet.com/Tags/Database+Administration/feed/ipod/
Free SQL Server Learning Videos: http://www.microsoft.com/sql/editions/express/learnvideos.mspx
General SQL Server Learning and Education: http://www.microsoft.com/sql/techinfo/default.mspx
General SQL Server Learning and Education: http://technet.microsoft.com/en-us/sqlserver/bb331758.aspx
Learning SQL Server using the Sample Databases: http://msdn.microsoft.com/en-us/library/ms124501.aspx
SQL Server for the Oracle DBA: http://www.microsoft.com/sql/prodinfo/compare/oracle/oracledba/quickguide/index.htm
SQL Server DBA Handbook (oldie but a goodie): http://www.microsoft.com/technet/prodtechnol/sql/2000/books/adminpoc.mspx
SQL Server Express – Sample Applications: http://www.microsoft.com/sql/editions/express/samplewebapps.mspx
Lots of free database schemas: http://www.microsoft.com/sql/editions/express/starterschemas.mspx
SQL Server Basics in Flash: http://www.eggheadcafe.com/videodemo/default.asp
Webcasts, Virtual Labs and Podcasts: http://www.microsoft.com/events/series/Technetsqlserver2005.aspx
SQL Server Whitepapers: http://www.sqlskills.com/whitepapers.asp
“Ask Stan”: http://sqlserverpedia.com/blog/
Basic Programming Training videos: http://msdn.microsoft.com/en-us/beginner/default.aspx
SSIS Learning Resources: http://sqlserver-qa.net/blogs/tools/archive/2008/04/07/sql-server-integration-services-ssis-learning-materials-and-web-resources-for-beginners.aspx
I was asked about the post I did on the multi-server query feature we put in SQL Server Management Studio 2008. Because it seems to render on the screen in a sequential fashion, the user thought the queries were issued one at a time from the Registered Server group. The answer is "No", sort of.
So here's what really happens: There is a pool of about 50 or so threads per CPU that we have to work with for those connections. We asked for all the servers at once, and that request is assigned to one of those threads, if it's available. That means that if you had two processors and weren't doing much else, you would see 100 queries issued almost simultaneously - which works out to be pretty parallel. The effect of painting the screen ends up being sequential because we hold the results until we get them all back.
And there you have it.
(Bonus tip: This feature works faster on systems with mulitple procs, as you can see.)
I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny":
With the stored procedure I created ready to go, I need to test it - in case it's long or complicated, or even spins off to another procedure, we now have the Transact-SQl (T-SQL) Debugger feature. This is something Microsoft put back in to SQL Server after having it years ago - but this time it's integrated right into SQL Server Management Studio (SSMS). All you have to do is bring up a procedure or set of statements, and then click the little green right-arrow in the toolbar or click "Debug" in the menu bar.
When you do, you're dropped into debug mode, and you can click the "Step Into", "Step Over" and so on buttons in the toolbar. You'll walk step-by step through the statements, and down below you'll get a list of the variables and their assignments. Priceless.
I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 1:00pm - "New and Shiny":
In my DBA day I finally get a break to do something I really enjoy - new development. I decide I need to create a new stored procedure, and in that procedure I'd like to use a system function. I'm sure you've faced this yourself. With Intellisense turned on in SQL Server Management Studio, as you type you're shown not only Transact-SQL syntax, but even the variable names you've created earlier in the script. I've found this feature has been invaluable in speeding me up in development.
Another cool part of Intellisense is that it helps me figure out the required parameters for those system functions. It's a small thing, but it helps a lot!
I collect quotes - I think I'll start sharing those on Friday. I got a new one today:
If you want things to stay as they are, things will have to change. - Giuseppe Tomasi di Lampedusa (1896-1957)
http://edge.technet.com/Media/Real-World-DBA-Episode-18-Connecting-to-SQL-Server/
This week:
In the news, the SQL Server 2008 Feature Pack for RC0 is now available.
In this week’s feature, we’ll talk about accessing SQL Server.
The web link is on deciding to use SQL Server or Microsoft Access,
and the tip this week deals with data providers.
In the News:
In the news, the SQL Server 2008 Feature Pack for RC0 is now available. This pack includes lots of add-ins for SQL Server 2008 RC0, including Backward Compatibility components and Data Mining add-ins and much more. Find more at http://tinyurl.com/6kjc9w.
Feature:
Whenever you connect to a SQL Server database, two broad technological areas are involved. One is the transport, and the other is the programmatic access. Let’s take a quick look at what each of these areas is responsible for. I’m focusing on SQL Server 2005 in this Podcast, since each version has software differences to handle each area.
We’ll keep the discussion simple and describe the process from the viewpoint of a client application, a connection to a SQL Server Instance, and the SQL Server Instance itself. Starting at the client end, the transport needs a way to “talk” to a server. Even if the SQL Server Instance is installed on the same system, the client uses a client network library to send and receive information. You can install this software from the SQL Server installation media, but you also have the connectivity built in to a Microsoft Operating System. I’ll explain more about that in a moment.
There are four methods that the client network library can use to send information to SQL Server. The first is the TCP/IP protocol, which is in use for most networks today. A TCP/IP network involves a set of unique numbers that identify each “host” or computing device on the network. You can learn more about it at http://tinyurl.com/2wkx.
A client can also use the “Shared Memory” protocol. This is useful when the client and the server are on the same hardware and don’t need to communicate across a network, or the system doesn’t contain a network interface hooked externally.
SQL Server 2005 clients can also use the Named Pipes protocol, which is a legacy network protocol used in some older networks. In general, it’s better to use TCP/IP on Microsoft networks, but there are times when the Named Pipes protocol can be useful. You can read more about it at .
Finally, you can also choose the Virtual Interface Adaptor, or “VIA” protocol, which uses a specific card address and a port on that card. You can read more about that protocol at http://tinyurl.com/636rny.
A client machine with the SQL Server tools can enable a particular protocol and set the order of precedence for each one to talk to the server using the Configuration Manager. Once you set up the client, you’ll need to ensure that the network functions between the client and the server.
On the server side, there is a set of network libraries as well. In some editions of SQL Server, the protocols need to be enabled. You can do that using the SQL Server Configuration Manager tool. This is the number one issues for connectivity – since SQL Server is shipped “secure by default”, sometimes these protocols aren’t enabled on the server, so the client can’t reach it. Always check the Configuration Manager tool to ensure you have enabled the protocols you’ll be using.
An interesting note is that the server network protocols don’t have an order. This makes sense, because whatever the client protocol is determines what the server will use for that connection. The order there isn’t important to the server, it just answers the protocol.
Inside the network packets between the client and the server, you’ll find the Tabular Data Stream, or TDS. This is an application-layer protocol used by SQL Server for data.
So that’s the transport area for connectivity. To enable applications to format the data and requests to be put on the network wire (or memory path), you need programmatic layers on the client. There are two major types of software libraries, or groups of code, that do this: the ones that come with the operating system, and the libraries provided by SQL Server or any of the programming languages from Microsoft.
All of the modern Microsoft operating systems come with a set of libraries that you can use in code to communicate with SQL Server. These are not as “rich”, or complete, as the ones that come with SQL Server or the programming language installations. You’ve probably seen these drivers in the “ODBC” icon of the control panel. ODBC, by the way, stands for Open Database Connectivity. For a while, SQL Server shipped “replacements” for these drivers, which combined with other files made up the Microsoft Data Access Components, or MDAC. This could cause your systems to be out of synch with each other, so it was sometimes difficult to ensure the versions of the drivers were the same on all systems.
SQL Server 2005 and the Microsoft programming languages install another set of drivers which can co-exist with the operating system versions, which makes it easier to keep everything in sync. There are two basic classes of these libraries: the SQL Server Native Client, or SNAC, and the SQL Client.
The SNAC libraries include Open Database Connectivity, or ODBC, which is one of the libraries that is also supplied by the operating system. This library is Win32 based, and is one of the most popular access libraries that developers have used over the years. Another library, called the Object Linking and Embedding for Databases or OLEDB, uses the Distributed Component Object Model or DCOM.
The SQL Client library includes the ActiveX Database Objects for dot net, or ADO.NET. This is the library delivered for the managed code set in the .NET programming languages.
Finally, there is an older library called the “Database Library” or DBLIB which has been deprecated for SQL Server 2005 and higher.
With that quick rundown, we now have a “peg” to hang some more advanced connection topics.
Web Link:
Sometimes you don’t need the power of SQL Server for a small, single user application. But how do you know when you should use one over the other? You can read more about this at http://tinyurl.com/2725zg.
Tip of the Week:
SQL Server includes lots of data providers for various platforms , from SQL Server 7 to Oracle and IBM’s DB2. For the best performance, make sure you run a comparison of these providers over the ones from that platform vendor. You can see large performance differences based on which one you use for a given situation.
You might notice that I blog quite often about security here, even though I'm on the management team. The reason is that over the years as a DBA I've seen so many shops that don't take care of the basics, so I think you just can't talk about it enough.
Microsoft just released a new security bulletin that addresses four security vulnerabilities involving SQL Server 7.0, SQL Server 2000 and SQL Server 2005 including MSDE, WMSDE and WYukon. Get it, read it, understand it - here's the link to the bulletin:
http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx
As always, put this on your testing server and test, test, test.
I gave a presentation at TechEd 2008 where I covered our new features in "A Day in the Life of a DBA". Here's more of what I showed for 10:00am - "Panic Button":
While I'm examining the state of performance on my system, I discover that one set of queries is monopolizing most of the CPU and Memory resources. If you're faced with this situation in SQL Server 2008, you now have the ability to "throttle" down the resources for a group of connections. Using connection metrics such as user name, host address and so on, you can create "Pools" that have a set amount of CPU percentage and memory sizes assigned to them. You then tie out the user to the pools, and that's as much as the user's connection will get. That way they run slower, but the other users don't suffer as much. No code changes are needed.
Also with SQL Server 2008, the highest edition allows you to "Hot add" CPU's to your system. A simple "Reconfigure with Override" statement brings them online into SQL Server.
If you're installing the latest release of SQL Server 2008, you'll probably immediately start looking for the Surface Area Configuration (SAC) tool. It's been removed, and replaced with new Policies that we ship with the Policy Based Management feature. You can read all about that here.
So why do it this way? Well, what this does is give you a single location, tool, and methodology to work with to set and control your system's state. Sure, it's a change, and I know we all hate change, but it's a good one - a single tool to do the same kinds of things is far better than lots of tools to do the same thing. So check out that link and try it on your test system today.
