SQLJunkies Blogs

Synonyms and SQL Server Express

Roman — Sat, 03 May 2008 04:15:43 GMT

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

Multiple Hierarchies from SQLCAT

marcorusso — Mon, 21 Apr 2008 16:52:18 GMT

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

Deleted All Logins to SQL Server 2005 and Lost sa Password: You Did WHAT? And BizTalk 2006 Is Using It?

drodabaugh — Fri, 11 Apr 2008 10:46:00 GMT

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.

Many-to-Many Session at European PASS Conference 2008

marcorusso — Fri, 11 Apr 2008 08:50:30 GMT

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

Take advantage of FE caching to optimize MDX performance

mosha — Fri, 28 Mar 2008 23:48:00 GMT

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.

LINQ to extract object permission from SSAS

marcorusso — Fri, 28 Mar 2008 10:01:33 GMT

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

Thoughts about the “Current Day” problem in SSAS

tpagel — Tue, 18 Mar 2008 04:37:00 GMT

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...

So long and thanks for all the fish

mz1313 — Sat, 01 Mar 2008 05:16:00 GMT

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.

Happy Launch Day!

RickH — Wed, 27 Feb 2008 17:04:34 GMT

It's Official, today!

We have a party for SQL Server 2008 in LA! The product may not be shipping, but that won't stop us from having our party.

Seriously, Today marks the beginning of a more useful Launch than the 2005 launch. What am I saying? The Launch Events will probably be more educational for you this time around because there hasn't been as much focus on having a lot of events on 2008 as of yet. Signup today for a launch event near you!

Cross-posted from SQLBlog! - http://www.sqlblog.com

Adios SQLJunkies

marathonsqlguy — Sat, 23 Feb 2008 23:09:41 GMT

Thanks for getting me started but it's time to close the door.

Allen

Cross-posted from SQLBlog! - http://www.sqlblog.com

In case you did not hear, PASS Call for Speakers is now open!

RickH — Fri, 22 Feb 2008 12:53:27 GMT

In the latest PASS Community Connector, there is a call for Speakers for this year's summit in Seattle in November. If you have never been to Seattle in November, it really makes you want to learn more about SQL Server in the breakout sessions. ;-) But seriously, it is a great venue to meet other SQL Server professionals and meet the MSFT who actually write the code for SQL Server itself. You know that since MSFT is located in nearby Redmond that the place will be very accessible to MSFT developers and managers.

Also, did you know that the abstracts that are submitted are reviewed/selected by a committee of PASS Volunteers just like you?

Join other industry professionals and help build the SQL Server community as a 2008 PASS Community Summit speaker in Seattle, Washington, November 18-21. Act now and submit an abstract by the March 28^th Deadline.

All speakers receive:

Complimentary registration to the 2008 PASS Community Summit
Industry recognition
Networking opportunities with thousands of attendees

Microsoft employees are encouraged to submit presentations using the online submission website. Please note that any Microsoft submissions will be reviewed and abstract status will be determined by Microsoft.

For more information, please visit our Call To Speakers website at http://calltospeakers.sqlpass.org/ or e-mail any questions to calltospeakers@sqlpass.org.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Change Tracking and Change Data Capture

marathonsqlguy — Thu, 21 Feb 2008 15:33:34 GMT

Tonight's meeting of the Ohio North SQL Server Users Group will include the Detroit SQL Server Users Group as well. Here's the agenda:

5:30 - 6:00 PM - Early Arrival/PASS Intro and Presentation Tips and Techniques - Allen White
6:00 - 6:10 PM - Get Pizza and Settle/Coordinate Live Meeting with Detroit
6:15 - 7:15 PM - Bill Wolohan - SQL Server 2008 Change Tracking and Change Data Capture
7:15 - 7:30 PM - Q/A & Wrap Up

Go to http://www.bennettadelson.com/sql and click on the link "reserve your seat".

Allen

Cross-posted from SQLBlog! - http://www.sqlblog.com

Start PowerShell in Management Studio

marathonsqlguy — Wed, 20 Feb 2008 13:00:19 GMT

In the February CTP of SQL Server 2008 a new feature has appeared in SQL Server Management Studio. You now have the ability to right-click on an object in the Object Explorer window and open up a PowerShell window. In this window you can navigate the database structures much like you can a disk file subsystem, or like you can navigate the registry in PowerShell.

So, let's say you right-click on your server name and open up a PowerShell window. You can then issue the command:

cd Databases/AdventureWorks

Now you're pointing to the AdventureWorks database. Using PowerShell in this way allows you to browse the Server Management Objects (SMO) tree structures. (By the way, you'll have to remember to use proper case when browsing the SMO objects. They are case-sensitive in this environment.) Now, you can type this:

dir Tables

What returns is a list of the tables in AdventureWorks. It's kind of neat idea, and definitely fun to play with. I haven't found a productive use for it yet, but it's too new for me to make a judgement yet. I like it, but I don't yet know why.

Let me know what you think.

Allen

Cross-posted from SQLBlog! - http://www.sqlblog.com

PASS Europe 2008

marcorusso — Wed, 20 Feb 2008 10:05:26 GMT

It's a shame I haven't blogged for two months, but I've been really (I mean - REALLY) busy finishing the upcoming Microsoft Programming LINQ book. Now I'm coming back to my BI world and the first news is that I'll speak about SSAS models based on many-to-many relationships at the European PASS Conference 2008, April 14-16 2008, Neuss, Germany (Europe, of course...). For those of you who want to know what I'm going to talk about... you can read The Many-to-Many Revolution paper I wrote more than one year ago. It is based on SSAS 2005, but there is nothing to add with SSAS 2008 too.

In reality I and Alberto developed some variation of the base models I described in the paper. In particular, we had great success with the Balance Reclassification model (sorry, we still don't have an article about it) that allows defining multiple parent-child hierarchies on a balance sheet, also using sign rollup operator (which is not natively supported if you have a many-to-many relationship with a parent-child hierarchy). Unfortunately, we desisted using it in a Gb-sized fact table for performance reason. But it is working well in many other typical scenarios, where balance sheet fact table has a granularity that gives a lower size of the fact table.

Let me know if this is an argument (Balance Reclassification model) that is interesting for you - I could give some anticipation in a blog post or in a short article.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Transaction log backup deadlock

ACALVETT — Tue, 19 Feb 2008 23:10:42 GMT

My blog has moved to: http://sqlblogcasts.com/blogs/acalvett/
The full version of this post can be found here.

Recently we started to see deadlock errors when backing up our transaction logs. The "important" part of the error is shown below.

Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.

What this meant was that the transaction log backup was occurring but the entry in the msdb was not being made as it was being chosen as a deadlock victim so we investigated the cause of the problem as we had some processes that used this information to copy transaction logs to other servers and we needed it to be complete.

We used trace flag 1222 to output the deadlock information to the error log and found the culprit to be a Microsoft stored procedure called "sp_delete_backuphistory" that is called by SQL 2005 maintenance plans when you use the "History cleanup task" and tick the "Backup and Restore History".

To read the complete version of this post go to here.