Sql Server User Group on Tuesday 10th March 2009

Wow, a large crowd and many questions.

The topic was SharePoint for Sql Guys

Was trying to present three things but so many questions we barely got through the first bit.

The three parts were:
What you need to tell your SharePoint Administrator
What you need to do
What tools can help you.

The feedback was quite good with most liking the topic etc

Will now need to add my scripts and presentation

Would recommend you run the reports that monitor the server or use settings to check the fragmentation
See….http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

You are probably better having a quick look at:
http://technet.microsoft.com/en-us/library/cc964305.aspx

sql server reporting services 2008 windows 2008 and sharepoint can all live together

Finally had the time to suss this out.

First you need to know the tricks to get to the hidden sharepoint admin settings and then there's one little config setting that will stop nearly every attempt to get it to work regardless of whether you use SharePoint integrated mode or native mode.

All I can say, is alot of people would strangle whoever created the sql server 2008 reporting services install because this one setting is just ridiculous.

And to think I thought it was IIS7 that was causing the issue!

Was getting errors like:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

and rsInvalidDataSourceReference

Regards,

Tom Bizannes

SharePoint and Sql Server

Sydney, Australia

http://www.macroview.com.au

Reporting Services and SharePoint Integration on Windows 2008 server

Had fun trying this on a windows 2008 box.
Note this also applies for setting up on a vista box....
Everything worked after hitting each bit a few times!
So important to get permissions set, root site collections set properly and IIS7 configured right before installing anything.

So the steps to install in order are:
Set up IIS7 with all the correct settings:

Common HTTP Features:
Static Content
Default Document
HTTP Redirection
Directory Browsing

Application Development:
ASP.Net
ISAPI Extension
ISAPI Filters

Security:
Windows Authentication

Tools:
IIS Metabase Management
IIS 6 WMI Management

Then make sure you set tcp and named pipes in the sql server surface area configuration tool for the remote connections.

Then install service 2 for sql server and the service pack for the service pack if it isn't up to version 9.0.3054

Then run the sharepoint reporting services msi file that integrates them noting this doesn't run on a windows 208 server unless you install a reg hack to run as admin or turn off the uac.

Then you need to go into the reporting services configuration tool to configure and that is where many get mixed up.
Reporting Services needs to use the same pool as sharepoint to integrate.

Everything finally fell into place except the reporting services configuration settings didn't show in the sharepoint admin application page.
Had to go to the _layouts pages instead.

When deploying your reports, the pc with report designer should also be service packed and then you get an extra option regarding sharepoint in your deployment options wording for your target folders etc.

http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx
http://servername:portno/_layouts/ReportServer/ManageTrustedAccounts.aspx
http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx?Settings=RS


Regards,
Tom Bizannes
Sydney, Australia
Microsoft SQL Server and Microsoft Access Consulting

VBA Copy missing files

Public Sub CopyMissingFiles(sProdFiles As String, sOfflineFiles As String, sFileExt As String)
Dim i As Integer
Dim sDestination As String
Dim iFiles As Integer
'FileExt = "*.pdf"

'Add \ at end if missing
If Not Right(sProdFiles, 1) = "\" Then sProdFiles = sProdFiles & "\"
If Not Right(sOfflineFiles, 1) = "\" Then sOfflineFiles = sOfflineFiles & "\"

iFiles = 0

With Application.FileSearch
.NewSearch
.LookIn = sProdFiles
.FileName = sFileExt
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
sDestination = Replace(.FoundFiles(i), sProdFiles, sOfflineFiles)
If Not FileOrDirExists(sDestination) Then
'Debug.Print .FoundFiles(i)
FileCopy .FoundFiles(i), sDestination
iFiles = iFiles + 1
End If
Next i
End With

If iFiles > 0 Then
MsgBox "Copied " & iFiles & " pdf files to " & sOfflineFiles, vbInformation
End If

End Sub

Sub test_CopyMissingFiles()
Call CopyMissingFiles("D:\ProdTest\Files\", "D:\files\", "*.pdf")
End Sub

VBA Ado Example for Sql Server 2000/2005 etc...

Just use {SQL Server} for most...
Note: Backward compatibility isn't set automatically on sql server 2005/2008!
So one can use Sql Server Native Client - note - you need to download and install this small component from codeplex.....on each client pc that will need it.
Or you use normal oledb connections which work fine, once named pipes is enabled on sql 2005/2008!

Public Function AdoExample() As Long
Dim AdoRs As ADODB.Recordset
Dim AdoCn As ADODB.Connection
Dim sCon As String
Dim sSql As String
Dim sServerName As String
Dim sDatabaseName As String

On Error GoTo err_AdoExample

sServerName = "Database Server"
sDatabaseName = "Database"

'sCon = "Driver=SQLNCLI.1;DataTypeCompatibility=80;Server=" & sServerName & ";database=" & sDatabaseName & ";trusted_connection=yes;"
sCon = "driver={SQL Server};Server=" & sServerName & ";Database=" & sDatabaseName & ";trusted_connection=Yes;"
'sCon = "driver={SQL Native Client};Server=" & sServerName & ";Database=" & sDatabaseName & ";trusted_connection=Yes;"
Set AdoCn = New ADODB.Connection
AdoCn.ConnectionString = sCon
AdoCn.Open
Set AdoRs = New ADODB.Recordset
sSql = "Select @Version as Version"
AdoRs.Open sSql, AdoCn ', adOpenDynamic, adLockOptimistic

debug.print AdoRs!Version

AdoRs.Close
AdoCn.Close

Exit Function

err_AdoExample:

MsgBox err.Description & vbNewLine & err.Number, vbInformation
Resume Next

End Function

Regards,
Tom Bizannes
Sydney, Australia
Database Development and Auditing

Re: Index / Match versus Vlookup macro

Wow..

Vlookup is way too dodgy!

Index match returns the values looking up itmes in a column, but vlookup which should have had exact matches had issues with - in the names and came with with incorrect values....

=INDEX('Sheet2!C:E,MATCH(AG974,'Sheet2!C:C,0),3)

This checks for a match to cell AG974 in the other worksheet - 'Sheet2' and gets the value in column E...(The 3rd column in the columns selected)

Regards,
Tom Bizannes
Excel Macros and Development Sydney

Office Developer Conference 2008

This was really good.

Full kudos to Graham Seach for Organising this.

Couldn't go to all the sessions...but learnt a few new tricks.....

I presented Real World Access and Real World Excel...Two rapid fire ones about migrations to Office 2007 where we helped clients to upgrade microsoft office and discovered a few gotchas..

Wow these really stretch you...It's easier to talk for an hour than for 10 minutes!

Got back and now designing an Access 2007 picture gallery/viewer for your pc...
Access can read and show pictures in a directory without having to load them into the database.

Regards,
Tom

Re: Michael Noel and SharePoint Virtualisation

This was really good…

Michael Noel did a really good talk about virtualising SharePoint using either HyperV or Vmware. ( and he also mentioned no 3 in virtualisation - Cirtix Xen Server)

Still cannot believe Michael flew in from the states today and is flying out again today….just to talk to me and Peter regarding virtualising SharePoint…
Well actually there were a few others there….

He’s written a few SharePoint and isa server books and whitepapers for Microsoft etc…He knew his stuff.
One of his clients has 250,000 SharePoint Users!

Don’t virtualise Sql Server was one very important point…This is due to the high I/O of Sql Server
Next to go physical would be Index Server….
Other Virtuals only lose 2% to 3% of CPU performance so web servers do make a good case for Virtualisation.

Watch the NUMA boundaries…giving to much Ram for a virtual doesn’t equate to better performance if the NUMA boundary is hit..--> Too much paging etc...
Good rule of thumb … Ram per vm = Ram/CPUs…..
e.g two quad cores --> 8 cpus --> 64G of Ram --> 64/6 = 8G per virtual.

Hyper V guys need to get Ms Virtual Machine Manger 2008 – came out in Oct 2008…

Check out:
http://tinyurl.com/virtualsp
http://tinyurl.com/edgenoel2
http://tinyurl.com/edgenoel3

Virtual Machine Manager 2008 is out now….
System Centre Data Protection Manager 2007 great for shadow copies to restore sharepoint servers faster..

VMware has a cool tool P2V (free now!!) for copying physica to Virtual Machines…
Will need to email some people who asked me about this this year….

Big sites get vmware ESX or Citrix xen server 5 for vmotion or xen motion for great fail over if one server goes down.

Synergy and AvePoint have some SharePoint Replicators? … Will need to check these out…

Peter came along because apart from hyper v he is now playing with vmware---
It was good that Michael confirmed what I had hinted to the guys about -- don't virtualise SQL Server..That being said, up to 100 users should push sq server too far? Maybe not unless they are really using SharePoint by using an addon like Wisdom DMF where they can easily open and save their documents....Then take no chances...

Re: Sydney Sql Server User Group Tuesday 11th November 2008

BI was the topic.

This was slightly different than a canned demo as open conversation and comments were the go..

The main things one got out of this would be that the more you know MDX, the better you can design reports, build cubes and anlyse the data....

There was a hint that one can do MDX in Excel 2007....Will try this out.....That would enable you to do more than just slice and dice.....

e.g. For power analytics...

Office Dev in two weeks time!

The Office Development Conference is only two weeks away!

If you are thinking of going, you need to let them know via email so there will be enough Pizza!
Oh my gosh!...Will need to get my Cholesterol checked before this weekend!

Same as last year, cannot go to all the sessions I want to go to!

FYI – I am doing two quick rapid fire sessions, as I will be the only Excel Expert speaking apart from Grant at Angry Koala talking about data mining....
plus I couldn’t help myself and just had to do something on Access rather than just sit and correct people all the time!

Will have to hand around a few WISDOM cubes……

Regards,
Tom Bizannes
Sydney, Australia
http://www.macroview.com.au