In the first in a series of monthly articles, 'Confessions of a Sys Admin', Matt describes the issues involved in Change Management, and gives a simple guide.
Source Click Here.
It can happen at any time: You get a request, as Admin, from your company, to provide the contents of somebody's mailbox from a backup set as part of an investigation. The Recovery Storage Group is usually the easiest way to do this. It may either mean using the Exchange Management Console or the Exchange Management Shell. Jaap explains all.
Source Click Here.
It sometimes happens that Web Server controls that visualize data don't quite fit with the way that the data is actually held in the application. This shouldn't be an obstacle, as Ion Freeman points out. To prove his point, he shows how to use Nested Sets with Treeview.
Source Click Here.
Desmon explains the fundamentals of building a test lab for Windows servers and Enterprise applications from scratch using Hyper-V on a supported x64 machine. He even suggests tips and tricks to help make the journey smoother.
Source Click Here.
UK SQL Server User Group (Reading, 15th Jan) - Business Intelligence; Chris Webb (Creating a SSRS, SSAS & SSIS monitoring solution with SSIS, SSAS & SSIS) and Allan Mitchell (Data Mining - time prediction)
Business Intelligence - Creating an SSIS, SSAS & SSRS monitoring solution with SSIS, SSAS and SSRS; Data Mining - Marking Predictions over time Cost Free Registration Via UK SQL Server User Group Address Building 3, Microsoft Campus, Thames Valley Park, Reading, RG6 1WG Directions to Event Join us for another UK SQL Server User Group meeting. 5.30pm - 6:00 Registration and networking + Tea/Coffee with biscuits. Meet and greet. 6:00pm - 6:30pm Round Table discussion and Nuggets - ALL Take stock...(read more)
Source Click Here.
What's new for a DBA in SQL Server 2008; Nuggets and networking Cost Free Register Via UK SQL Server User Group Address Leeds Metropolitan University Directions to Event What's new for a DBA in SQL Server 2008? There are many great new features in SQL Server 2008. In this presentation Martin will demonstrate some of his favourite features that will be of interest to DBA including the new Auditing feature, Transparent Data Encryption, Change Data Capture, Resource Governor, Compression features, Performance...(read more)
Source Click Here.
UK SQL Server User Group (Edinburgh, 29th Jan) - Rob Carrol Microsoft PFE (Upgrading to SQL 2008) and Martin Bell (Resource Governor demo)
Upgrading to SQL Server 2008 - Rob Carrol PFE; Resource Governor demo; SQL Nuggets and networking. Cost Free Register Via UK SQL Server User Group Address Microsoft Office at Waverley Gate, Edinburgh Directions to Event Scottish Area SQL Server User Group Meeting, Edinburgh - Thursday 29th January Upgrading to SQL Server 2008 - Rob Carrol As a Premier Field Support Engineer for Microsoft, Rob is heavily involved with clients who are in the process or looking to upgrade to SQL Server 2008. In this...(read more)
Source Click Here.
NTFS fragmentation degrades SAN performance? Well it does for insane fragmentation....(read more)
Source Click Here.
A lot of developers have SQL Server and its different services running on their PC or notebook to develop or test BI solutions. Unfortunately this slows down your system quite a lot. To speed things up when not using SQL Server, I used to stop and start each service manual quite often.
Recently I found out that it's possible to start and stop all services at once with a simple batch file. It now only takes a couple of seconds instead of a few minutes and some annoying steps.
Copy/paste the following in a .txt file and rename it to .bat to make it a batch file, execute it by double clicking the file.
START SCRIPT:
NET START "SQL Server Agent (MsSqlServer)"
NET START "MsSqlServer"
NET START "MsSqlServerOlapService"
NET START "ReportServer"
NET START "SQL Server Integration Services"
STOP SCRIPT:
NET STOP "SQL Server Agent (MsSqlServer)"
NET STOP "MsSqlServer"
NET STOP "MsSqlServerOlapService"
NET STOP "ReportServer"
NET STOP "SQL Server Integration Services"
Source Click Here.
I'd recently built a two-node failover cluster for a customer using Windows Server 2008 and it's working really well. The customer wanted to extend the cluster by adding a third node and I came across a problem with the Full Text Search service following the SQL Server installation.
The new node was introduced to the failover cluster - no problem. I'd re-run SQL Server setup.exe on the new node for each of the three instances, then run Service Pack 2, plus CU7 to synchronise the SQL binaries on the new node with the existing nodes. I'd adjusted the Possible Owners and Preferred Owners to reflect the intended failover actions. Next I wanted to test failover of each instance to the new node to validate SQL Server started and there were no problems.
Everything worked fine, except FullText Search - which wouldn't start, the following appears in the Application Log:
The SQL Server FullText Search (SQLInst2) service depends the following service: NTLMSSP. This service might not be installed.
There is a mention of this problem in the following KB article, although the resolution is to install SP2 for SQL 2005 - which had already succeeded.
List of known issues when you install SQL Server 2005 on Windows Server 2008
After some further research and testing - I changed the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql$SQLInst2\DependOnService
The DependOnService showed two values RPCSS, NTMLSSP on the new node, but the same key only showed RPCSS on the existing 2 nodes (where the instances failover without problem). Removing NTLMLSSP from this registry key for all three FullText Search instances on the new node and restarting the server resolved the problem - and the instances now failover.
Regards,
Justin Langford
Source Click Here.
I was searching for a particular document for SQL 2008 and thought I'd list the links to what I found, there's some especially good white papers in Technet, I can recommend the T-SQL enhancements and the indexed view white papers especially: SQL Server 2008 White Papers http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx Technet SQL 2008 White Papers http://technet.microsoft.com/en-us/library/bb418496.aspx A list of blogs and other sites, sadly no Grumpy Old DBA http://msdn.microsoft...(read more)
Source Click Here.
Donald(Farmer) has posted his new years resolutions for his speaking engagements. http://www.beyeblogs.com/donaldfarmer/archive/2009/01/you_say_you_wan.php They're great.
I totally agree with the reduction of slides. My presentations last year had few slides and is something I'm going to commit to more in 2009. I remember seeing Mike Taulty presenting at a usergroup meeting and he had slides that moved rather than being static. I'm going to commit to moving the slides I do use, from text ones, to graphical ones.
Donald says he's going to stop using analogies. I love analogies so not sure I'll do this but ensuring they are relevant is something that one needs to do.
-
Source Click Here.
When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions There are many methods to know the Compatibility Level of a database using queries...(read more)
Source Click Here.
OK, this is rather short notice I know but the UK SQL Server User Group is hosting a BI evening event next week, on January 15th at Microsoft's HQ in Reading:
http://sqlserverfaq.com/default.aspx?item=event&itemid=139
Since this is the rumoured date for Microsoft laying off 15% of its staff, you might be advised to wear some wellies in case you step in a puddle of blood on the floor. Hopefully all the BI folks will be ok though...
Anyway if you follow the link you'll see that I'm one of the speakers: I'll be repeating the session I did at PASS last year on building a monitoring solution for Integration Services, Analysis Services and Reporting Services. Also speaking will be Allan Mitchell, on his new pet hobby of data mining. Hope to see you there!
Source Click Here.
Now public on Erland Sommarskog's website. Enjoy! Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)
Source Click Here.
This is a very exciting/confusing/glooming period for SQLServer Developers/DBAs
On one hand there is the 'cloud' which will take our precious boxes away from us and we won't be needed to manage it anymore.
On the other hand is an avalanche of technologies bundled with every release of SQL Server.
Back in the 2000 days all you really needed to know was
VB/VBA/VBS
DTS
T-SQL
MDX
SSAS
DOS
WSH
Profiler/QA and other tools
I can just see the skills required for a SQL Server DBA these days
VB/VBA/VBS
C#
SSIS
DTS
Powershell
T-SQL
MDX
SSAS
SSRS
SSNS
LINQ
Entity Framework
SSDS
SSMS/ Profiler/QA/BI Studio and other tools
With Kilimanjaro this list will only get longer.
Soon your IT shop will look like a hospital.
You need some SSIS with C#? Talk to DBA A
You need some Powershell automation with SMO? Talk to DBA B since DBA A doesn't know Powershell
You need some DTS? Talk to DBA C because DBA A and DBA B started to use SQL Server with version 2005 (9) and don't know anything about DTS
Is there really one super DBA who knows all these technologies? I don't think so; it is time to start specializing. Besides the stuff you absolutely need to know like T-SQL, the internals and maintenance, you will have to pick your direction.
So I ask you the reader: what brings the future for the SQL Server DBA?
Source Click Here.
If you live in the greater Boston area you might be interested in knowing that the New England SQL Server Users Group and Southern New England SQL Server Users Group have been quietly working for the past several weeks to create a SQL Server oriented Code Camp, using the PASS Community Connection framework. Following is the blurb we came up with to describe the event:
It's all about the data! Today's applications are increasing data-dependent and whether you are a developer, DBA, or BI professional, you need to know how to work a variety of data across a range of products. This free, full-day Code Camp-style event will feature three concurrent tracks throughout the day to satisfy any tech professional who is interested in the latest and greatest tips and techniques for working with SQL Server 2005 and 2008, the Microsoft BI toolset, and all of the new .NET data access technologies, including LINQ and the Entity Framework.
Interested in attending? Click here to join us! Register soon; space is limited and the event is starting to fill up. You can find a current list of the sessions that are planned for the day by clicking here. I'll post again once we have a full time table worked out.
Like all Code Camp style events, this one has an open call for speakers, which we'll leave open until early next week. If you're interested in presenting, please submit an abstract or two! The more speakers the better for this kind of community event. For instructions on how to do that, please see Chris Bowen's blog post announcing the Data Camp.
Please let me know if you have any questions. We're looking forward to a great event, and hope to see you there!
Source Click Here.
When I wrote the 2005 book, I promised that I would take the time to respond to reviews as much as possible. From the good ones if I didn't really believe that the person read the book (there were a few I have seen, but not too many people tend to write good reviews that clearly haven't read the book unless they are paid to.) In fact, I am constantly surprised with good reviews, mostly because it is so difficult to write a book. I just spent an hour yesterday tracking down 20+ typos that the Chinese translator found.
I will also comment on the less good ones that are posted by people who clearly don't like the material. You aren't required to like the book, and when I am writing it I purposefully will ruffle the feathers of certain groups of people (particularly the ones who don't believe normalization matters!) In fact, I think that the 2005 book was significantly better than the first primarily because of a few reviews that smacked me around and made me realize some of the errors in judgment that I made. My favorite review of the 2005 book was a private review that was scathing in a few important points that I applied to the 2008 book.
What burns my feathers is bad reviews that have no information whatsoever. I mean, take this review of the book on Amazon (http://www.amazon.com/review/R29BAGXMZMIC7Y/ref=cm_cr_rdp_perm):
Waste time to reading this book.,
"Since nobody publish a review for this book, I would like to say something. I have to say: Don't read it. It just waste your time."
Now, I won't make any kind of fun of the reviewers grammar, as it is very likely that he is not a native English speaker. But if I were to review a remake of Bio-Dome where Pauly Shore spoke with an English accent in Shakespearean-style rhyme and stopped watching the film 5 minutes inI could say more than this about than "it will waste your time." I will also admit that there are probably a million people that would plunk down a tenner and happily watch this movie (and not just because movie theater popcorn is so much better than you can make at home.) Part of a good book review is to identify the type of reader. If this person is a DBA, this might be that he read the first chapter and said "bleh," then fair enough. If he read the words "database" and thought that there would be dating tips, well, yeah, it would be a waste of time.
I might not be able to speak for every author out there, but I am sure that almost any author loves to get honest feedback on their work, particularly us who write trade literature. I want to target what people want to read, and with new editions likely for new versions of SQL Server, it is important to provide new/fresh/useful upgrades to the material. The best place to determine what to add are reviews: public, private , whatever, as long as they are from people who have read the material and know what they were reading.
As always, if you want to contact me, go to the contact form on my website: http://drsql.org/contactus.aspx (you can contact me anoymously there too) or email me at louis@drsql.org. Just please give honest feedback about what you have seen, not just post a review because no one has published a review yet.
Source Click Here.
It's hard for me to believe it, but I've been blogging about SQL Server for over four years now. For those of you who've been reading my blog all of these years, thank you. And if you've commented or added a few thoughts over the years, I thank you even more! It's hard to get up the courage and carve out the time to post in the first place, so adding a comment is always a nice validation that the blog is both being read and offering something of value.
I also want to say a special word of thanks to my friend Michele Crocket and the team at SQL Server Magazine for giving me the opportunity to blog there starting way back in December of 2004. If you're curious, take a look at my first (rather uneventful) blog post here. Over the years, I've written an average of two blog posts per week. But, for some reason, my favorite blog posts have little to do with a specific SQL Server tip or piece of practical advice. My favorite blog posts usually tend to be off-topic articles like the gathering of the first PASS board of directors, my series of posts about getting into the book writing business, and the shower that tried to kill me.
As we begin the new year, there's a lot to look forward to with many new innovations in the technology, SQL Server, that is the hub of our professional life. Drop me a note if you think of something you'd like to see get special attention as I blog. And, once again, thanks for reading my blog as it now enters its fifth year.
-Kev
Source Click Here.
The DBA cannot be sure that the sensitive data that is flowing to the database has been sufficiently protected. If sensitive data is compromised while it is in storage there is no doubt that the DBA will be the first questioned.
Source Click Here.
Session submission is now open for SQLBits IV. It will be in Manchester on the Saturday 28th March 2009. We are planning on having more sessions than before so if you submit a session you will have more chance of being chosen. To submit a session go to http://www.sqlbits.com/information/SessionSubmission.aspx ( you will need to register on the site to be able to submit a session)
Source Click Here.
Its shocking how many people don't even do the basics of ensuring your data is protected.
I've just read this article http://itknowledgeexchange.techtarget.com/sql-server/mirroring-isnt-a-backup-solution/ about a company that ran JournalSpace.com that relied on RAID to protect itself. NO BACKUPS.
Shocking. I was asking thother day about spares for raid arrays, if you are bothered about spares I would hope you have all your backups sorted as well.
I remember I was told that when the World Trade Centers were bombed in 1993 many companies went out of business because they didn't have backups of their data.
Just imagine if your company didn't have any of its data, it would be crippled, as happened with JournalSpace.com
So remember take backups and put them offsite.
-
Source Click Here.
This is a great list of demo tips from Buck . I would agree with Bill that you need to change the foreground hihghlighting to change
I would also add that I find I often end up in Notepad and the command prompt so you need to set the font size in those as well.
Fortunately for the command prompt you can have a Demo Command prompt that is configured with big text.
Then there is the use of zoomit. I haven't actually used it but I know I should, especially for those areas that you can't increase the font size. Although changing your font resolution to large sccale fonts 120 dpi can help, although I've seen dialogs look odd because of this.
Finally I would add the use of a multi desktop tool that means you only have one app on one desktop. This makes things look a lot smarter and provides an easy way to flip between the different applications.
I'm working on a process for saving settings and being able to easily restore them, because changing the font sizes can be a pain.
-
Source Click Here.
I was reminded this morning of how to rotate text in a report.
I've also developed some code to do it by use of graphics if you prefer the text to go in different directions. More on that later
-
Source Click Here.
Sean McCown has been given the MVP award in January this year. I did a google to find out somethings about him and came across a great article on SQL Server Central about great responses to interview questions from experienced DBAs it is so true though. What I find is people can have years of experience working with SQL Server but no knowldge of SQL because they've been doing the same thing for 10 years. They have other people that do design and development and performance tuning, so all they know is how to check the status of agent jobs.
Sean's got a few blogs http://weblog.infoworld.com/dbunderground, http://dbarant.blogspot.com and a tutorial site http://midnightdba.itbookworm.com/
Contgratulations Sean
-
Source Click Here.
The biggest feature of management studio that most people don't know about is the keyboard shortcuts for stored procedures.
You can configure a shortcut i.e. CTRL+1 to execute a stored procedure. Whats more if you have some text highlighted it can be passed to that SP as a parameter.
To configure go to Tools | Options | Keyboard.
You will see a set of shortcuts defined by default i.e. ALT + F1 for sp_help.
I set CTRL + F1 to sp_spaceused and CTRL + 3 to sp_helptext.
Whilst these are great they can be better. For instance if you have execution plans selected and you run sp_help you will have a huge number of execution plans generated. This causes a huge problem.
I've created a connect item to get the keyboard shortcut functionality extended and to allow selected text to be passed as a string. (Currently if you select Sales.Order and do ALT+F1 it will fail because of the . (period) in the selected text)
The connect item is here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=390612
-
Source Click Here.
When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions There are many methods to know the Compatibility Level of a database using queries --Method 1 EXEC sp_dbcmptlevel your_database_name --Method 2 EXEC sp_helpdb your_database_name --Method 3 SELECT name , cmptlevel FROM master .. sysdatabases WHERE name = 'your_database_name' --Method 4 SELECT name , compatibility_level...(read more)
Source Click Here.
Working out how to monitor Analysis Services usage is something I've spent a fair bit of time doing over the last few months; I'll get around to writing up my SQLBits presentation here soon, I promise, but in the meantime here's a quick blog entry on a related topic: how can you work out which dimension hierarchies your users are actually using in their queries? It's useful to know this for a number of reasons, for example if you're thinking of changing the AggregationUsage, AttributeHierarchyEnabled or AttributeHierarchyOptimizedState properties. Or if you're just plain curious.
The first step towards answering this question is to capture all the MDX queries that are being run, and you can do this by running a Profiler trace on the Query Begin event and saving it to a table in SQL Server in the way I described here. Once you've got the queries you then need to work out which queries mention which hierarchies and you can do this in SSIS using the Term Lookup transform in the data flow:
Unfortunately the Term Lookup transform is a bit picky as far as the input it can use. You can get a list of all the hierarchies in every dimension in every database in Analysis Services by running the following DMV query:
select * from $system.mdschema_hierarchies
To be able to use this in a Term Lookup I had to create a linked server in SQL Server pointing to Analysis Services, then building a view that queried the Analysis Services DMV using the OpenQuery function so:
select cast(hierarchy_unique_name as varchar(100)) as HIERARCHY_UNIQUENAME from openquery([AS], 'select * from $system.mdschema_hierarchies')
Having done this, and having run the SSIS package, you then get a table containing one row for each query/hierarchy combination:
Running a select distinct on this table of course gives you a list of all the hierarchies mentioned in your queries:
Using different DMVs would allow you to analyse different aspects of the queries - for example, you could work out which MDX functions were being used in the queries with the mdschema_functions DMV (similar to what Mosha was doing here, but maybe with a view to seeing whether your client tool was using 'bad' MDX functions).
Source Click Here.
First, an up front apology to Mosha - this is half in jest, half real. MDX is a powerful and useful language, but I find that its very existence has made my life very difficult when it comes to developing a solid Analysis Services solution. That's not to say I don't use it, or endorse it, but I worry greatly about its overall usage.
1) It tempts you by looking like SQL, but once your brought into it you realize how complex it is (if you don't have Mosha's MDX Studio, get it).
2) MDX allows you to do TOO much when it comes to a multi-dimensional structure, without first understanding multi-dimensional concepts. I've seen code that creates filters or subset cubes that overlap or end up cutting out measures implicitly. You have no warning about possibly undermining previous dimensional modeling - that would be a nice addition to see.
3) Along those lines, it enables sloppy dimensional modeling in the first place. "Throw it together, will add in things later". With a multi-dimensional database, it is very important to model it correctly, just like you would a relational structure. Unfortunately there are no Codd/Date rules of normalization, and often very little work done up front understanding the actually relationships and what the data is meant to represent. For example, instead of using a many-to-many cube dimension (what I call the dimesion to measure tie) to represent something like a bill of materials, I often see this done afterwards somehow via MDX calculations and such. Instead of going back to the dimensional modeling, the answer is usually "we can do this in MDX later".
4) Yet another language with its own syntactical oddities - damn you syntax, you will be the death of me!
5) I can't count the number of calculations I've seen in a cube that could have been done before the cube is loaded in the first place. An MDX calculation that does something like PRICE * QUANTITY = TOTAL SALES is just a waste of processing on the cube side. Remember, MDX is post aggregation - so the large the cube, the more dimensions it has and the level of drill down it allows all play a roll in the speed of things.
Those are 5 to start - anyone have anymore?
Source Click Here.
Every year I put out resolutions, but this year is a little different. I will post my SQL only blog resolutions here, and my personal ones here. Every year I find my ability to put out stuff is limited by my desire for sleep and fun, and this year is no different. So I am going to be realistic this time.I think.
1. Only collaborative works this year. I am collaborating with Tim Ford (http://ford-it.com/) on the oft mentioned DMV book for red-gate. I feel it will get done with or without me this time, but I am committed to making it happen. The MVP book work is mostly done, but there is a good bit more work to do before it goes to print for sure.
2. At least one SQL blog on this site every two weeks or more often. I have my pillar posts that are in progress, (one is ready for posting in a day or two), and I am going to post some of the DMV stuff along too, most likely.
3. Back on the forums more steady now that the holidays have passed. I try to stick to 3-5 posts a day, even when I am writing, but it can be laborious. Luckily the talent that is working the forums has greatly improved over the past three years.
4. Bring my back catalog of posts from drsql.spaces.live.com over to sqlblog in cleaned up/updated form, as well as posting them to my website drsql.org for future reference as well.
5. Keeping drsql.org more up to date with stuff I do.
6. Speak at least 3 times this year (user group (nashville.sqlpass.org), Devlink (devlink.net), and PASS, hopefully).
7. Work with PASS on social media to help make them a solid place to go to meet other SQL nerds, even those on other communities.
8. Work on my responsibilities with the Nashville SQL User's group to help us turn a corner to a thriving group. Right now we have a lot of potential, but need to do a bit more.
9. Read more. One thing about producing content is that it prohibits you from ingesting content from others.
See, realistic.
Source Click Here.
Session submission is now open for SQLBits IV. It will be in Manchester on the Saturday 28th March 2009. We are planning on having more sessions than before so if you submit a session you will have more chance of being chosen. To submit a session go to http://www.sqlbits.com/information/SessionSubmission.aspx ( you will need to register on the site to be able to submit a session)
-
Source Click Here.
I've submitted a couple of sessions for SQLBits IV
You can see the sessions that have been submitted so far by going here -Submitted Sessions
-
Source Click Here.
When I was a lad and starting out in work an learning about servers and disks I was told of large corporations that regularly replaced storage drives to mitigate against failures.
They would start after x years of buying new storage and start replacing drives. They would, by the time they reached 3 or 4 years have replaced all the drives and be looking to cycle through again.
Things have moved on greatly since I heard this story and wondered if anyone does this, given the proliferation of SANs and often the complete replacement culture I've seen in many places.
The other thing I often find in small places is the lack of spare drives being used in RAID arrays. Whilst I can understand that if, you have spare drives somewhere close, you actively monitor the drives. However in the event of a failure over the weekend a spare can save you in the even you are running a less resilient RAID array or are unlucky.
Do people think that with RAID 10 that the likelihood of getting 2 drive failures in the same mirrored pair is really rare and so don't need spares.
I've created a quick survey if you would like to participate. Its only got two questions so should take you less time that it has for you to read this post.
If the survey doesn't appear above Click Here to take survey Cheers
-
Source Click Here.
Pop quiz.
Your database is in bulk logged mode. You do a transaction log backup.
Can you restore the log to a point in time between the main backup and the log backup?
Scroll down for the answer
Answer: It depends
If you don't have any bulk logged operations then you can however the second you have a bulk logged operation you are stuffed, and you have to restore the whole log. So if someone has put a little SELECT INTO statement of a SSIS package that does BULK insert you won't be able to do point in time recovery.
So the lesson learnt is, only switch to BULK logged mode when you don't want to do point in time recovery.
-
Source Click Here.
Subscribe to:
Posts (Atom)