Latest Posts
Microsoft has
released SQL Server 2008, all editions! Brian Randell, reporting from TechEd South Africa this week, seems to have been the
first to get the word out beyond Microsoft.
It's available on MSDN right now. No idea when it will be on the shelves.
Don
Per an earlier post - here is a
link
to a full version of the table of contents for "SQL Server 2008 Transact-SQL Recipes." Feel free to post comments if you have questions about the table of contents or topics listed.
** Cross-posted from www.joesack.com **
I came home to find a hard copy of my new book waiting for me. This is always an exciting and strange moment to finally hold the finished product in my hand. I'm pretty happy with the result, and I feel it reflects the problem/solution theme I was aiming for. It is always a little daunting once a book is printed, because you have to hand it off to the world and hope that people find it useful. You kind of have to just give in, hope for the best, and move on to the next project.
It is a bigger book than last time (last book was 733 pages, this one is 839). Although it was an update - it still took 9 months, but they were high quality hours and I enjoyed a good portion of it.
Looking through it today - I was disappointed to see that two of my requested changes that I submitted to the publisher in June didn't get integrated into to Chapter 2 - so I'll blog about them here so you can know to look for them. There were some syntax changes that were made in the Release Candidate that differ from CTP 6. The two changes that didn't get in for whatever reason are as follows:
- Page 91 - the CTP6 version of the MERGE syntax, instead of the RC0 version was used. Here is the corrected, RC0 version of the syntax:
MERGE INTO HumanResources.CorporateHousing p
USING dbo.StagingCorporateHousing s
ON p.UnitNBR = s.UnitNBR
WHEN MATCHED AND s.IsRentedIND <> p.IsRentedIND THEN
UPDATE SET IsRentedIND = s.IsRentedIND
WHEN NOT MATCHED BY TARGET THEN
INSERT (UnitNBR, IsRentedIND) VALUES (s.UnitNBR, s.IsRentedIND)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
- Page 98 - the @partition_switch argument should be removed from sys.sp_cdc_enable_table
The rest of the RC0 changes I made and requested seem to be there, so I'm happy. We'll see if there are any RTM changes I need to blog about. With 800 pages - the possibilities are endless. :)
** Cross-posted on www.joesack.com **
My new book, "SQL Server 2008 Transact-SQL Recipes" is coming out in a few weeks (estimated publication at the end of July). As of July 1st, Amazon.com is selling it for $37.70. They don’t have a picture of the cover up yet, but it will have the standard bumble-bee color scheme Apress cover, so expect no surprises there. J
Although this was an update of my last book, it still took 9 months to write (plenty of weeknights and weekends holed up in my office). I updated existing content, and added content for both SQL Server 2008 and SQL Server 2005 SP2 additions.
Like my last book, I focus specifically on the Transact-SQL language. Even with the focus on T-SQL and not the GUI, the book wound up being greater than 800 pages. Also like the last book, I use a problem/solution format. The overall theme and mission statement for this book was:
"Look up what you need to do. Learn how to do it. Do it."
I'll have a downloadable detailed index link available after the publication date. That link will include the specific recipes for each chapter. In the meantime, here is a high level list of the chapters:
CHAPTER 1 SELECT
CHAPTER 2 Perform, Capture, and Track Data Modifications
CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking
CHAPTER 4 Tables
CHAPTER 5 Indexes
CHAPTER 6 Full-Text Search
CHAPTER 7 Views
CHAPTER 8 SQL Server Functions
CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors
CHAPTER 10 Stored Procedures
CHAPTER 11 User-Defined Functions and Types
CHAPTER 12 Triggers
CHAPTER 13 CLR Integration
CHAPTER 14 XML, Hierarchies, and Spatial Data
CHAPTER 15 Hints
CHAPTER 16 Error Handling
CHAPTER 17 Principals
CHAPTER 18 Securables, Permissions, and Auditing
CHAPTER 19 Encryption
CHAPTER 20 Service Broker
CHAPTER 21 Configuring and Viewing SQL Server Options
CHAPTER 22 Creating and Configuring Databases
CHAPTER 23 Database Integrity and Optimization
CHAPTER 24 Maintaining Database Objects and Object Dependencies
CHAPTER 25 Database Mirroring
CHAPTER 26 Database Snapshots
CHAPTER 27 Linked Servers and Distributed Queries
CHAPTER 28 Query Performance Tuning
CHAPTER 29 Backup and Recovery
I enjoyed writing this book; but most importantly I hope you find it to be a useful, practical reference.
In the last months I cross-posted from my blog on SqlBlog.com to SqlJunkies.com, but starting from today I will be only on http://sqlblog.com/blogs/marco_russo.
This is my last post on this blog on SqlJunkies.com. See you on SqlBlog!
I and Paolo Pialorsi have been interviewed at TechEd by Ken Rosen. We talked about our experience as book authors of Programming LINQ.
If you are interested in writing a book, or if you simply want to see our faces and hear our italian accent, you can watch the video available in both low resolution and high resolution. Enjoy!
Cross-posted from SQLBlog! -
http://www.sqlblog.com
I'm already in Orlando for TechEd 2008 Developers. Tomorrow I and Paolo Pialorsi will be at the TechEd bookshop for a book signing of our just released Programming Microsoft LINQ, scheduled at 4:00PM-4:30PM. I wrote a post a few days a go with the list of chapters included in the book. LINQ to SQL and LINQ to Entities are two technologies that are significative to access data, even if you don't have to use them in every possible scenario.
If you are attending to TechEd, meet us tomorrow at the bookshop to talk about LINQ!
Cross-posted from SQLBlog! -
http://www.sqlblog.com
Finally, the Programming Microsoft LINQ book is available. We (I and Paolo Pialorsi, the other author) updated the website that supports our books (http://programminglinq.com), where you can download the sample code of the book.
What’s in this book? Well, we tried to cover everything that was in RTM, but we also introduced technologies that are still in beta or in early CTP stages, like LINQ to Entities and Parallel LINQ. To give you an idea of the content, at the end of this post there is a list of the chapters included in the book.
Now, the next news is that I will be at TechEd Developers next week in Orlando. Feel free to contact me if you want to arrange a meeting talking about LINQ or SSAS. Moreover, I and Paolo will be at the bookshop for book signing on June 3rd from 4:00pm to 4:30pm. I hope to see you there!
In these days I'm already working on a new project, this time on Data Warehousing and Analysis Services. More news on this blog in a few weeks...
Programming Microsoft LINQ
· Part I LINQ FOUNDATIONS
o 1 LINQ Introduction
o 2 LINQ Syntax Fundamentals
o 3 LINQ to Objects
· Part II LINQ to Relational Data
o 4 LINQ to SQL: Querying Data
o 5 LINQ to SQL: Managing Data
o 6 Tools for LINQ to SQL
o 7 LINQ to DataSet
o 8 LINQ to Entities
· Part III LINQ and XML
o 9 LINQ to XML: Managing the XML Infoset
o 10 LINQ to XML: Querying Nodes
· Part IV Advanced LINQ
o 11 Inside Expression Trees
o 12 Extending LINQ
o 13 Parallel LINQ
o 14 Other LINQ Implementations
· Part V Applied LINQ
o 15 LINQ in a Multitier Solution
o 16 LINQ and ASP.NET
o 17 LINQ and WPF/Silverlight
o 18 LINQ and the Windows Communication Foundation
· Appendixes
o A ADO.NET Entity Framework
o B C# 3.0: New Language Features
o C Visual Basic 2008: New Language Features
Cross-posted from SQLBlog! -
http://www.sqlblog.com
I've been a big fan of using SQL Server aliases for a long time because it allows you to make physical location of SQL Servers transparent to the client machines. With SQL Server 2005 Microsoft introduced synonyms, allowing you to define logical names for objects in another database or even on another server. This could be among other things beneficial if you need to move some tables to another database. Instead of recoding your application, you can define synonyms and point them to the new location (I wrote an article for SearchSQLServer.com about synonyms recently, you can get more details there if interested) This week I realized that synonyms can have another great benefit. As you know, SQL Server Express has a limit of 4GB per database. If your database begins to grow close to 4GB, you can move one or more large tables to another database on the same server, create synonyms in the original database and point them to the new location. I tested it on my SQL Server Express instance and it does work as expected. So with this knowledge, this limitation might become less of an obstacle for you to consider SQL Server Express.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
I'm still late with blog reading and sometimes I discover interesting things one or two months later than the original posting. I just read the SQLCAT analysis of the several solutions available to handle multiple parent-child hierarchies within a single dimension. The multiple hierarchies pattern described in my many-to-many paper has been used (with some variations) and now I have the "SQLCAT certification" that this model is faster than others!
I think there is space for improvement in this area and the many-to-many space is still a relatively unexplored space. If you have experience adopting those models, please share your knowledge - and if you are shy, write me directly and I'll post the interesting data.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
It's been a while since I blogged but I had a circumstance yesterday that I thought was noteworthy. Recently my employer cut our staff to about 1/4 of what it was nine months ago, so all of us in "management" are now hands-on. I've inherited DBA work (since we cut the DBA), something of which I've not done much in the past eight years during a BI career. I have always appreciated what competent DBA's do for those of us in BI development. I'm sure I'll appreciate it more as time progresses.
As the title suggests, I committed an egregious error. I was working on a dev server and needed to reload a database which hasn't yet gone to production. I inherited the database and the application from a consultant (yup, cut him too) and it hasn't been fully loaded. As part of the work, I thought I'd build an "initial load" procedure and add it to the documentation. One of the methods for initialization is to create the database with a script, so I had SSMS write a script. I told it to script the database logins. Did I read the script before punching the go-giddy button? No. In a few seconds, the script errored out saying that I didn't have permission to create logins. Huh? Of course I do! I'm a sysadmin. A few more minutes of sleuthing revealed the disaster: I had deleted every single login in that server. Every last one, including myself. I had even whacked Builtin/administrators.
Shortly thereafter I realized we didn't have the sa password. I had completely locked myself out of my own server. What to do? I considered a full rebuild of the server, but there were 17 user-defined database on the server. Additionally, the majority of those databases were for a dev instance of BizTalk 2006, and while I'm not an experienced DBA, I worked with our DBA during initial installation of BizTalk 2006 and knew that it wasn't backed up or restored conventionally. Plus, there was a dev SharePoint installation using the SQL Server as well and I knew even less about the restore procedures for that. I was felling a little sick at that point.
A quick Web search revealed that Microsoft has left a trap door in SQL Server 2005 for this problem. You may find a simple and correct procedure for resetting a lost sa password when you cannot otherwise get access to the server, in the following link: http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx. I did exactly what Raul Garcia told me to do, and it worked! Restart the server in maintenance mode and then add a user to the the sysadmin role with sp_addsrvrolemember.
Next, I ran only the portions of the offending script which created logins. But was I done? Hardly. Inspection of the script (in which I asked for descriptive headers) told me that all SQL Server logins would be recreated with a random password and automatically disabled. I only had four of those, but I had to find the application passwords and make them match, and then enable the accounts. I also noticed that the associations between those logins and their database users seemed to be incorrect. I reset those with SSMS and they seemed to be working fine.
Next, I noticed that none of my backup jobs were running. SQL Agent could not access SQL Server. I use the same domain account to start both the SQL Server service and SQL Agent, but I had wiped out the permissions of that domain account. I fixed the problem by adding a login for the service account to SQL Server and gave it sa rights. I'm sure this is not the best possible way I could have solved that problem, but I can go back and fix it later. The highest priority of any DBA is to safeguard the company's data and this was only a dev server.
Finally, the SQL Server log showed that the BizTalk accounts could not log on to SQL Server. The message shown in the log was something like this:
Login failed for user '<domain>\<domain user>'. [CLIENT: 10.1.2.17]
Error: 18456, Severity: 14, State: 16
State 16 seems to indicate that a login has no permissions in a database which it is querying. Running sp_change_users_login with the "Report" option showed nothing. All of the BizTalk users had proper associations in their databases, just as SSMS said they did. Furthermore, we got the Windows password for one of the BizTalk Windows accounts and logged in to the server. We were able to connect to all the proper databases and query tables, just the permissions said we would. In other words, BizTalk was having no real problem connecting to SQL Server and querying its databases.
So, what kept causing the problem? We noted that the errors only occurred after restarting the SQL Server service, and then only one time. A ha! Is it possible that BizTalk was trying to connect to SQL Server and access its databases before the restart recovery is complete? Yes! Had I been more observant, I would have seen this in the log file. We put this to the test by stopping the BizTalk services on the BizTalk dev server and restarting the SQL Server service. Sure enough, the errors disappeared. It was nothing more than an order of operations issue. BizTalk tries to connect very quickly when it loses its SQL Server connection. It was trying to access a database before recovery was complete.
So that's the saga. Here's what we learned:
- Don't let Dave be your DBA on production servers if he can't stop destroying dev servers.
- Always have current backups. We did, so I knew I could get back if the method described herein didn't work.
- BizTalk is a very complicated system. You'd better practice restoring it because if you really have to do it, it could take hours just to know the process for recovery.
- Always record your sa passwords and securely store them.
- Read database creation scripts BEFORE you execute them. Five minutes of reading may save four hours' work.
- Microsoft has provided a way to create a new admin account on SQL Server even if you are boneheaded enough to completely sever access to SQL Server.
I'll be a speaker at the European PASS Conference 2008 next week in Dusseldorf. I will talk about advanced dimensional modeling using many-to-many relationship. The content is based on "The many-to-many revolution" paper I wrote two years ago. If you will attend that conference and you already used some of the models I described in the paper, I will be happy to get direct feedback from you.
My session is scheduled on Wednesday at 14:30. Unfortunately, I will not have much time after the session because I will have to go to the airport - for this reason, contact me in advance if you want, I'll be at the conference starting from Monday.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
I have decided to move my blog from the sqljunkies site to
sqlblog site. The transition started about a year ago. During this year I was posting every blog on both sites. This transition period has ended now, and from now on all the posts will be on sqlblog only. I apologize for all the inconvenience that it causes, but sqljunkies had too many problems to deal with, and the current owners didn't seem interested in fixing them. The sqlblog owners, on the other side, were were responsive and supportive, making sqlblog much more attractive solution for the blog publishers.
The latest post is
Take advantage of FE caching to optimize MDX performance.
Yesterday one customer of mine encountered the issue described in KB933836 and posted also by Chris Sells.
The issue is that the deployment of a SSAS database always overwrite object permissions defined on database objects like cubes. The "keep existing roles" setting of deployment wizard does not preserve those information. I needed to solve the issue and I simply made a simple program that creates an XMLA file with object permissions that I need to restore after database deployment. I used LINQ and XML Literals in Visual Basic - I wrote a book about LINQ and I have to find a reason for this, but I really think that LINQ saved my time.
I will write a more complete article about this issue one day, may be... by now, if you have the same issue, you can contact me to get the code I wrote. If you don't need it, look at the readability of LINQ in a case like this.
If I will receive some request, I will consider to publish this simple tool.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
Well, I think it’s time for a new post here in my blog... Yes, I’m still alive and still working in the MS BI space at Avanade... Many things changed in the last months so this blog was not on my top priority list, but maybe this will change again...
So I just want to publish some thoughts about the “Current Day” problem in SSAS... There are quite some articles about that and what I want to tell you is nothing more than bringing two posts together...
Mosha wrote quite something about this task... http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx. How to use VBA to find out the current date and how to map this with your dimension... At the end he mentions performance issues with this... And that might be right... Since this is a calculation which will be run in each MDX you send to the server there is nearly nothing it can cache... Mosha’s recommendation is to use some fixed member you change daily...
So what can you do? I came across this article... http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/8/Default.aspx. Here you find some code how to build up a simple application which changes your actual MDX script on your server...
I didn’t have time to test it but it should be pretty straight-forward to build up a tiny tool which updates a set with the actual date in your MDX script. So you have that fixed set and your performance should get better...
If someone likes to implement it and give some feedback, I’ll be more than happy to publish it...
This is my last post on SQLJunkies. I'm moving to sqlblog.com - welcome to my new
blog. It's empty meanwhile but not for long. Since nice part of my RSS feeds subscription belongs to sqlblog.com bloggers (
Kalen Delaney,
Adam Machanic,
Denis Gobo,
Linchi Shea etc), I was more than happy to receive an offer to move there. So... see you in
my new blog.