wsspectacular

DBA script: how to quickly assess all of your SQL instances

2008-05-16T20:35:00Z

Hi All,

I'm sure most of you who play with SharePoint also wear the DBA hat from time to time (or the other way around). I have been working with a customer who has hundreds of instances scattered across the enterprise. This script customized to answer several questions, but mainly to report on the patching level of each instance. Note that this is not a discovery tool; you have to know the instance names ahead of time, and have to have access to them.

Anyway, I find it comes in handy when you have lots of instances to manage.

SQL Server Auditing Tool

This is a custom VBScript I developed to quickly audit a number of SQL Server instances at once. The script uses SQL DMO libraries to connect to SQL 2000 and 2005 instances on the network, using Windows authentication to connect and gather properties of each instance to include:

Instances:

Name and SQL version (also indicating service pack)
When the instance was installedo The version and service pack of the Windows OS

Databases:

Name, file location, file size
Tally of disk space consumed by all databases in instance

Paste the following code into a text file and save it as DBChecker.VBS. You can then call it from a command line using CSCRIPT. Don’t just double-click on the VBS file because it will output the results in a series of Windows popups, which is annoying as hell.

You can “pipe” the output to a text file, e.g. CSCRIPT DBChecker.vbs sql01\sharepoint sql01\tigerpaw sql01\tigerpawtest > dbcheck.txt

You can then paste the comma-delimited text into word and convert it to a table for reporting. Or you can open the txt file in Excel.

To run, this script requires a local instance of SQL Server or MSDE. It uses those libraries to instantiate. If you have SQL 2000/MSDE, it will only connect to other SQL 2000 instances. If you want to connect to SQL 2005 instances, you have to have SQL 2005 (which can connect to both SQL 2000 and 2005 instances).

' Script name: DBChecker.VBS
' -- SQL Server auditing script
' -- Version 1.3a Written 5-16-08 by Greg Burns
'
' Description:
'               This script enumerates all databases in a SQL server instance,
'               then calculates the size of each database, and displays the
'               path to the database files on disk. The Version of each instance is
'               also displayed.
'
'               You can specify a
'               space-delimited list of SQL Server instances and the script will
'               process all of them in sequence.
'
' Usage: CSCRIPT VBSIZE.VBS [<instancename>] [<instancename>] [<...n>]
' Notes:
'               1. Instance name uses the following syntax: HOSTNAME\INSTANCENAME
'               2. To capture output to text file, use the redirect,
'                               e.g. "CSCRIPT DBChecker.VBS myserver\myinstance > dbcheck.txt
'               3. Leave <instancename> blank to assess only the default SQL instance.
'               4. This version of DBChecker uses Windows Authentication (SSPI) to
'                               connect to the SQL instance. If you attempt to
'                               connect to a SQL instance or DB for which you do not have
'                               login access, the script will abort with an
'                               'unspecified error.'

wscript.echo "Database Size and Location Reporter"
wscript.echo " Size = Total Size of Data File + Transaction Log of DB."
wscript.echo "-------------------------------------------------"

'Configure array based on command line arguments
'               If no arguments, assume local hostname
If Wscript.Arguments.Count = 0 Then
    arrComputers = Array(".")
Else
    Dim arrComputers()
    For i = 0 to Wscript.Arguments.Count - 1
        Redim Preserve arrComputers(i)
        arrComputers(i) = Wscript.Arguments(i)
    Next
End If

For Each strComputer in arrComputers

'Connect to instance:
                strDBServerName = strComputer
                Set objSQLServer = CreateObject("SQLDMO.SQLServer")
                objSQLServer.LoginSecure = True
                objSQLServer.Connect strDBServerName

'Instance information
                wscript.echo "SQL Instance: " & strDBServerName
                wscript.echo "Version: " & objSQLServer.VersionString
                wscript.echo "Number of databases: " & objSQLServer.Databases.count

'Table header
wscript.echo "Database, Size (MB), Path"

'Iterate through all Databases in instance
for i = 1 to objSQLServer.Databases.count

                                set objDB = objSQLServer.Databases(i)
                                intSize = objDB.Size
                                intTotalSize = cdbl(intTotalSize) + cdbl(intSize)
                                strDBName = objDB.Name
                                WScript.Echo strDBName & ", " & intSize & ", " & objDB.PrimaryFilePath

'Post total size of all databases in instance
wscript.echo " Total: ," & intTotalSize & ","
wscript.echo "-------------------------------------------------"

intSize = 0
intTotalSize = 0

'Next Instance
Next

Posted on SharePoint Blogs

Connecting Visual Studio 2005 to Team Foundation Server 2008

2008-05-14T18:43:00Z

Hi All,

I had a heck of a time finding all the information I needed to complete this task, so I thought I'd put it all together in one place, in case any of you are ever tasked (like I was) to implement TFS 2008.

I’ve figured out how to get Visual Studio 2005 Professional to connect to and create Team Projects on Team Foundation Server 2008. There are a few patches to install, but it works!

1. Install Team Explorer 2005

This adds the basic functionality of the Team Explorer to the vs 2005 environment. You get the ability to open and create Team Projects.
However, attempting to create a Team Project on a TFS 2008 server, you get the following error:

This is due to the fact that you’re trying to create a workspace on a WSS 3.0 site instead of a WSS 2.0 site. There is a patch available. But in order to install the patch, you have to re-apply Visual Studio 2005 SP1.

2. Install (or re-apply) Visual Studio 2005 SP1

Note: there are two components to the service pack, which will run separately. The first is the general VS 2005 update. Then a second wizard runs to update the Team Explorer.

3. Install hotfix VS80sp1-KB932544-X86-ENU.exe

This will configure the Team Explorer to properly create projects on TFS 2008 sites.

Posted on SharePoint Blogs

When Datasheets go bad

2008-04-14T15:27:00Z

I had deployed WSS at a customer site, and they were really enjoying it, making particular use of the Datasheet view of lists, which they used as a sort of shared spreadsheet. Then, suddenly, one day it stopped working everywhere, on every machine. What would happen when you tried to view any list in Datasheet view is a quick redirect to Standard View, with a message at the bottom that said:

"The list cannot be displayed in Datasheet for one or more of the following reasons: A datasheet component compatible with Windows SharePoint Services is not installed, your browser does not support Active X controls, or support for ActiveX controls is disabled in http://<website>."

I Googled and researched and experimented but did not find any answers to my problems.

Here are the salient details:

The server hosting WSS is a domain controller, and no office components are installed on it.
The workstations are all running Office 2003 Standard (Excel and Word, no MS Access)
Shortly before this happened, I had attempted to create a GPO to add the WSS site to all users' Trusted Sites,so they wouldn't be nagged for a login. I don't think it worked correctly. I am mentioning this not becaause I now think it caused the problem, but because I chased it for so long as a possible culprit.
This issue appears to have begun affecting all workstations at the same time.
No error appears in any event log or WSS log.

I opened a Microsoft case on it, and they isolated the problem to the client, and not the server (as I had assumed). I had been leaning toward the server as the source of the problem; how else could it have affected all workstations at once? But it turns out that the Datasheet view uses a library called STSLIST.DLL, which is actually a Microsoft Access component for SharePoint (and not Excel, as I had thought). But you shouldn't need MS Access to be able to use STSLIST. They had me try a bunch of different fixes, including:

Repairing/reinstalling Office 2003. No joy.
Installing/Re-applying Office 2003 SP2 (no joy)
Downloading and registering a new version of STSLIST.DLL (11.0.8200.0), no joy.
Installing or upgrading to Office 2007, any edition: SUCCESS.

Yes, installing Office 2007 solved the problem on any workstation we put it on. However, the customer balked at being forced to upgrade all her workstations, since Office 2007 is expensive. WSS should not require Office 2007. Remember Good, Better, Best? There ought to be a way to make it work with Office 2003, like it was working originally. Microsoft agreed. But since we had a viable (albeit expensive) solution, and the fact that the lists could be edited in Standard View, the case was deprioritized to "pure research".

Finally, the MS engineer got back to me and said he had seen this problem occurring at several other sites, and that it was now a known issue. They have apparently come up with a fix for this, in the form of a public hotfix, which you can download here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;949670

The KB article does not mention my specific problem but the MS engineer said what was happening is that, when Office 2003 and 2007 components are installed side by side on the same workstation, the WSS libraries were pointing to the wrong Office libraries. So this fix is supposed to take care of it.

I have not had the time to test this out, but I thought I'd post it here anyway in case some of you were seeing the same issue elsewhere. I'll know by the end of the week if this is the answer to my problems.

Posted on SharePoint Blogs

Jonas Salk and InfoPath 2007

2008-04-07T18:17:00Z

Hi folks,

I'm lucky enough to work at a company that rewards innovation and independant thought. For my annual performance evaluation, I was handed a six-page "self-evaluation" form. It was actually a Word 2003 document intended to be printed out and thrown in a file cabinet, but that's so... twentieth century, don't you think? I couldn't help myself. I took the form and converted it to an InfoPath 2007 form and submitted it via email to my boss. He was impressed.

Some of the improvements I made:

Each question is its own "section", which can be added or removed from the form to create customized evaluations. For example, I am a developer, but for non-developers, you could omit the "Adheres to company coding standards" section.
I assigned a point-value to each response. There are three possible "scores" per question:

Below Satisfactory (1 point)
Satisfactory (2 points)
Above Satisfactory (3 points).

Depending on the response, I used conditional formatting to adjust the shading of each section based on the numeric value of each response:

1 = Light Pink
2 = White
3 = Light Green
By default, this shading is not displayed when the form is printed, which results in a cleaner hard copy.

The evaluation form was split up into three groups: Company-Wide issues, Job-Specific Tasks, and Self-Improvement.

The first two sections are where the scoring is done. Each of these sections tallies up their subtotals and compares it to the Low, Average, and High scores possible.
The last section adds up the aggregate score. Managers can use these scores to determine how the employee's strengths and weaknesses balance out.
I was going to try to implement a graph to give a visual representation of all this, but there is no built-in charting functionality in InfoPath. I thought I might experiment by dropping in an Excel charting object, but I didn't have time to figure it out. I wanted to finish this within a day.

The cool thing about InfoPath is that, when you post it to a SharePoint document library, your data fields can be "promoted" to appear in the list, and views can be created based on these values. So now the managers can organize employees by their various scores and drill down into the details.

Anyway, each of the questions has a field for "comments", for which I used a rich-text field. As a lark, for one question I put the answer below, more to test the formatting functionality than anything else. Although wordy, I felt it answered the question, so I left it.

----------------------------------------------------------------------------------

Voluntary outside learning
Definition of this benchmark: Takes time outside of work to keep up on developments in the IT business. Magazine subscriptions, reading, web sites etc are sources for this learning.
Employee Performance Level: Above Satisfactory

Comment:

Jonas Salk, who invented the cure for polio, once said "the greatest reward for a job well done is the opportunity to do more work." It speaks volumes about the man, not to mention his work ethic. When I was younger, I never really identified with that sentiment. Work was simply something to be endured and gotten over with, so you could spend your energy on something fun. It wasn't until I began working with computers that these words suddenly rang true to me. With computers, each problem becomes a riddle to be solved, but it is a problem that allows you to take it apart and analyze by pure deductive reasoning. Computers reward insight and attention to detail; it makes those who excel in this field truly mystical and mysterious to the rest of the computing public. I suppose the key to finding satisfaction in your work is never feeling like you are "done". Each morning I get up and actually look forward to the day. I study and research and evaluate technology on my own time, not for the mundane pursuit of certification or accolade. I study it to know it better. Even now, after all these years of working in IT, I don't feel "done". I feel frankly unqualified, behind the curve, inadequate… but people do seem to be happy with my work. Very early in my career I had the mistaken notion that my employer and coworkers expected me to know everything. When asked a question or handed a task, I felt I was supposed to already know the answer, simply by merit of being "the computer guy". I was irrationally hard on myself, and this didn't serve me well because these raised expectations went unfulfilled and lost me a job. It was a painful lesson, but I lost my fear of saying "I don't know" when asked a question. I learned to give my answers in percentages and estimates. I use the word "might" a lot. And I never stop studying.

Jonas Salk had it right. Doing what you enjoy isn't work. It is a labor of love.

----------------------------------------------------------------------------------

It was a pretty good performance review. They gave me a raise.

Posted on SharePoint Blogs

SharePoint Backups Won't Start

2008-03-20T16:40:00Z

Hi All,

I ran across this problem at more than one site. When you go into Central AdministrationàOperationsàBackup and Restore and attempt to queue a SharePoint backup, it appears to simply sit there without ever starting. The status message reads “Status: Preparing current backup/restore job. If the backup/restore job does not begin after five minutes, make sure that the SharePoint Timer Service is running.” Normally the job starts within seconds, but now it never begins. If you click “Timer Job Definitions” you see that there is an object named “Backup/Restore”, but if you go to Timer Job Status, you see that it is not running.

Clicking “Refresh” is about as effective as repeatedly pushing the elevator call button to make it hurry up.

No errors or messages appear in the event logs. This is unusual because SharePoint is very prolific in its errors when something is wrong. Even delving into the SharePoint logs at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\ bears little useful information.

I’ve seen this both on MOSS and WSS servers.

Well, I did a little digging and I found someone else who is having the same problem.

http://www.eggheadcafe.com/software/aspnet/29497541/wss-30-backup-problems.aspx

It appears that the recent Daylight Savings Time patch (or the subsequent clock change) has screwed something up in the SharePoint timer. What’s going on it that the job is starting very, very late. Furthermore, once it starts, it apparently takes a longer time to finish. A couple of people reported that when they modified the DST clock settings on the server hosting their WSS sites so that it would not “Automatically adjust clock for Daylight Savings Time”, the problem vanished.

I haven’t been able to verify this, since I am currently at a site where the WSS implementation is sitting on a domain controller. Rolling the clock back on a DC isn’t a smart move.

Have any of you seen this issue?

Posted on SharePoint Blogs

What to do When User Profiles Don't Have Email Addresses

2008-02-29T06:58:00Z

Today, at a customer site, I addressed a problem that annoyed users more than usual. It seems that they can't sign up for alerts or assign each other tasks, and they get an error screen that offers to fix the problem, but leads to a dead end. I evaluated it, and created this write-up:

Among the many pieces of information collected and presented by SharePoint, some of this information comes directly out of Active Directory. AD was always intended to be a directory first, a central repository for information about users, phone numbers, email, etc. I’ve seen phone systems that tap directly into AD, for example. But most places I’ve been, AD is simply treated as a place to store user accounts, and much of the information on each account is left blank.

SharePoint’s strength (or weakness, if you’re a cynic) is that is depends on AD for certain pieces of information. MOSS periodically queries AD and updates all user profiles with this information, such as who works for whom, phone numbers, department information, and email addresses.

Problem:
It’s this last piece of information that, when missing, breaks some functionality in SharePoint. In order for people to be able to sign up for alerts (e.g. to be notified when a document is changed, an item is added to a list, or status changes on a task), or be assigned tasks, or a multitude of other collaborative processes, the email address field must be populated in AD. Otherwise, they get an error like this:

And if they click the friendly link to set their email address, they get this (sorry for all the redaction):

…which is to say, a completely useless page where they can’t change anything.

Snickering about Microsoft aside, this is an intentional design. SharePoint is supposed to pull this property from the user’s AD account, and even if I set the field to allow users to edit their email, it would be overwritten by default every time the AD query was run.

Sharepoint has extensive property mapping, and the “Work E-mail” field is mapped to the AD “mail” property, which is editable on the “General” tab of the user’s AD account:

You can see that even though I can bring up my non-administrator account, I can't edit most of the properties. It has to be done by someone with elevated permissions.

There are a lot of other pieces of information that SharePoint can use that are typically left blank, as well:

The Organization tab is used to bring a neat feature into SharePoint: it will use this information to create a mini-org chart on each user’s profile, so you can see a user’s supervisor(s) and peers at a glance.

So at this site, where there are a large number of users and administrators typically didn't bother to fill out the complete properties when creating users, the result is that SharePoint becomes less useful--or even broken. The organization recently installed an Exchange server, but it was incorrectly implemented. I believe the reason no one’s email address is current in their AD profile is a problem with the MS Exchange implementation at this company. When Exchange is installed, it adds numerous properties to a user’s profile, and I pretty am sure that this email field on the General tab is automatically populated when the user is assigned a mailbox.

Since this company isn’t set up with a typical configuration, this property has to be populated manually by an administrator. I proposed three possible solutions:

Undertake a project to do a mass-population from a CSV file, using CSVDE or a similar scripting tool.

Actually the company had been looking at a tool that would do this for them, but had not gotten a budget together.

Unmap the email field so that it is not refreshed whenever there is a profile import task performed by SharePoint (schedule is run once daily). Also, set SharePoint permissions to allow users to edit this field.

Problem: this will require a great deal of administrative overhead because email addresses are subject to change, and users may not be inclined to edit their own information, which will lead to an inconsistent user experience.

Allow users to submit a request for helpdesk to update their active directory profile information.

In the end, we chose option #3. Using InfoPath 2007, I created a simple web-based form to allow users to enter all the information (per the screenshots noted above) and submit it via (plain text) email to the helpdesk mailbox. I believe this is the most straightforward approach and it helps the IT staff because they won’t have to track down lots of contact and HR information. Since not everyone is using InfoPath 2007, I published the form to the MOSS site to be rendered as a web form, and specified that the form submitted via email not use an attachment; it was a static HTML table.

By and large, this took care of the problem. The MOSS site currently has light usage, so the volume of user requests for IT to update their profiles would also be light. When IT gets around to purchasing their AD-automation tool, this form method can be retired.

Once again InfoPath saves the day! I think I will write more about it in an upcoming post.

Posted on SharePoint Blogs

I installed Vista SP1 and All I Got Was This Lousy T-Shirt

2008-02-29T03:32:00Z

Despite having some misgivings, I went ahead and installed Vista SP1 on my laptop (after making the appropriate funeral arrangements and updating my will). As a Microsoft Partner, I have access to the download ahead of the general public. It surprised me that Microsoft did not originally plan to make it available to developers and partners early, so they could test it. But I suppose enough people complained bitterly that they caved in and gave us access.

Anyway.

I went into it with several expectations:

1. That it might do something horrible and irreversible to the operating system, based on shoddy drivers or a third party program.

2. That it might resolve the unrelenting lag experienced when opening My Computer or a network share

3. That it might resolve the most annoying of all my Vista-related problems, which is that the laptop hangs when coming out of standby and must be hard-booted.

4. That it might add some interesting functionality to the OS.

I was wrong on all but one of these assumptions. It did not kill my computer. It has not noticeably improved performance (although I am still evaluating this). And the OS seems to behave exactly as it did before. The only new thing I’ve seen so far is that the RDP client has some extra information. All of which, I guess, you’d expect from an service pack. You don’t want sweeping changes, you just want added stability.

And stability I got. I am still testing this, but it looks like the standby-lockup issue has gone away.

Anyway, I just wanted to throw my test-case out there. My laptop is loaded with specialized software, 64-bit drivers, and connects to numerous networks. It went off without a hitch.

The one thing I didn’t like about it was that it took over 2 hours to finish (not counting the time it took to download). I have dual Turion processors and 4GB of RAM. You’d think it would be a little snappier. I can only imagine how long it will take on a less-powerful machine.

The installation rebooted the computer several times. After the first reboot it did not come all the way back to the login prompt. It remained in the “pre-installation environment” where it displayed such helpful messages as “Service Pack Part 1 of 3 installing. Do not turn off your computer”. At one point a command window appeared and displayed status as it copied 12,000 files and updated registry keys.

The event logs are full of lots of new entries, none of which look exactly comforting, but don’t appear to be errors either.

So far, this seems like a safe upgrade.

Posted on SharePoint Blogs

How not to set up Sharepoint Search

2008-02-03T19:09:00Z

This is going to be a quick little post where I confess to doing something stupid. I should have known better.

We are currently prototyping a MOSS 2007 Enterprise solution for a government organization. This customer has a fairly large public website, and a similarly large intranet site. While demonstrating how to configure search scopes, I created a scope pointing at the external site, and changed its settings to "unlimited" without really thinking about what that meant.

Well, what it means is, it's a good way to fill up your server's hard drive, and a good way to annoy other departments of the government. It turns out that in "unlimited" mode, Sharepoint crawls every link, then keeps crawling links found on destination pages, branching out until, I assume, it has indexed the entire Internet. I also learned that Sharepoint doesn't handle the ROBOTS.TXT files correctly, so sensitive information could be crawled along with everything else. I couldn't believe this, until I found a post here that confirms it: Sharepoint only looks at the root of the site for ROBOTS.TXT files, and ignores them wherever else they may be:

Observations

During our testing we discovered the following.

1. robots.txt file is cached for 24 Hours following it's first request by the crawler. The implication of this is that changes to robots.txt require either a restart of the Office Search Service or a delay of upto 24 hours before they are respected by the gatherer.

2. Placing a robots.txt anywhere other than the root of a website is completely ineffective.

For example. http://www.website.com/folder/robots.txt will be ignored

So after running for a weekend, the following Monday I received a much-forwarded email from irate webmasters in another city who wondered what the hell my MOSS server was doing crawling every directory on their site, even in places specifically flagged by the ROBOTS.TXT file (that upset them more than anything else).

I also saw that the Search database had grown by seven gigabytes over the weekend, which brought my Sharepoint server to its knees (the VM we were using had limited disk space).

So, let my life serve as a warning to others. Don't use the "Unlimited" scope setting unless you know where every link on your website goes.

Edited to add:

I forgot to mention the resolution to this. One of the customer's biggest problems with their Intranet and Public website is search integration. For an external site, probably the best solution is to use a Google Site Search, which basically embeds a Google search field on your webpage and filters the results to your organization's URL.

This is better, I think, than pointing Sharepoint at it, because first of all, Google handles the ROBOTS.TXT files correctly. Second of all, especially if you have a large site, Google does all the heavy lifting and even archives versions of the web pages. If you wanted to, you could drop this functionality into a Sharepoint Search Center page and have the Google field sit next to your Sharepoint field.

For the customer's intranet, it is another matter entirely. A couple of years ago, they were running both webs on a beleaguered NT4 server running IIS4. I know, most of you are cringing. This server had been hacked in the past, but it had been recovered and locked down and was limping dutifully along. We migrated all content to IIS6 without much difficulty, but the one thing that couldn't be migrated was the search solution. In IIS4 there was a built-in script to allow basic user searches. Actually, it worked pretty well. But in IIS6, Microsoft stripped it out, perhaps out of concern for security, and there was no similar functionaliy. We explored and evaulated numerous open-source and third party replacements, but they were either too expensive, inaccurate, or simply didn't work as advertised. The most functional solution was the FrontPage search solution that used a web bot to index content, but it turned out it never did this automatically; you'd have to re-run it every time you updated the website (which was often).

Now this customer has MOSS 2007, and I'm glad to say that this is going to solve their problem once and for all. Now we have the ability to create search scopes, target types of information, and go far beyond any functionality they ever had before.

Posted on SharePoint Blogs

Why WSS? Why not MOSS?

2008-01-27T01:04:00Z

I've been demonstrating SharePoint a lot recently and it's got me thinking about the differences between MOSS and WSS. Windows SharePoint Services has come a long way since version 2.0, but the jump in functionality between 2 and 3 should have gotten a couple more version numbers, in my opinion. It's like moving from Office 2000 directly to 2007. There's so much more there, and it's all free. Well, "free-ish". You still need to have a Windows 2003 server, as well as Active Directory if you really want to make it useful.

I've been pushing my company to develop a sharepoint business offering. We have lots of small and midsize customers who could benefit from the collaboration tools, workflow, and communication capabilities. For a small group of people, WSS is a perfect fit. If there is no immediate need to scale, WSS is my first choice for more customers. Even if it is a smaller group inside of a large organization.

But sales people often don't see things that way; they want to sell comprehensive solutions, so they push the full MOSS server, which is exorbitant and overkill for SMB.

I don't know how it is where you live, but SharePoint is what I call "the coolest thing you never heard of". It's simply one of those things people overlook. Or, more commonly, it's something one of the administrators reads about and decides to install, and the users completely ignore it. So I find myself explaining SharePoint an awful lot. Once people understand that it's not just a web server, and that it's free(ish), and their existing Office 2003 and 2007 products already work with it... they tend to get excited.

I have one customer that is a retail furniture store. They had a few problems they complained about:

There are a few Macintosh users on the network, who develop multimedia and marketing materials. Is there a way for these users to share their files with everyone else?
All the stores are required to post their daily/monthly totals in a way that all the other stores can see them.
There needs to be a way to communicate important information to all managers and salespeople, and allow them to browse past announcements.

They had mostly been using email to pass files around and spread the word. Gmail is pretty good about large attachments, but some files were over 20MB, so there were running into a barrier there.

Naturally I recommended a simple WSS site, using a Team Workspace template. I showed the customer how to import their Daily Sales spreadsheet into a custom list in Datasheet View, with column totals. I showed them how to use Document Libraries to share files and even attach metadata to them. I showed them how to use the Content Editor web part to stream the WMV files. I showed them how to use Announcements and Alerts to post price changes, recalls, and stock alerts, and notify key personnel when content had changed.

This simple solution was exactly what they needed; a perfect fit for WSS. WSS design, deployment, and customization is a great gig, if you can get it. You just need a sales staff that knows how to push it.

Here are a few things you can show your customers to get them interested in WSS:

Demo Video of the WSS Workspace

http://office.microsoft.com/en-us/sharepointtechnology/HA102055631033.aspx

Note: this video was apparently moved after it was published. There used to be links to it from the main SharePoint page (http://www.microsoft.com/sharepoint) but they are broken. Anyway, this video is pretty good, moves quickly, and gives an excellent introduction to many of the things WSS and SharePoint can do.

WSS Demo
http://www.wssdemo.com/default.aspx

This site is a heavily customized WSS workspace. I sometimes show it to customers because it can give them an idea of what they could do, if they wanted to enhance the web experience or even create an extranet. It also has the Microsoft Sharepoint Training Kit installed, which is nice.

Sharepoint End User Training Videos

TraingSpot has 12 downloadable videos for End Users to come up to speed on basic concepts. There's a lot of other stuff, too: SQL Server 2005, Reporting Services, etc ($39.95)
Microsoft has the Office SharePoint Server 2007 Training Kit, which is designed for server administrators to install on an Office SharePoint Server site to help end-users learn about Office SharePoint Server. Or you can download the Standalone version, which runs nicely from your desktop. (free)

These are just a few resources; I will post a longer list when I get a chance.

One thing that has me excited is Windows Search Server 2008 Express. It is still in beta, but it is basically a Windows Sharepoint Services 3.0 installation with all the search functionality from MOSS 2007 Standard, and is free. The only limitation I have seen is in scalability. Search Server also has a supported upgrade path from WSS 3.0. Coolness!

Posted on SharePoint Blogs

SQL Replication gotcha!

2008-01-22T01:45:00Z

I know this is not strictly a SharePoint issue, but since I haven't seen it posted anywhere else, I thought I'd write about it here.

First, although I have been using SQL Server in its various forms since Version 7, I do not claim to be a "guru"... which is a good thing, because I screw up just as handily as the next guy. This weekend, I discovered several things:

SQL Server 2005 errors are not well-documented.
The replication tools in SQL Server are less than useful in isolating problems.
A call to Microsoft Support is not a panacea.

On 1-18-08, I was working with another consultant to support the comparison of two large databases before a major load of data was to be performed. They had asked me to set up a linked server so that they could run all queries side by side.

I created a linked server, but it became apparent that since we were using Windows-Only security with NTLM, and the user was running the queries from his workstation, he had run into the double-hop limitation and his credentials were not being passed to the back-end linked server.

We played with the Linked server properties for a while but couldn't get it to work (I didn't know why, at the time).

I then decided to copy over the remote database, and remove the linked server. When I tried to delete the Linked Server, I encountered an error saying that it couldn't be removed due to replication configuration. Rather than leave the linked server connection open, I simply set the security "Not be Made".

I didn't know it at the time (nor did anyone else) but SQL Server replication uses Linked Servers in its own way (invisibly). If there is a Linked Server set up for one of its subscribers, it will use that. If you disable that linked server, it will break replication.

This is why, Between the publisher instance and problem server, none of the subscriptions were working. However, all the subscriptions between the publisher and a second subscriber were still working.

We were unaware of any replication issues when we later decided to enable AWE memory management on the server. After rebooting, we noted lots of replication errors in the Application Log:

Event Type: Error
Event Source: <instance>
Event Category: (2)
Event ID: 14151
Date: 1/21/2008
Time: 11:10:08 AM
User: <Domain user identity for SSIS>
Computer: <Publisher server>
Description:Replication-Replication Distribution Subsystem: agent (null) failed. Server <Subscriber server\instance>' is not registered at server '<Publisher server\instance>'. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

In the main SQL Server log, we noted SQL Error 14151, Severity 18, State 1. We also noted SQL error 20053. The problem persisted even after both servers have been rebooted. They began as soon as the SQL Agent service starts. The Application log was flooded with "14151 " events. If you created a Replication subscription, it would create successfully but will never update itself. It would continually fail and retry and fail. In the SQL Replication monitor, a tracer token revealed high (infinite) latency for Distributor-to-Subscriber. Naturally, we concluded that the AWE memory configuration had somehow corrupted replication, but didn't know why. I researched the errors but could not resolve the issue. And by this time I was totally off into the weeds. This is, of course, where I called Microsoft. We spoke to a very nice Indian man who had not the first clue why this was happening. We spent Friday, Saturday, and Sunday on the phone, but were no closer to a solution Monday morning. The customer was becoming very nervous because this is a production server, and it had to be back in production by Tuesday. We spent Monday morning (MLK Day) on hold, until we discovered we were in Hold Hell (they had put us on hold until the engineer came into work at 6PM). The customer and I discussed options. The error led me to believe that there was a problem with Replication configuration, so... what if we uninstalled and reinstalled Replication for this SQL instance, and then recreated all the subscriptions? It seemed plausible. However, although everything went smoothly, the errors returned as soon as we created our first subscription. Same problems: errors flooding the Application Log, infinte latency in Replication monitor, failed SQL Agent jobs. Finally, we got another Microsoft Engineer on the phone, but I noticed he approached the problem the same way, checking the same pieces of information. It was this query that made me sit up:

select * from master.sys.sysservers

This listed all the Linked Servers, which I had been messing with on Friday. I asked if Linked Servers had anything to do with Replication and the MS engineer said yes, replication actually creates its own connection using Linked Server technology, and if there is a Linked Server already set up, it will use that.

It was a quick deduction that the change I had made on Friday, where I had changed the security from "using the login's current security context" to "not be made", had killed replication... but only to one subscriber. This also explained the mysterious Server <Subscriber server\instance>' is not registered at server '<Publisher server\instance>' error.

Resolution:

I set the existing Linked Server back to to using "Be made using the login's current security context"

Something to Note:

Neither Microsoft engineer had had the slightest inkling that Linked Servers might be the culprit here, and it was only because I was paying attention that we were able to solve the problem. A good thing, too, because there was talk of reinstalling Windows altogether.

So, although this was ultimately a human-induced problem, we were able to get the server back into production in time for business on Tuesday.

Posted on SharePoint Blogs