Phoenix: SQL Server 2005 Best Practices

 

The following are some of the best practices adopted by SQL Server DBA's. I have covered some important stuffs starting with Tempdb, Memory, DB engine, Replication and Index.

Phoenix: SQL Server 2005 Best Practices

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway

Learn something new every day.  Until now, I was using the sql client alias configuration tool to set this one up.

No more dependencies on server admins!

Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

There's more info in MS KB Article 313295:

TCP/IP:

server=tcp:hostname

You can optionally specify a specific port number. By default, the port is 1433.

server=tcp:hostname, portNumber

Named Pipes:

server=np:hostname

You can optionally specify a specific named pipe.

server=np:\\hostname\pipe\pipeName

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway

Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral

 

At times it can be necessary to move the data and or log files from one location to another on the same SQL Server. There are two ways to go about doing this task, detaching the database from the SQL Server Instance, moving the files to the new location in the operating system, and then reattaching the database to the SQL Server Instance, and using ALTER DATABASE with the MODIFY FILE option to move the files through a metadata switch, taking the database offline, moving the file in the operating system and then bringing the database back online. Both accomplish the same task, but there are a number of reasons why the ALTER DATABASE method can make more sense for doing this kind of task.

Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral

Windows 7 and Windows Application Compatibility : Boot from Windows 7 VHD Boot without having any native Operating System

 

Boot from Windows 7 VHD Boot without having any native Operating System

VHD boot is a new feature of Windows 7 and can be used in very creative ways. If you have a need for Reimaging a machine (like a testing environment) back to its original configuration then booting from VHD seem to be a very easy way to do it.

Windows 7 and Windows Application Compatibility : Boot from Windows 7 VHD Boot without having any native Operating System

Microsoft SQL Server Development Customer Advisory Team – Mirroring SQL

 

To get more information on SQL Server database mirroring, check these links (Some of these were written for SQL 2005 but still apply to SQL 2008):

· Database Mirroring and Log Shipping Working Together: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Implementing Application Failover with Database Mirroring: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Database Mirroring Best Practices and Performance Considerations: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Database Mirroring FAQ: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

· Performance Boost for Database Mirroring: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx

· Troubleshooting Database Mirroring Deployment: http://msdn.microsoft.com/en-us/library/ms189127.aspx

· How to Avoid Orphaned Users With Database Mirroring:

http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!494.entry

Microsoft SQL Server Development Customer Advisory Team

Halving your delete times with large datasets

Here is info from MySpace and the SQL Performance Team on how to delete large ordered blocks of data.

create view v1 as (select top (10000) * from t1 order by a)

and we can delete the “top” rows using simply

delete from v1

The query plan for this delete is much simpler.

and the I/O and cpu statistics demonstrate the improvement:

Microsoft SQL Server Development Customer Advisory Team

Digital Volcano – Find and Replace multiple files

TextCrawler and Duplicate Cleaner are two useful tools for a DBA working with Reporting Services.

In combination with Reporting Services Scripter, TextCrawler allows you to perform comparisons of reports between servers (by changing the server name in the RDL) and to change the root path for folders in the rss scripts (provided the name is distinct).

Digital Volcano

BI-tch – Dropping the time in datetime

Sometimes door #1 isn’t the best option.

Option 3: DATEADD

DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)

Returns 2009-04-09 00:00:00.000 (shock horror!)

BI-tch

Andrew Fryer's Blog

 

To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:

• DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
• DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
• Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
• Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.
• Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
• Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
• Integration Services Samples – A few sample packages to start auditing and viewing lineage on.

Andrew Fryer's Blog

RamDisk for Performance and Security

This technology was around since the dawn of XT and could be valuable for improving SQL performance.

RamDisk and RamDisk Plus®
(a.k.a Ramdrive)

RamDisk and RamDisk Plus offer dramatic improvement in the storage performance of servers and applications running on Windows Server 2003, Windows XP, and Windows 2000. Intel’s IoMeter benchmark reveals that our ramdrives typically show a 50 times performance gain over a physical hard disk. In many instances, that translates into an improvement of 3 to 10 times in overall application speed.

RamDisk for Performance and Security

Pragmatic Works - BI xPress Screenshots

Good-looking SSIS Auditing, configuration, monitoring and change management tool.

Pragmatic Works - BI xPress Screenshots

THE BI Blog : The Bird is the Word

Straight from the BI Blog comes social BI networks.

Here is a useful list of some of the communities and social groups for Microsoft BI, out there to get involved.

Follow the bird, the bbb-bird bird bird, bird is the word…

Twitter feeds

Microsoft BI

Nic Smith

Donald Farmer

Guy Weismantel

Microsoft SharePoint

SQL Server Pro’s

Microsoft_Excel

Facebook groups

Microsoft Business Intelligence

Microsoft SharePoint

Microsoft SQL Server

Excel

Linkedin groups

Microsoft Business Intelligence

SharePoint Users Group
SQLServer Central

Excel

Microsoft YouTube Channel

http://www.youtube.com/user/wowmsft

Microsoft BI blogs

The Microsoft BI Blog

http://blogs.msdn.com/bi

THE BI Blogroll

Intelligent Insight on Performance Management
PerformancePoint Team Blog
Norm's PerformancePoint Server Blog
Excel Team Technical Blog
SQL Server Reporting Services Team Blog
RDA Business Intelligence Blog
Sacha Tomey's BI Blog
LATAM Business Productivity Blog
SharePoint Team Blog
SQL Data Services Team Blog
The I in BI Hayley Rixon’s Blog

Microsoft BI Websites

Microsoft BI

Microsoft People Ready Business

SharePoint

SQL Server

Excel

Partners

http://www.microsoft.com/bi/partners/default.aspx

https://partner.microsoft.com/bi

TechNet Discussion Forums:

SQL Server Analysis Services

SQL Server Data Access

SQL Server Reporting Services

Management Reporter

ProClarity - General

SharePoint - Excel Services

Data Mining

Monitoring and Analytics

SharePoint - Social Computing

SQL Server Data Warehousing

SharePoint - Collaboration

SharePoint - Business Intelligence

Office Online Discussion Forums

Excel

THE BI Blog : The Bird is the Word

Amit's blog : Disk Partitioning Offset

 

Volume alignment, commonly referred to as sector alignment, should be performed on the file system (NTFS) whenever a volume is created on a RAID device. Failure to do so can lead to significant performance degradation; these are most commonly the result of partition misalignment with stripe unit boundaries. This can also lead to hardware cache misalignment, resulting in inefficient utilization of the array cache. For more information on this, see Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000.

Amit's blog : Disk Partitioning Offset

SQL Steve’s SQL Tips - While replacement

Instead of counters and while loops, this is a simple replacement.

But what if you have billions of rows in the “ErrorLog” table and you need to delete them a batch at a time so that you don’t lock up the entire table? You can use the “TOP” operator to specify the batch size and then specify an integer value that follows the “GO” keyword specifying the number of batches to execute.

For example:

DELETE
TOP(100000)

FROM

    ErrorLog

WHERE

    ErrorTime < ‘2008-01-01′

GO 10000

SQL Steve’s SQL Tips

Two Time-saving VS Command Line Parameters

Who needs Windows...

“…\devenv.exe” “path to solution file”

You can copy an paste your exising shortcut file to a new shortcut file and then modify the Target field to have the additional solution parameter.

To use both of these together, put the solution file first and the /nosplash second.

To see other command line options, run devenv.exe with the /? parameter.

Two Time-saving VS Command Line Parameters

Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3

 

Complete OLAP infrastructure without Microsoft Analysis Services, part 3

In previous part of this tutorial we've built an analytical database using Microsoft Analysis Services. We've been able to browse the database using SQL Management Studio and execute MDX queries on it.

In this part of the tutorial we will:

• build an offline static cube (*.cub file) from the relational database using C# and ADOMD.NET
• query the static cube using Microsoft Excell as static cube browser
• query the static cube with MDX queries using C# and ADOMD.NET

Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3

Internals Viewer for SQL Server - Home

 

Project Description
Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.
All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works
Note There was a problem with the first release of Beta 2 20081228. If no add-in is visible please follow the instructions in Troubleshooting.
User Guide
Troubleshooting
Features

• Integration with SSMS (SQL Server Management Studio) 2005 and 2008

• The application is installed as a SSMS add-in
• Internals information integrated into the Object Explorer
• Transaction Log viewer integrated into the Query Results

• Allocation Map

• Displays the physical layout of tables and indexes
• Displays PFS status
• Overlay pages in the Buffer Pool

• Page Viewer

• Displays Data pages including forwarding records and sparse columns
• Displays Index pages
• Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
• Displays pages with SQL Server 2008 row and page compression

Internals Viewer for SQL Server - Home

Quick and dirty clone table schema

 

SELECT top 0 * into customer_backup
  FROM customers

Be sure to create your primary keys and indexes afterwards!

DM Companion – Data mining in cloud city

If you would like a better understanding of the Data Mining Experience from the web, DM Companion looks like a good learning tool.

Of course, there’s always this tool too:
http://sqlserverdatamining.com/cloud

DM Companion

DataPort Web | Sql bulk insert and paramaters

 

Sometimes transferring data quickly and safely can be a major task. In the following example I’ll show you how you can do a parameterized insert and how to use the sql transaction. This will speed up your insert tremendously.

DataPort Web | Sql bulk insert and paramaters

Data Dude - VSTS Database GDR VPC

 

You can download the VPC from the following location:

• VSTS “all-up” Virtual PC/Virtual Server image (6 GB download, expands to 15 GB)
• VSTS “all-up” Hyper-V image (6 GB download, expands to 15 GB)

Data Dude

Metadata Toolkit - Windows Live

The BI Metadata Toolkit

Overview

This white paper covers several interesting and unique methods for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis.

http://www.microsoft.com/downloads/details.aspx?FamilyID=182bd330-0189-450c-a2fe-df5c132d9da9&displaylang=en

Metadata Toolkit - Windows Live

Download details: SQL Server 2005 SP3

 

Microsoft SQL Server 2005 Service Pack 3

Brief Description

Download Service Pack 3 for Microsoft SQL Server 2005.

Download details: SQL Server 2005 SP3

Andrew Fryer's Blog – creating a “time of day” dimension

Highlighting some of the pluses of SQL 2008

Following on from my previous post, in some data warehouses there is a separate dimension for time of day, so that demand through a day can be modelled. Storing time in SQL server 2005 was a bit of a cludge typically involving picking an arbitrary date (like 1/1/1900) and then tacking the time on to the end of that.  Now there’s a separate time data type so it’s easy to store the right data and create the time dimension using a script like this:

declare @time time = '00:00'
declare @timekey int = 0
declare @timegrain int =15

if not exists
    (select  * from sys.tables where name = 'dimTimeofday')
create table dimTimeofday( timekey int, TimeofDay time)
while @timekey < 1440 begin   
    insert into dimTimeofday(timekey,Timeofday) values (@timekey, @time)
    set @time = dateadd(minute,@timegrain,@time)
    set @timekey += @timegrain
end

For more on the new time data type check books on line here.

Andrew Fryer's Blog

SQL Server Matrix Workbench

Excel inside SQL Server?  It’s possible…

/*In this workbench, Robyn Page and Phil Factor decide to tackle the subject of Matrix handling and Matrix Mathematics in SQL. They maintain that 'One just needs a clear head and think in terms of set-based operations' */

SQL Server Matrix Workbench

Download details: SQL Server 2008 Upgrade Technical Reference Guide

 

Thank You for Downloading

SQL Server 2008 Upgrade Technical Reference Guide

Download details: SQL Server 2008 Upgrade Technical Reference Guide

Killing sessions automatically with SSIS

Poor man's query governor for MDX here.

...or, what to do while you're waiting for Analysis Services to get a proper resource governor (fingers crossed for the next version).

Chris Webb's BI Blog - Windows Live

Report Viewer Redistributable 2008 from 9/9/2008 still does not read 2008 reports??? - TechNet Forums

SQL 2008 launched a few months ago... and left Reporting Services developers in the dust.

Good things will come to those that wait.

The current target date for a control that can read the 2008 RDL schema is currently the first calendar quarter of 2009.  These dates can and are subject to change.

Report Viewer Redistributable 2008 from 9/9/2008 still does not read 2008 reports??? - TechNet Forums

"So, a booth babe and a geek walk in to a bar..." and they get certified.

Notes from a former certification blogger.

I really recommend that you subscribe to Born to Learn.

Ask Ken why it's called Born to Learn, while you're over there.

The first stop for MCP, certification, or exam help continues to be your regional helpdesk: http://www.microsoft.com/learning/support/worldsites.mspx, as you know. The MCP newsletter is still the best, official way to get news about the program and exams: Subscription info. And don't forget the other great bloggers in Microsoft learning--if you get stuck somewhere, check in with one of these people or teams:

• Gerry: Developer Certification
• Mike: E-Learning
• Beta exams
• The Microsoft Learning web site
• Jeff: Developer Courseware
• Juliana: Microsoft Press
• ILT – Courseware Team: Instructor-Led Training
• Bill: MCT and Trainer Readiness

While I'm at it, I thought I'd answer some of your other, recent questions, too, in a little Q&A.

"So, a booth babe and a geek walk in to a bar..."

Seven Steps to Certification Success : Training and Certification : Learning : Microsoft Forums

Where to start when dealing with certifications

Before writing this article, I posed a question to many certified individuals. The question I asked was, “What was the hardest part of your certification journey?” You would expect to hear that the hardest part was the exam(s) themselves. However, many people responded that the most difficult challenge was that they simply did not know how or where to start. They would describe how they heard of a certification, bought a book and started studying. There was no research performed, no plan established, and no evaluation afterword to debrief and learn from the experience.

Seven Steps to Certification Success : Training and Certification : Learning : Microsoft Forums

JAM Software - SpaceObServer - The Hard Disk Space Manager with Database Storage

 

SpaceObServer - The Complete Disk Usage Management Solution

V3.3.2

SpaceObServer is a powerful and flexible hard disk space manager for Windows. It scans local and network drives using a background service and stores their structure, sizes and properties in an SQL database. In an Explorer-like user interface the collected data can be viewed and browsed in hierarchical or tabular views, 3D bar, pie, line charts and tree maps. Using the archived data you are able to track the development of the space usage from past to present, and forecast future size usage. A flexible file search, with predefined searches for very big, old or obsolete files allows filtering and listing files directly from the database. A duplicate file search is also included.

JAM Software - SpaceObServer - The Hard Disk Space Manager with Database Storage

I Shot the Serif - Free 3 of 9

 

Can you believe there are people who would charge you for a barcode font like this?

I Shot the Serif - Free 3 of 9

TFS & Virtual Machines

 

What to do if you need to roll back \ undo your virtualized client

Use the Force -  To get the server and workspace versions in sync again perform a get using the force option. 

OR

If you are using Hyper-V a better solution is to put your workspace on a drive that is offline to the host OS (Windows 2008) but known to the Hyper-V machine.  You have to disassociate this offline drive from the Hyper-V machine before you do the rollback and re-attach afterwards.  This keeps the rollback from affecting the workspace drive, leaving your workspace files the same. 

Ed Hintz (MSFT)

Download SQL-RD Subscription Free Trial - Schedule SQL Server Reporting Services reports in daily, weekly, monthly etc. A single report in an e-mail, or a batch of...

 

SQL-RD saves time and money by allowing you to schedule and manage MS SQL Reporting Services Reports on numerous servers from a single application. It exports your RS reports to multiple printers, FTP, Secure FTP, email, folders, FAX and SMS. Choose to output to doc, xls, rtf, wk*, dbf, htm, mhtm, pdf and more. Use standard frequencies like daily, weekly, mothly, or set up your own custom calendars and exception calendars. Dynamic schedules provide an unmatched feature for linking and automating reports, data and destinations. It integrates seamlessly with Outlook and Exchange Server. Use Event-Based schedules (triggers) to fully automate your business processes whether they are report-related or not. SQL-RD features a feature-rich intuitive user interface, Folder Housekeeping, Clustering, PDF, PGP, Excel and Zip security, and an NT (Windows) service scheduler. It is fully compatible with SSRS 2000 and SSRS 2005.

Download SQL-RD Subscription Free Trial - Schedule SQL Server Reporting Services reports in daily, weekly, monthly etc. A single report in an e-mail, or a batch of...

Kimberly L. Tripp | Transaction Log VLFs - too many or too few?

 

To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).

Kimberly L. Tripp | Transaction Log VLFs - too many or too few?

Useful Query #1 - find overlapping SQL Agent jobs

 

In SQL 2008 (or using 3rd party tools) you can run this command against multiple servers to determine which SQL Agent jobs overlap.  Perfect for finding contentious jobs...

use msdb
go

with cte  (name, server, run_date,run_time,run_duration,enddate,startdatetime,enddatetime,retries_attempted) as (
select --j.name, h.step_id, h.step_name, h.sql_message_id, h.sql_severity, h.message, run_status,
j.name,server, run_date, run_time, run_duration, run_time + run_duration enddate,

dateadd(hh, run_time / 10000,
            dateadd(mi, (run_time % 10000)/100,
            dateadd(ss, run_time %100,
            cast(cast(run_date as char(8)) as datetime)))) startdatetime,
            dateadd(ss,run_duration,dateadd(hh, run_time / 10000,
            dateadd(mi, (run_time % 10000)/100,
            dateadd(ss, run_time %100,
            cast(cast(run_date as char(8)) as datetime))))) enddatetime,

retries_attempted
from sysjobs j
inner join sysjobhistory h on h.job_id = j.job_id and h.step_id = 1
where run_date > convert(varchar(10),dateadd(d,-1,getdate()),112)
--and run_status <> 1
)
select cte.server, cte.name, cte.startdatetime, cte1.enddatetime, cte.run_duration, cte1.server, cte1.name, cte1.startdatetime, cte1.enddatetime, cte1.run_duration
from cte
cross join cte cte1
where (cte.name <> cte1.name)

and (cte.startdatetime between cte1.startdatetime and cte1.enddatetime
    or cte.enddatetime between cte1.startdatetime and cte1.enddatetime)
    and cte.run_duration > 300

Carpe Datum - Activity Monitor

Other than hiding Activity Monitor from us in SQL 2008 (it's on the toolbar now) it has some great new features that make the role of a DBA almost obsolete.

Well, not quite, but it does make things much easier.

Install SQL 2008 and you can even run things like Policy Based Management and Activity Monitor (Super 2008 Ed'n) against SQL 2005 & 2000 instances.

My favourite trick is to show query plan on an active query, show missing indexes, then implement.  Instant performance improvements... and here's another way to track performance (or just snoop).

The new Activity Monitor has another trick up its sleeve: If you open it and then expand the first band of information just below the four graphs, you'll see a list of processes that you can order, sort and filter. If you right-click any process, you'll see an option to "open in Profiler". Click that, and you'll open Profiler with a default trace right on that SPID. Very useful to quickly identify the actions of a connection.

Carpe Datum

Legalizing the crack that is Excel spreadmarts – Chris Webb on Project Gemini

There’s nothing wrong with Excel.  Actually, there’s tons of stuff wrong with Excel.  Here is one example of a Spreadmart gone bad.

Excel error leaves Barclays with more Lehman assets than it bargained for

The law firm representing Barclays filed the motion (download PDF) on Friday in U.S. Bankruptcy Court for the Southern District of New York, seeking to exclude 179 Lehman contracts that it said were mistakenly included in the asset purchase agreement. The firm — Cleary Gottlieb Steen & Hamilton LLP — said in the motion that one of its first-year law associates had unknowingly added the contracts when reformatting a spreadsheet in Excel.

Cut-Paste Wealth Destruction! :)

Actually, many of the problems with Excel aren’t really with the product, it’s how it’s used.  “When the only tool you have is a hammer, everything looks like a nail.”  Ditto when the only tools you are comfortable with for dealing with numbers are Excel and Calc.exe, and it takes 2 weeks (or more) for the same report to be built using a “reporting tool” by the IT team…

Chris has some great points that I wholeheartedly agree with.  In the past, I have been responsible for promoting the view of a “single source of total knowledge” or a “one view” of the organization.  Excel doesn’t fit into this picture as a storage mechanism, but it can be the UI, analysis, modeling, and calculation engine that is supported by a central repository stored in the SQL Server cloud (or wherever you may decide to store your data).  Not knowing the details on Project Gemini, I hope that it continues with this theme of server-based storage and client-based analysis, and doesn’t go the way of Coleco Gemini.  I hope that there are options for clients who don’t adopt the latest technologies.  

Spreadsheets have long been one of the most popular ways for corporate users to store and analyze data. But over the past few years, they have played an increasing role in data breaches because workers are apt to store them unsecured on laptops. In addition, hackers have actively tried to exploit vulnerabilities in Excel.

Since I’m a DBA at heart, I’m not comfortable seeing thousands of silos of varying degrees of accurate information multiplying and dividing around a company. With a DBA mindset, it is all about control, security, maintainability, and performance of your data. 

This kind of desktop, DIY BI is in a way similar to illegal drugs: there are always some people that want it, a certain number of them are always going to do it even though they know they shouldn't, so you've got two choices - either legalise it and then hope to control it, as with Gemini, or throw all your efforts into outlawing it.

Chris Webb's BI Blog: Last thoughts on Gemini for the moment

Project Gemini – Microsoft’s Brilliant Trojan Horse

The concept of transferring some of the calculations and aggregations to the client does make sense.  My laptop is more powerful than many of the 5 year old servers in use at some of the client sites I work in.  It would be great to be able to quickly build models without delving into Business Intelligence Studio, SSIS, SSRS & SMS, or asking a developer.  There’s not much faster than memory on a PC, so in-memory processing sounds great to me.  I just hope there’s a way to push out a lockdown mechanism, for when that laptop and its information disappears from the company.

I hope they include a connector to perform calcs inside the GPU too. 

I like what I see so far with some of the “value-adds” coming out of MS Downloads for Excel like the data mining tools, though in the wrong hands (or even worse, the right hands) the information could be very misleading and lead to disastrous results.  From a marketing and adoption perspective, does it make sense to sell the idea of distributing mass amounts of data down to a client PC?  I’m still waiting on a good forms-based interface to input data from Excel directly into a data repository.  Sort of an InfoPath merged with Excel, without the need to install InfoPath, and with the “always-on” save features from One Note.  Sure, web forms and web services.  What about just Excel to Sql?

Without proper governance and understanding of the technology, publishing to Sharepoint can still lead us to Enterprise Spreadmart solutions and IT maintenance nightmares. 

In my opinion, rather than sheets of 20 million rows of raw data crunched and stored in a spreadsheet on a laptop, file share, or document store, there should be sheets (or something else?) of results available with the data being stored, crunched, and transformed in a central, secure, redundant place (“THE CLOUD?”). I hope that this is the approach Project Gemini will provide.  Magic?

One truth, shared by many, common to one. 

Metadata, semantic web technologies and, yes, Gemini again

Gemini IS Analysis Services

Gemini is Inevitability

SPSFAQ – Scalability and the fact that Sharepoint isn’t a database.

Some people seem to think that Microsoft Business Intelligence is Sharepoint.  Sharepoint is one piece to the puzzle, and it can scale, but there are some inherent limits that need to be avoided.  Eli has a few.

The 2,000 rule: because stored procedure calls to SQL Server slow down as you reach 2,000 items, have less than that in a view on a List. Less than 200 ideally to have optimum performance.

SPSFAQ

Speaking of scalability & Microsoft applications… Windows 3.1 is dead.  Long live Windows 3.11.

Microsoft has officially retired Windows 3.1

news.bbc.co.uk — "An application has expectedly quit. Windows 3.x has come to the closing moments of its long life. On 1 November Microsoft stopped issuing licences for the software that made its debut in May 1990 in the US. The various versions of Windows 3.x (including 3.11) released in the early 1990s, were the first of Microsoft's graphical user interfaces .."

http://digg.com/microsoft/Microsoft_has_officially_retired_Windows_3_1

PSS SQL Server Engineers : SQL Server Support in a Hardware Virtualization Environment

 

SQL Server Support in a Hardware Virtualization Environment

There is no doubt that virtualization is a hot and popular topic (the number of questions over email I get daily are a testament to that). Therefore, I think it is important for our customers to understand the support policies from Microsoft regarding SQL Server running in a hardware virtualization environment.

We have just published the following KB article that outlines this policy:

http://support.microsoft.com/?id=956893

PSS SQL Server Engineers : SQL Server Support in a Hardware Virtualization Environment

BeI - Microsoft Business Intelligence

 

Day after day we are working with Cube Browser but when the project or cube designer has closed we lost our query, sometime it takes a long time to reorder again all our dimensions.

This add-in is giving us the ability to save personal views and run it as a new request.

I develop it for SSAS 2005 & 2008 (SSAS 2008 Pre CTP6 with VS 2008).

Download Add-in For SSAS 2005

Download Add-in For SSAS 2008

BeI - Microsoft Business Intelligence

BeI - Microsoft Business Intelligence – Using WMI & Reporting Services

 

Reporting Services Querying with WQL

Hi Everyone,

From time to time we need to "ask" our Operation system questions such as:

1.       How are you today?

2.       How was your night?

3.       Do you need anything?

4.       Did somebody do something to you?  Who?

Well, the best way to do it is querying the system with WMI queries by WQL language.

Few days ago my customer needed to decide what the best way is.

So, how we can do it? , the following solutions are some examples of ways to do it

BeI - Microsoft Business Intelligence

SSIS Junkie : T-SQL: Generate a list of dates

 

The following bit of code uses a common table expression (CTE) to generate a contiguous list of dates in SQL Server.

with mycte as

(

select cast('20080101' as datetime) DateValue

union all

select DateValue + 1

from mycte

where DateValue + 1 < '20080731'

)

select convert(char(8),DateValue,112) DateValue

from mycte

OPTION (MAXRECURSION 0)

SSIS Junkie : T-SQL: Generate a list of dates

SQL Server Forums - Deleting Large Number of Rows

SQL 2008 breaking “feature”

---

I was looking at the ROWCOUNT documentation and BOL states:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.
Something to keep in mind if your scripts will be run against sql 2008

SQL Server Forums - Deleting Large Number of Rows

How to open a website in Visual Studio 2005 from the command line - BradleyB's WebLog

 

How to open a website in Visual Studio 2005 from the command line

I’ve seen several queries asking how to launch Visual Studio 2005 opening a specific folder as a website from the command line. Unfortunately Visual Studio 2005 does not support this by default but you can enable the scenario by writing a macro.

To do this, create a new macro either in an existing module or in a new module. For this example I’m going to add a module call Website to the MyMacros project.

If you’re new to macros in Visual Studio 2005, select menu Tools/Macros/Macros IDE, then select the MyMacros project and add a module.

Before you can use the new Website extensibility objects within VS you’ll need to add a reference to VsWebSite.Interop.dll.

Once added you can add the following code:

Public Module Website

Sub OpenWebsite(Optional ByVal path As String = "")

If (String.Compare(path, String.Empty) = 0) Then

MsgBox("Must supply a folder path to the OpenWebsite macro", MsgBoxStyle.OkOnly)

Else

Dim webPkg As VsWebSite.VSWebPackage

webPkg = DTE.GetObject("WebPackage")

webPkg.OpenWebSite(path, VsWebSite.OpenWebsiteOptions.OpenWebsiteOption_None, False)

End If

End Sub

End Module

After this code is added you’ll be able to run the macro. You can test it out in the command window.

From the Command Window in VS:

>Macros.MyMacros.Website.OpenWebsite C:\MyProjects\MyCompany\CompanySite

From the Command Line:

devenv /command "Macros.MyMacros.Website.OpenWebsite C:\MyProjects\MyCompany\CompanySite"

After its working you can register a shell command enabling an “Open as Visual Studio Website” command on any folder in windows explorer.

To do this copy the following into a OpenWebsite.reg file and run it.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Folder\shell\OpenVSWeb]

@="Open as Visual Studio Website"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Folder\shell\OpenVSWeb\command]

@="devenv.exe /command \\\"Macros.MyMacros.Website.OpenWebsite %1\\\""

Now you should be able to right click on a folder in Windows Explorer and select “Open as Website”.

How to open a website in Visual Studio 2005 from the command line - BradleyB's WebLog

Querying and Reporting on Report Execution Log Data

SELECT 'http://localhost/reportserver?'+cast(c.path as nvarchar(max))+'&'+cast([Parameters] as nvarchar(max))
FROM [ReportServer].[dbo].[ExecutionLog] e
INNER JOIN catalog c on c.itemid = e.reportid
WHERE ….

This lets you query the execution log and generate a list of URLs to run reports in Reporting Services.

Querying and Reporting on Report Execution Log Data

Andrew Clancy's Blog : NHibernate Query Generator in Action

 

NHibernate querying is great – it gives us the ability to query objects directly without the need to know anything about the underlying relational schema. It has a couple of major flaws though: 1. property names are accessed via strings, so we don’t know until runtime whether they exist, or are mapped 2. it’s ugly and cumbersome – frankly probably one of the major barriers I’ve had to ORM until now.

Ayende’s NHibernate Query Generator fixes both of these things. Mapped properties are accessed via properties as nature intended, and querying is a lot easier to read and write. Not quite as neat as Linq-to-NHibernate will be, but so much better than native NHibernate querying that I wonder why it wasn’t included in the trunk.

Andrew Clancy's Blog : NHibernate Query Generator in Action

MS BI Conference: Monday Keynote - Mike Diehl's WebLog

Waiting on 2010…

Timeframes: the next major release of SQL will be 24-36 months from release of SQL 2008, but in the meantime, there are a number of releases coming: Madison and Gemini will be coming in the first half of 2010, and CTP's will be available sometime early next year. There are some incremental releases of Analysis Services, Integration Services and Reporting Services coming - the next gen of Reporting Services in particular will become available in a Feature Pack "real soon now".

MS BI Conference: Monday Keynote - Mike Diehl's WebLog

The Programmer's Weblog: Inserting Blob into Microsoft Sql Database

 

It took me a while to figure a good way to insert Blob information into a Microsoft Sql database. One of the things you need to do is to insert an empty Blob, and I wasn't finding that on the internet. In Oracle you will use an empty_blob() function. So, I managed to get my code to work and decided to post it here so people can find an easy example. If you need more information, let me know.

The Programmer's Weblog: Inserting Blob into Microsoft Sql Database

Using Grouping Sets in SQL Server 2008 | Servers and Storage | TechRepublic.com

Cube-like functionality in SQL 2008.

Today I took a look at the usefulness of the new GROUPING SETS clause in SQL Server 2008. The results of the GROUPING SETS clause can be achived in other ways, but it takes more programming work to do so. Next time I will take a look at SQL Server 2008 enhancements to the CUBE and ROLLUP; two constructs that can produce similar results to the GROUPING SETS clause, but in a different manner.

Using Grouping Sets in SQL Server 2008 | Servers and Storage | TechRepublic.com