Microsoft has posted the first cumulative update for SQL 2008 under article 956717.
Once installed, your patched instance will go to version 10.0.1763 from 10.0.1442.
Tags: sql 2008,
service packs
Categories: SQL Server
I'm blowing my budget this year in going to the Microsoft BI conference in Seattle, but for anyone else out there who, a) will miss the Microsoft BI Conference, or b) is lucky enough to afford two trips, then be sure to go to the DevConnections event in Las Vegas this November.
Some of us wear blinders when it comes to all things SQL Server and BI related, however DevConnections is broken out into several categories: SQL Server, SharePoint, ASP.NET, Visual Studio .NET, Microsoft Exchange, Windows and Unified Communications.
The main categories for SQL Server will be:
- SQL Server Administration
- SQL Server Development
- Business Intelligence
- SSWUG
- And one day for Microsoft presenters
The event will run from November 10th to the 13th. Early registration has already passed, but you still will get that free copy of SQL 2008.
Tags: conferences
Categories: SQL Server
Kevin Idzi of Microsoft was kind enough to leave a comment on my Extracting SharePoint Data using SSIS post informing me of Integration Services enhancements posted on CodePlex.
I had been delaying posting as I wanted to have a full sample showing it in use, but between work and buying a new house recently, I have not had the time to put together my virtual machine images for a complete demo.
The community sample has the following components available to download (in separate install packages):
- XML Destination Pipeline component
- Regular Expression Flat File for regular expression based flat file parsing
- Delimited Flat File Parser, for files with rows that are missing column fields
- Package Generation Sample
- Hello World Sample for demonstrating using the UI API
- SharePoint List Source and Destination – this component was written by Kevin
- WMI Source Component
All of these components are available for download here.
The base requirements for installing these components are .NET 3.5 and SQL 2008 Integration Services.
Once you run the MSI, you will be able to select the two new adapters from the "Choose Toolbox Items" dialogue:
One of the nice features that Kevin included in this adapter is the ability to execute CAML queries against the SharePoint List:
If you do not know how to write CAML or are not good at it like I am, then use the U2U CAML Query Builder written by Patrick Tisseghem; I've got a quick tutorial here: CAML Query Builder.
If you have any feedback about using this SharePoint List Adapter with SSIS, please be sure to leave comments or suggestions on the CodePlex site for Kevin and his team.
Tags: ssis,
sharepoint
Categories: SharePoint |
SSIS
My blog is officially one year old now and I thought I would share some interesting facts, some gleaned from metrics generated by this blog engine and others from Google Analytics:
- 61 blog posts (not including this one)
- Over 1300 unique visitors from 71 different countries
- Average visitor spent 3 ½ minutes on the site and visited more than four pages
- Most popular blog post and still the most frequented these past few months is: Extracting SharePoint data using SSRS. This post represented 19.47% of all page views on my site for the past year
- 61.09% of traffic came from search engines, 31.08% from direct traffic and 7.83% from referring sites
- Given that this blog covers Microsoft technologies and methods, it is no surprise to me that Internet Explorer is 78.61% of browsers used against this site, though Google's Chrome is coming on strong given it's very recent release
I would like to thank everyone who has e-mailed me thanks, suggestions, questions and random comments this past year. Please keep them coming!
I will be starting a pet project this month that will cover aggregations over public economic data – data feeds that the Treasury, Federal Reserve, Labor Stats Dept., etc. put out on a regular basis. It is pretty ambitious, will be long running, but done in many small phases. I will post updates here, as well as talk about all that I learn along the way. The site, once the shell is posted later this month, will be located at: http://www.datapublicus.com.
Please feel free to e-mail me with any questions or suggestions, and for anyone going to the Microsoft BI conference next month, hope to see you there!
Tags:
Categories:
Earlier this year, I posted a short feature on auditing in SQL 2005 using DDL triggers to capture events and log them. In SQL 2008, one of the less sexy features added, though highly important and underscored by this current eras reporting and traceability debacles in public documents, is SQL 2008's new auditing features (less sexy from a populist point of view, I do note that there is probably an avid community out there with an audit fetish, so no intention of diminishing this feature's importance to you and yours).
Quite simply, it is implemented by the following sample syntax (full reference here):
One of the interesting switches you can use for auditing is the ON_FAILURE argument accepts parameters of CONTINUE or SHUTDOWN. If auditing is paramount for this system, then any failure in the auditing mechanism will turn the SQL Server service off. As auditing is SQL instance specific, this can be useful for many types of compliance scenarios.
The audit is set up at two levels; first, as shown in the script above, at the server level, which captures audit events (more on those events here).
The second level that can be implemented is at the database level. The script below is run within the context of the database I wanted the audit on, while the first script needs to be run within the context of the master database (full reference here):
This particular audit specification will capture all DDL executed against my particular database and make it part of the log.
For more, you can reference books line here for the Auditing home page for SQL 2008.
Tags: auditing,
practices,
security,
sql 2008
Categories: Security |
SQL Server
SQL 2008 has now been fully released for just over a month, and for those who have not been keeping track of additions throughout the CTPs and Release Candidates, here is a bit of useful information: SQL 2008 has 46 new DMVs(as of version 10.0.1442).
If you have a 2005 instance on your box along side your 2008 install, you can run this from your 2008 instance query to take a look at the output:
select name fromsys.all_objectswhere name like'dm_%'
except
select name from [localhost\sql2005instance].master.sys.all_objectswhere name like'dm_%'
Several of these DMVs correspond directly to feature additions such as Filestream, new cryptography features and resource governor, plus several that give more insight into the host OS.
Here is the complete list if you don't have a 2005 instance handy:
dm_audit_actions
dm_audit_class_type_map
dm_cdc_errors
dm_cdc_log_scan_sessions
dm_cryptographic_provider_algorithms
dm_cryptographic_provider_keys
dm_cryptographic_provider_properties
dm_cryptographic_provider_sessions
dm_database_encryption_keys
dm_db_mirroring_auto_page_repair
dm_db_mirroring_past_actions
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_filestream_file_io_handles
dm_filestream_file_io_requests
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatcher_pools
dm_os_dispatchers
dm_os_memory_brokers
dm_os_memory_node_access_stats
dm_os_memory_nodes
dm_os_nodes
dm_os_process_memory
dm_os_spinlock_stats
dm_os_sys_memory
dm_resource_governor_configuration
dm_resource_governor_resource_pools
dm_resource_governor_workload_groups
dm_server_audit_status
dm_sql_referenced_entities
dm_sql_referencing_entities
dm_tran_commit_table
dm_xe_map_values
dm_xe_object_columns
dm_xe_objects
dm_xe_packages
dm_xe_session_event_actions
dm_xe_session_events
dm_xe_session_object_columns
dm_xe_session_targets
dm_xe_sessions
Tags: sql 2008,
management,
auditing
Categories: SQL Server
More wall decorations! Actually, keep this as a PDF on your computer for reference instead of printing and save paper.
I came across this SQL 2005 system views poster in my travels across cyberspace. It is posted on a cube at the client site I am currently at, so I've been looking for a soft copy ever since I started here.
This poster breaks out the system views in nicely colored patterns in the following groups:
- Objects, Types and Indexes
- Linked Servers
- Common Language Runtime
- Database Mirroring
- Service Broker
- Security
- Endpoints
- Partitioning
- Databases and Storage
- Transaction Information
- Server Wide Configuration
- Server Wide Information
- Execution Environment
- Tracing and Eventing
Tags: sql server,
storage subsystem,
sql engine,
management
Categories: SQL Server
After putting up a primer post about statistics last week, my mind has now jumped to another facet of performance: cached plans.
Cached plan objects, whether they are for procedures, ad-hoc statements, views, etc., can have both a negative and positive effect on system performance. There are some general guidelines for when you will want to consider freeing the cache in order to negate when the cache plan is having a negative effect on system performance:
- Whenever you make database schema changes to widely used objects
- Large loads of data or heavy transactions higher than average
- If the database servers up ad-hoc reports
- You are getting ready to do performance testing
The SQL statement to clean up the cache is: dbcc freeproccache [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ].
On the three optional parameters you can use with this statement:
- Plan_handle is the varbinary(64) identifier for the in memory plan. This identifier is constant only while this plan remains in system cache. It can be found in the sys.dm_exec_sql_text, sys.dm_exec_query_plan or sys.dm_exec_plan_attributes DMVs
-
SQL_handle is also a varbinary(64) identifier. It refers to the batch or stored procedure that a query is a part of in the system. It can be found in the sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants or sys.dm_exec_connections DMVs
-
Pool_name is the name of a Resource Governor resource pool and is a sysname. You can find more detail in the sys.dm_resource_governor_resource_pools DMV.
For heavy performance testing, or for a server that is really bogged down, the dbcc DROPCLEANBUFFERS statement is a way to clean out the cold buffer cache without restarting the server. It is recommended though, to run a CHECKPOINT command; this command forces SQL to write all dirty pages to disk.
After this has completed, then run the DROPCLEANBUFFERS command against the system to clean out the system buffer. Note that for pure reporting systems with no writes back to the database, this is not a required step.
Tags: performance,
sql engine
Categories: SQL Server
With all the new features in SQL 2008, it is easy for me to call 29 pages an executive summary.
Kevin Kline, SQL Server MVP, has put together a top ten list that goes into relative detail on the following ten points:
- SQL Improvements
- Developer Improvements
- Reporting Services Improvements
- Compression
- Data Types and Data Storage
- Security
- Resource Governor
- Auditing and Change Tracking
- Analysis Services Performance Improvements
- Multi-Server Management
The whitepaper can be downloaded here from Redmondmag.com.
Highly recommended read, even for those who have been using SQL 2008 for a while now.
Tags: sql 2008,
sql server
Categories: SQL Server
If you are lucky enough to have a large format color printer (please save paper and don't print!), then TDWI has the download for you: a 20x30 Master Data Management technology poster.
For anyone who has not found it yet, here is a direct link to the PDF for Microsoft's MDM roadmap – of interest given their acquisition of Stratature last year.
Be kind, download TDWI's poster and keep a soft copy rather than ordering a print or printing it yourself. A lot of people love to have these on their walls, but rarely, if ever, reference them. This poster has a lot of great summary data and key concepts, so it will make for a great reference.
Tags: master data management,
data management
Categories: Metadata and MDM