Site Content

Navigation

Weekly Deals at Buy.com!

Syndicate

Syndicate content

Search

SQL Server Expert Blogs

more

SQL Server MS Blogs

more

You are hereFeed aggregator

Feed aggregator

Revisiting the transaction batch size

Linchi Shea - Sat, 07/31/2010 - 20:05

It’s a well known good practice to control the batch size when you perform large data changes. For instance, you may need to purge a large amount of data monthly, and if you delete them all in one shot, you may blow up your transaction log. Therefore,...(read more)

Categories: SQL Server Expert Blogs

Loading data to SQL Azure the fast way

MS SQL CAT Team - Fri, 07/30/2010 - 17:11

Introduction Now that you have your database set up in SQL Azure , the next step is to load your data to this database. Your data could exist in various sources ; valid sources include SQL Server, Oracle, Excel, Access, flat files and others. Your data...(read more)

Categories: SQL Server MS Blogs

Quote of the day - Doing the right thing

Bucky Woody - Fri, 07/30/2010 - 07:39

Today's quote is from Ovid, a Roman poet: "Video meliora proboque deteriora sequor"

 (I see and approve the better course, yet choose the worse.)

Unfortunately all too true for me far too often.

Categories: SQL Server MS Blogs

SharePoint 2010 Products, SQL Server, Storage and Databases (oh my)

SharePoint IT Pro Documentation - Fri, 07/30/2010 - 03:22

The SharePoint 2010 Products are, at core, Web applications built on SQL Server databases. To best administer an environment and keep it performing well, administrators should understand the following:

  • How to choose the best version of SQL Server for their environment
  • How to best configure and monitor SQL Server for SharePoint 2010 Products
  • Which databases they are running, and their characteristics
  • Whether it’s appropriate to use remote BLOB storage to meet their business needs.

We have assembled key database, storage, and SQL Server content in one easy location—the SQL Server and SharePoint Server 2010 Databases resource center.

The resource center provides links to great core resources that you should be familiar with:

The resource center is also a one-stop shop for SQL Server DBAs who manage SharePoint environments, with links to information about integrating SharePoint with Reporting Services, and PowerPivot for SharePoint.

Please let us know if the resource center is helpful, and if you there is any additional content you’d like to see about managing SharePoint databases.

Categories: SharePoint

SQL 2008 FileStream Fails to Enable After Setup on Cluster that uses Veritas Mountpoints

SQL Server SQL CSS - Thu, 07/29/2010 - 10:52

Recently a customer ran into an issue where they had successfully installed SQL Server 2008 SP1 on a 2 node Windows 2008 cluster. When they went to restore a database that was given to them, they found out the database was created with the new SQL 2008 FileStream feature and couldn’t restore the database on their newly installed SQL 2008 SP1 clustered instance. So they went to enable FileStream through the SQL Configuration Manager. After clicking the check box “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for file I/O streaming access”, then attempting to apply their selections they encountered a message in the dialog.

The message appears below the last checkbox (my system doesn’t have the error, but I wanted to show the dialog). The actual message:

"A previous filestream configuration attempt was incomplete. Filestream may be in an inconsistent state until re-configured"

In addition, if you look in the Application Event log you will see an Access Violation was raised:

Faulting application wmiprvse.exe, version 6.0.6002.18005, time stamp 0x49e0274f, faulting module CLUSAPI.dll, version 6.0.6001.18000, time stamp 0x4791acce, exception code 0xc0000005, fault offset 0x000000000001df73, process id 0xca4, application start time 0x01cb28f4bcfeed65.

Cause

This is a bug in the SQL Server 2008 post-setup FileStream Enablement code. We assume the cluster resources are physical disks and not mount points. This causes a NULL value to be returned to the hResult, which we then pass to the Cluster API which causes an AV. We have only seen this problem when using Veritas Storage Foundation (in this case 5.1 SP1 of VSF) mountpoints.

Workaround

1. Uninstall and Reinstall SQL Server 2008 SP1 and enable FileStream as part of the setup. It will succeed. Make sure you backup all system and user databases before uninstalling and reinstalling SQL Server.

2. Change storage so that it doesn’t use Veritas mountpoints

3. This problem will be fixed in CU10 for SQL 2008 SP1 due out in September 2010. This will allow FileStream enablement post-setup when using mountpoints.

Thanks

Eric Burgess

SQL Server Escalation Services.

Categories: SQL Server MS Blogs

Installing SQL Integration Services after SQL Cluster Setup has Completed

SQL Server SQL CSS - Thu, 07/29/2010 - 10:15

Today I ran into an issue where, SQL Server 2008 SP1 was installed on a Windows 2008 cluster and was working just fine, but we wanted to install SQL Server Integration Services (SSIS) to the two nodes of the cluster. Since SSIS is not cluster aware, we thought it was be just a simple process of adding features to an existing instance of SQL Server. Unfortunately, it was not that intuitive.

In setup’s SQL Server Installation Center you naturally select “New SQL Server stand-alone installation or add features to an existing installation”, because you already have a installed instance of SQL Server on the cluster.

In the “Installation Type” step of the setup, if you select the second ratio button to “Add features to an existing instance of SQL Server” is when you run into problems in this situation.

 

If you select the “Integration Services” option only during the “Feature Selection” step, then proceed with the SSIS installation you will encounter the following error if you already have a clustered SQL instance:

---------------------------
Rule "Existing clustered or cluster-prepared instance" failed.

The instance selected for installation is already installed and clustered on computer SQLClustInstName. To continue, select a different instance to cluster.
---------------------------
OK  
---------------------------

Of course, you don’t want to install another cluster to your instance, but here is the key: you do want to “Perform a new installation of SQL Server 2008”, which is the first radio button in the Installation Type step of the setup (screen shot above). This will allow you to select any feature you desire to put on the system in the Feature Selection step:

Here you would only select “Integration Services” (unfortunately my screen shot shows I have SSIS already installed). By just choosing “Integration Services” you can successfully install SSIS on each node of the cluster. You must run this setup on all nodes of the cluster where you want the SSIS service installed.

Note: Don’t forget to alter you <ServerName>.\SQL2008</ServerName> property in the MsDtsSrvr.xml file to correctly point to your SQL virtual server name rather than the default “.\InstanceName” that is currently there.

Have a great day!

Eric Burgess

SQL Server Escalation Services

Categories: SQL Server MS Blogs

Just how permanent does that data need to be?

Bucky Woody - Thu, 07/29/2010 - 06:23

As data professionals, we plan for things. We plan for growth, we have a disaster-recovery plan, we have a plan for consolidation. Those are all good things. But I've seen very few shops that have a good data retention plan. This is a plan that basically answers the question, how permanent does that data need to be? Just like the disaster recovery plan, we have to ask the organization what they want, and just like that DR plan they'll tell you they want it all. In the case of the DR plan, they'll say "we don't want any downtime", and when you ask them how long to keep a set of data they will say "forever". But both of those things come at a cost. Not only is there a storage cost, but as the data grows your window for maintenance, backups and optimization grows.

So take some time today and put that retention question on your roadmap. I know, you have a lot to do, but if you don't at least open the discussion now, you'll have a lot more to do in the future.

Categories: SQL Server MS Blogs

The Microsoft IT Showcase

Bucky Woody - Wed, 07/28/2010 - 07:27

I had dinner in Seattle (three words: bacon-wrapped-steak) with the folks that put on "How Microsoft Does IT" (get it? Does "it", "IT"? OK, I'm easily amused) and I wanted to point out this fantastic resource to you - again. I rave about it all the time, because before I started at Microsoft I worked at shops large and small, and many times I was called on to come up with strategies and architectures for my organizations. And what better place to start than the people who wrote the software, and use it in their business every day. There are so many references there you'll never get through them all.

If you're not familiar with the site, it's a great compilation on how we at Microsoft run our IT infrastructure - Windows, Office, SharePoint, Exchange, SQL Server, Biztalk, the whole thing. If you don't have it bookmarked, definitely go check it out. There's a DVD you can order as well, and we keep the site very up-to-date and current.

The link is here: http://technet.microsoft.com/en-us/library/bb687780.aspx 

Categories: SQL Server MS Blogs

The TechNet Wiki and Updated Security Checklists

Bucky Woody - Tue, 07/27/2010 - 07:18

You're probably familiar with a Wiki - a document set that anyone can edit. Did you know TechNet (Microsoft's source for technical professionals) has one? And did you know there are lots of folks keeping it up to date? Well, Rick Byham, one of my friends over in the SQL Server group has posted a bunch of security checklists - and you know how much I love checklists! You can go to the Wiki here: http://social.technet.microsoft.com/wiki/ and search for "Checklists", but here's what he's posted to get you started:

Database Engine Security Checklist: Encrypting Sensitive Data

Database Engine Security Checklist: Enhancing the Security of Database Engine Connections

Database Engine Security Checklist: Limiting Access to Data

Database Engine Security Checklist: Database Engine Security Configuration

Thanks, Rick!

Categories: SQL Server MS Blogs

Security Checklists on TechNet Wiki

SQL Server Security Team - Mon, 07/26/2010 - 13:34

Rick Byham, our wonderful technical writer, just posted some checklists you may find useful on the TechNet Wiki. You can search the wiki for word checklist or use these links:

Database Engine Security Checklist: Encrypting Sensitive Data
Database Engine Security Checklist: Enhancing the Security of Database Engine Connections
Database Engine Security Checklist: Limiting Access to Data
Database Engine Security Checklist: Database Engine Security Configuration

It's a wiki, so feel free to correct, comment, etc. Hope this becomes a good resource for the collective knowledge out there on these topics. Thanks Rick!

Categories: SQL Server MS Blogs

Exploration and Understanding

Bucky Woody - Mon, 07/26/2010 - 07:49

As most of you know, I tend to spend a little time on social networks like Twitter, FaceBook and LinkedIn. On one of those networks, Ken Simmons sent the following thought: “One hour sessions give you great ideas you can explore; full day sessions give you in depth knowledge you can use.” He was talking about the difference in a one-hour session and an all-day event at the Professional Association of SQL Server (PASS) summit.

 

He brings up a great point. Many sessions at a technical conference have a “level” associated with them – 100 for beginners through 400 for advanced topics. This can be challenging – if you give a 400-level presentation, you have to assume a great deal of knowledge on the part of the audience, and in one hour you can only cover something very specific at that level. For instance, you could cover in one hour the details of how the locking mechanism works, but only in specific circumstances.

 

An all-day (or at least more than a few hours) workshop, on the other hand, allows you to start with the basics and move all the way to some fairly advanced information. When you think about it, it’s where you spend the time, in-class or on your own. The issue is when you forget this, and believe that a speaker can get you from 100 to 400 level in one hour – it just doesn’t work like that.

 

I’ve got an article here on how to attend a technical conference. Might be worth a look.

Categories: SQL Server MS Blogs

Quote of the Day: On Earning it Yourself

Bucky Woody - Fri, 07/23/2010 - 07:25

 Never stand begging for that which you have the power to earn.        Miguel de Cervantes

Categories: SQL Server MS Blogs

Product Review: Schema Compare for Oracle

Simple-Talk on SQL - Fri, 07/23/2010 - 00:00

One of the more important tasks in the process of rolling out incremental developments to a multi-server production system is to double-check that all of the planned modifications, and nothing else, have been deployed. An Oracle expert, Bruce Armstrong, comes across SQL Compare for Oracle, and sees if it helps with this time-consuming task.

Categories: SQL Server Expert Blogs

White paper: Configuring Kerberos authentication for SharePoint 2010 and SQL Server 2008 R2 products

SharePoint IT Pro Documentation - Thu, 07/22/2010 - 13:29

Hello, this is Norm, IT Pro writer for business intelligence in Microsoft SharePoint 2010 Products. Kerberos is often used in business intelligence solutions to establish secure access to external data sources such as Microsoft SQL Server. Last week, a new white paper was published that gives instructions to configure Kerberos authentication in a multi-server environment for several SharePoint Server and SQL Server business intelligence scenarios.

This document helps you understand the concepts of identity in Microsoft SharePoint 2010 Products, describes how Kerberos authentication plays a very important role in authentication and delegation scenarios, and describes the situations where Kerberos authentication should be used or may be required in solution designs.  

The document also shows how to configure Kerberos authentication end-to-end within your environment, including scenarios that use various service applications in Microsoft SharePoint Server. Additional tools and resources are described to help you test and validate Kerberos configuration. The "Step-by-Step Configuration" sections of this document cover the following scenarios for SharePoint Server 2010.

·         Scenario 1: Core Configuration

·         Scenario 2: Kerberos Authentication for SQL OLTP

·         Scenario 3: Identity Delegation for SQL Analysis Services

·         Scenario 4: Identity Delegation for SQL Reporting Services

·         Scenario 5: Identity Delegation for Excel Services

·         Scenario 6: Identity Delegation for Power Pivot for SharePoint

·         Scenario 7: Identity Delegation for Visio Services

·         Scenario 8: Identity Delegation for Performance Point Services

·         Scenario 9: Identity Delegation for Business Connectivity Services

To download the white paper, click here OR paste http://go.microsoft.com/fwlink/?LinkID=196600 into your browser address bar.

Enjoy!

Categories: SharePoint

When you’re asking for help, make sure you explain what you’re trying to accomplish

Bucky Woody - Thu, 07/22/2010 - 06:24

At some time, all of us need technical help with something. Whenever you do, make sure you try and frame the question not necessarily in terms of what you want to know, but what you're trying to do. Spending time on thinking through your questions will help you get better answers, and people will appreciate that you're putting some effort into the process, and they are more willing to get help.

For instance - let's say yo're stuck on a particular SQL Server Replication issue. You're not sure whether moving the Distributor function away from the Publisher server is the right thing to do, given your budget. You could ask:

"Where should the Distrbutor go in a Replication scheme?"

That's too vague, and doesn't help others help you. Instead, this might be a better way to put the question:

"I'm setting up the design for my Replication scheme, and I have a limited budget. What are the best ways to save money in my design?"

This question goes more towards what you really want to know, and in fact, you may find out that the Distributor question isn't what you need to care about at all. Sure, sometimes you just want the phone number to a store, and you really don't need to tell everyone you're hungry, but if the question goes beyond simple facts, it's a good idea to include as much information as possible so folks can help you best.

Categories: SQL Server MS Blogs

Implementing the OUTPUT Clause in SQL Server 2008

Simple-Talk on SQL - Thu, 07/22/2010 - 00:00

In retrospect, it was probably the inclusion of the OUTPUT clause in the MERGE statement that gave SQL Server 2008 its most powerful SQL enhancement.. It isn't the easiest of features to explain, but Bob does it in his usual clear and careful way.

Categories: SQL Server Expert Blogs

SQL Source Control: The Development Story

Simple-Talk on SQL - Thu, 07/22/2010 - 00:00

Often, there is a huge difference between software being easy to use, and easy to develop. When your pilot users tell you "by week three of any development project you've thrown out anything that takes time or just gets in your way", then it is time to be serious about usability, as well as quality. We decided to find out from some of those at the sharp end of the development of Red Gate's SQL Source Control.

Categories: SQL Server Expert Blogs

VALUES() and Long Parameter Lists

Simple-Talk on SQL - Thu, 07/22/2010 - 00:00

To make progress as a relational Database programmer, you have to think in terms of sets, rather than lists, arrays or sequential data. Until that point, you'll feel the need to pass lists, arrays and the like to functions and procedures. Joe suggests some unusual ways around the difficulty and broods on the power of the VALUES constructor.

Categories: SQL Server Expert Blogs

Excel Services and Excel Web Apps common/different features

SharePoint IT Pro Documentation - Wed, 07/21/2010 - 08:20

Excel Services extends the Excel desktop experience to the web and is built upon a server-hosted version of the Excel calculation engine. Advantages include enabling users to interact with Excel workbooks directly in their web browser, without having to have the Excel client installed on a client computer. This increases the overall reach and use-case scenarios of Excel-based data and applications.

 

Microsoft Office Web Apps includes an online companion to Office Excel that enables users to access and edit documents, regardless of their location. The Office Excel web app is also available to users through Windows Live and to business customers with Microsoft Office 2010 volume licensing and document management solutions based on Microsoft SharePoint 2010 Products.

 

The following table provided by Microsoft SharePoint Insights lets you see shared and exclusive features for Excel Services and the Office Excell web app.

 

Capability

Excel Web App part of Office Web Apps

Excel Services in SharePoint 2010

View

x

x

Edit

x

Collaboration

x

Create New

x

Create Excel-Driven Dashboards

x

Display New Visualization

x

x

External Data

x

x

- Anonymous

x

x

- Secure Store

x

- PowerPivot

x

Web Services API

x

REST API

x

JavaScript OM

x

User-Defined Function

x

x

 

See the following TechNet articles for more information:

·        Excel Services overview (SharePoint Server 2010)

·        Deploy Office Web Apps (Installed on SharePoint 2010 Products)

Categories: SharePoint

Why does PREEMPTIVE_OS_GETPROCADDRESS Show a Large Accumulation?

SQL Server SQL CSS - Wed, 07/21/2010 - 06:56

There is a bug in SQL Server 2008 that causes PREEMPTIVE_OS_GETPROCADDRESS to include and accumulate the execution time of the extended stored procedure (XPROC). The following is an example showing the increase in the GetProcAddress wait time.

select * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS' or wait_type = 'MSQL_XP'
exec master..xp_dirtree 'f:\'
select * from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS' or wait_type = 'MSQL_XP'

GetProcAddress is used to load the entrypoint in the DLL (XPROC) and should complete quickly but due to the accumulation bug the wait time is inflated.   To get a better idea (ballpark) of how long GetProcAddress really takes you can using the following query.

declare @WaitTime bigint
select @WaitTime = wait_time_ms from sys.dm_os_wait_stats where wait_type = 'MSQL_XP'
select @WaitTime - wait_time_ms from sys.dm_os_wait_stats where wait_type = 'PREEMPTIVE_OS_GETPROCADDRESS'

Bob Dorr - Principal SQL Server Escalation Engineer

Categories: SQL Server MS Blogs