Основы ввода-вывода Microsoft SQL Server. Часть 2 (продолжение)
По материалам статьи Bob Dorr:
Microsoft SQL Server I/O Basics. Chapter 2
Разреженные файлы / Копия на записи / Контура
Для снимков баз данных и онлайновых операций DBCC CHECK* используется присутствующая
в NTFS технология разреженных файлов (sparse files). В этой главе будет дана детальная
информация по этой технологии, применяющейся теперь и в SQL Server.
Обратите внимание: на момент публикации, заявленные производителем новшества,
такие как "thin provisioning", еще не были протестированы. Более детальную информации
по этой теме можно найти в программе
Always On Storage Solution Review SQL Server.
Онлайновые операции DBCC CHECK* для каждого проверяемого файла данных используют
в качестве переходного процесса контур разреженного файла. Имена контуров соответствуют
следующему шаблону: «ORIGINAL FILE»:MSSQL_DBCC«DBID».
Контур - вторичная область данных, связанная с оригинальным файлом, и поддерживаемая
файловой системой. Онлайновые DBCC операции используют контура, для создания переходных
снимков базы данных, на момент исполнения проверки. Этот снимок пользователям базы
данных недоступен. Контур снимка дает возможность онлайновой DBCC операции сымитировать
и проверить все факты используя точную на определённый момент времен копию базы
данных. Для этого нужно только непосредственно само хранилище. Во время онлайновых
DBCC операций, в контуре сохраняются только те страницы, которые изменились после
начала DBCC. Когда онлайновая DBCC операция закончится, контур удаляется.
Стоит обратить внимание и на то, что контур предоставляет возможность DBCC операциям
уменьшить гранулярность блокировок. Если контур создать не удаётся или недостаточно
места, DBCC возвращается к старому алгоритму, в основе которого лежит блокировка
таблиц. Администратор должен обеспечить достаточный запас свободного места на
каждом томе, что необходимо для обеспечения высокого параллелизма обслуживаемых
Для получения более подробной информации об онлайновых DBCC операциях, см.
DBCC Internal Database Snapshot Usage
в SQL Server 2005 Books Online.
ССЫЛКИ НА СТАТЬИ
Статьи на русском языке
Возможности переменных Windows Powershell
Если вы работаете с языками сценариев на основе Windows, такими как VBScript или KiXtart, вы знаете, что переменная это просто удобное "приспособление" для хранения данных. В языке Windows PowerShell тоже есть переменные, но они, в отличие от переменных в более старых языках сценариев, предоставляют разработчикам куда больше возможностей.
Развертывание файловой системы EFS. Часть 2
Развертывание любой файловой системы EFS (Encrypting File System - шифрованная файловая система) всегда сосредоточено вокруг двух элементов: серверной части, с упором на управление сертификатами и агентами восстановления, и пользовательской части файловой системы EFS. В прошлом месяце мы рассматривали серверную часть, обсуждали варианты восстановления данных и ключей,
Временные таблицы в sql server. Так ли необходимы временные таблицы?
Временные таблицы всегда прекрасно помогали разработчикам. Раньше, когда я использовал access, я обычно создавал временные таблицы, которые удалял после решения задачи. При использовании sql server решить задачу можно гораздо проще. Не так ли?
Восстанавливаем данные с жёстких дисков и RAID: опыт Kroll Ontrack
Если случится катастрофа, такая, как выход из строя жёсткого диска, на помощь всегда могут прийти профессионалы. Мы посетили центр восстановления Kroll Ontrack, где происходит процесс возвращения пользовательских данных "к жизни". Что интересно, можно восстанавливать не только отдельные жёсткие диски, но и RAID. Тем не менее, регулярно делайте резервные копии - так вы сэкономите массу нервов, денег и времени
Microsoft SQL Server 2005 Mobile Edition Device SDK
Доступен для скачивания Microsoft SQL Server 2005 Mobile Edition Device SDK. Страница загрузки тут:
Вчера стал доступен исправленный: Data Mining Add-ins for Microsoft Office 2007
В своём блоге, Jamie MacLennan настоятельно рекомендует всем, кто пользовался этой "примочкой", заменить её обновлённой версией (там же он поясняет причины), скачать которую можно отсюда:
Ошибка: обратитесь к документации!
Меня недавно сподвинуло на небольшое исследование и я подумал, что результаты этого исследования могут быть полезны не только мне. Проблема: SQL Server 2005, установлен SP2 и , при обращении через ODBC, начинают вылетает ошибка со следующим описанием:
Худшие методы (MS SQL Server) - игнорирование первичных ключей и кластерных индексов
Это третья статья в серии статей, посвященной Худшим методам (см. "Худшие методы - часть 1 очень длинной серии!" и "Худшие методы - объекты, не принадлежащие DBO"), которые пока вызвали довольно мало откликов читателей. Не каждый пока соглашается со мной, однако, похоже, что я не единственный, кто видит много "плохих" методов повсеместно.
Новые возможности T-SQL в MS SQL Server 2005 (исходники)
В MS SQL Server 2005 появилось множество новшеств, позволяющих еще более продуктивно использовать эту СУБД. Многие из них, такие как новые типы данных, интеграция с платформой .NET, поддержка XML, новые функции ранжирования, улучшения в системе безопасности и прочее, уже были ранее описаны в журнале RSDN Magazine [1, 2, 3, 4, 5]. В этой статье будут рассмотрены новые операторы и функции работы с данными. В связи с грядущим в ноябре выходом финальной версии, приведенная информация является предельно актуальной для всех разработчиков, использующих SQL Server.
Использование типов text, ntext и image в SQL Server
До сегодняшнего дня, сталкиваясь с задачей хранения большого объема бинарных данных в SQL Server по традиции мы использовали тип image. Однако, сегодня мне коллега присылает ссылку на статью MSDN, в которой говориться следующее:
Connecting Excel 2003 to AS2005 - need OLE DB for Analysis Services 9.0
If you need to connect Excel 2003 pivot table to SSAS 2005, you need to have OLE DB for Analysis Services 9.0 installed. Excel 2003 will default only have the 'Microsoft OLE DB Provider fro OLAP 8.0' installed, and this driver will not work for SSAS 2005.
Checking instance and database parameters
In yesterday's post, we had covered how to check for init.ora parameters and their settings in Oracle. In this post, we will cover SQL Server and DB2 LUW.
Backup and Restore with User Instances
Backup and Restore is an important part of any application, but when you're writing an application for an end-user, it becomes even more important that you do a good job of handling Backup and Restore in your code since you end-user will likely not know much about being a Database Administrator. Add to that the fact that we don't include SQL Agent or the Maintenance Plan wizard in SQL Express, and you have the perfect reason to roll your own solution for Backup and Restore.
Report Design Tips and Tricks
This white paper covers best practices on report design and helps you avoid common mistakes when choosing a report layout and output format. Take advantage of existing product features to achieve the results you want. The paper includes report and code examples that implement functionality that is frequently requested. (32 printed pages)
New Cartesis Benchmark Demonstrates Superior Performance
Peak Performance of 1,500 Concurrent Users and 12 Simultaneous Consolidations Deliver Framework for Customer Success
Choosing Between SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition
The SQL Server family offers two products suitable for local storage: Microsoft SQL Server 2005 Compact Edition and Microsoft SQL Server 2005 Express Edition. With the release Compact Edition for desktop scenarios, Microsoft is positioning Compact Edition as the default local database. However, both editions are free to download and deploy. Choosing between the SQL Server Express and SQL Server Compact edition of SQL Server 2005 can be difficult because they seem to target the same scenarios. This paper helps developers understand the benefits of each edition and when each edition should be used for local data storage.
Using a RAM Disk with Analysis Services
One topic that has come up occasionally over the years is whether there is any merit in using a RAM Disk with Analysis Services to improve performance. Certainly in the SQL Server world they seem to have some benefits (see for example Tony Rogerson's post here: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx) but as far as I know no-one has ever done any testing for Analysis Services so when I found myself with a bit of free time and a customer who might potentially benefit I thought I'd do so myself.
Important Update for the Data Mining Addins!
Due to a late found performance regression we decided to rerelease the data mining addins. A change we made in the product shortly before release was causing queries in the Data Mining Client for Excel to take several minutes instead of several seconds. With the fix, for example, I can do a prediction query against 10,000 rows in Excel and have the results populated back in to the worksheet in under 20 seconds on my laptop.
Announcing Service Release 1
I am happy to announce that first Service Release of Visual Studio 2005 Team Edition for Database Professionals is in the works and will be available in the second quarter of CY 2007. In this service release we are addressing issues that were found by our customers as well as three bigger areas that we wanted to address based on the continuous feedback from users.
OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2
SQL Server 2005 SP2 has an important enhancement to OBJECT_NAME metadata function and a new OBJECT_SCHEMA_NAME metadata function. I will first describe the old functionality to give some context and demonstrate how the new features help a lot.
Additional Q&A on the Visual Studio Orcas Sync Designer
Q: Why does the Orcas Feb CTP Typed DataSet designer not work on Vista? A: Visual Studio Orcas is not yet fully compatible with Vista. In the Feb CTP there are several issues with something we call "red bits". These are changes to the runtime that shipped in .NET 3.0 which shipped with Vista. For our own internal testing, we do have fixes allowing us to test on Vista and all the scenarios are working. However, due to the way the dlls are signed and shipped in Vista, and the time crunch we're working under, we weren't able to get the work done in such a way that we could service a Vista box that had the Feb CTP installed. This was a very tough decision as we do not want to set the expectation that Visual Studio Orcas won't be Vista compatible. But, it was felt more important to make sure that anything we ship, including the CTP must be serviceable. As we all know, we're working on to maintain our schedule, and since CTP's are not meant to reflect the final quality, it was determined to ship the Feb CTP without full Vista support. As those that have seen my demos, we do have Vista working, and we will have full support for Vista. I believe, but not sure, that B1 will be fully Vista compatible. So, for now, if you're using Vista, the Feb CTP should be run in a VPC. In general, it's not a bad practice anyway.
T-SQL: A T-SQL poser - Part 1
Today one of my colleagues, Saivendra Kayal, posed a problem and wondered if it could be solved in T-SQL.
T-SQL: A T-SQL Poser - Part 2
OK, a couple of days on and it seems my T-SQL poser created a little bit more interest than I expected - 7 comments and some follow-up blog posts testify to that (yes, 7 is alot for me). If you're browsing here first then go and read the previous post to understand the background here.
Where is my SQL Server Integration Services package running?
By 'where package is running' I mean what CPU and memory resources are used, and where the SSIS needs to be installed. This simple question surprisingly often confuses users, as there are many options to store packages and many options to run it.
Why do I get "product level is insufficient..." error when I run my SSIS package?
This is a common question at SSIS forum, so I decided to post an answer here to save myself from re-typing it again and again :)
Why Debug command is disabled for my SSIS package?
Debugging support for SSIS packages is probably one of the best features of the SSIS 2005 designer, but sometimes you may find that "Debug" and "Start without Debugging' commands are grayed out. Why?
Running SSIS package programmatically
I got several questions asking what is the best way to run SSIS packages programmatically. One question is about running SSIS from a .NET 1.1 application (SSIS uses .NET 2.0). Another about running package remotely "Do I really have to write an ASP.net app just to run a package on the server?" There were also questions about running package from ASP.NET page (which second user tries to avoid, but surprisingly many people want).
SSIS: File System Task Move and rename files in one step
In some ETL scenarios, when processing files, it is necessary to rename the already processed files and move them to a different location. In SSIS you can accomplish that in a single step using the File System Task. The example I have prepared assumes the package will process a set of files using a ForEach Loop container; then for each file, using the 'Rename' operation in File System Task will do both; rename and move the file.
SQL WHERE List Matches Any or All
I saw a cool post recently from Jon Galloway called "Passing lists to SQL Server 2005 with XML Parameters". This is a pattern I've used several times while building the new version of Channel 9. If you'd like to learn how to pass in lists to stored procedures, check out Jon's post.
SYSK 312: Why You Should Not Change SQL Server Service Account Using Services Management Console
The SQL Server documentation states that it's strongly recommended to use SQL Server Configuration Manager and not Control Panel\Administrative Tools\Services (services.msc) when changing SQL Server or SQL Server Agent service account. The question is - why?
Microsoft MVPs: Always Looking Ahead
Last week, I had a great time and learned at lot at Microsoft MVP Summit. At these gatherings, it's always exciting to mingle with the world's best Microsoft technologists--both from inside Microsoft and from the partner community. Also, I learned a lot of exciting news about the next version of SQL Server, codenamed Katmai. Here are the 10 most exciting changes that will be happening in Katmai. This is breaking news that very few people know yet. Don't forget--you heard it here first!
I blogged here about a problem with SQL Server check constraints and Jamie Collins made a comment that Jet also suffers from the same problem.
In response to Jamie Thomson's T-SQL poser, here's one very efficient method of concatenation in an aggregate query. It uses the FOR XML clause to turn a result set into a string.
Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting
Upgrading to SQL Server 2005 has many benefits . As we look back at the past year and the deployment of SQL Server 2005 with our ISV partners, one important but under-advertised feature in SQL Server 2005 became increasingly visible; the ability to control the dynamic name resolution behavior. This capability applies to workloads where object names are NOT fully-qualified with a two or a three part name (for example: "database.schema.object"). If you already fully qualify your object references, which is a SQL Server best practice, then this blog does not apply to you.
Sync Designer in Orcas
Cool stuff gets attention from everyone! This is especially true with the Sync Designer in Orcas. The Visutal Sutido team has done a great job enabling developers to put Synchronization Services API to work in their data application quickly and hassle-free. I like to think of the sync designer as my personal sync assistant. Everything that gets me started quickly is a bless. You know, we developers like to poke around and step into the code to see how things work. But before we can do that, we have to get something running first and that's what the assistant will do for you! Nice work from the Oracs team.
Deep in Sync: Handling PK-FK Constraints
Roger Jennings in his most recent blog post asked an interesting question on how sync services deal with one-to-many relationship between tables undergoing synchronization? Roger is the most active blogger I know of and he is watching sync services closely. It is no secret that his blogs is one of my favorites. I check it at least once a day if not more! I cannot wait to see his upcoming article on sync services next May along with the test harness that he is working on.
Pocket Symphony (Enabling SSRS Feature in non-default Site Collections)
After installing the Reporting Services Add-in for Integration with SharePoint, you may find that the Reporting Services features are not visible in your SharePoint web sites. Why does this happen? Because when the add-in is installed, it is only activated for the sites in the default Site Collection.
ReportServerTempDB IO Saturation
Reporting Services uses a temporary database for storage of objects which are, well, temporary. For example, report snapshots which are associated with a particular user session as the result of a live execution. Cached report snapshots are also stored here because they begin life as a result of a live report execution. This means that in systems where there are a lot of live report executions happening, we are churning through a lot of data in the ReportServerTempDB. As your system scales out or up, you will undoubtedly experience a problem where you will begin to saturate the IO subsystem hosting the ReportServerTempDB.
How to avoid 1000 rows limitation when querying active directory (AD) from SQL 2005 with using custom code
As all of you know it is possible to query active directory from SQL Server with using ADSI provider as linked server. This solution works fine until you will have a lot of users in active directory. According to best practice guide windows system engineers always configured AD to return no more then 1000 rows per one query. Of course you can avoid this limitation too (with using range keyword or some other ways, as for me require deep AD knowledge). Some other extremily complicated T-SQL scripts can be found in internet. My current post's goal is to show two main things:
SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
This white paper covers some of the operational and administrative tasks associated with SQL Server 2005 security and enumerates best practices and operational and administrative tasks that will result in a more secure SQL Server system. Each topic describes a feature and best practices. For additional information on the specifics of utilities, features, and data definition language (DDL) statements referenced in this white paper, see SQL Server 2005 Books Online. Features and options that are new or defaults that are changed for SQL Server 2005 are identified. Coding examples for operational tasks use Transact-SQL, so understanding Transact-SQL is required for you to get the most out of this paper. : Security is a crucial part of any mission-critical application. This paper describes best practices for setting up and maintaining security in SQL Server 2005.
CASE function in SQL Server - part I
Muthusamy Anantha Kumar
In SQL Server, the CASE functions evaluate a list of conditions and then return one or many results. In this article, I am going to illustrate the various uses of CASE functions in SQL server, in different places.
SqlCredit - Part 3: Schema/Proc Updates and Automated Unit Testing
This is part 3 of a series. If you have not read part 2, you can find it here. In this month's installment, we will discuss updates based on feedback from part 2 as well as introduce automated unit testing.
GROUP BY v DISTINCT (group by wins!)
Jamie Thomson (SQL Server MVP from the UK) threw out a challenge recently, and it was interesting to see the responses. His question was about string concatenation, and of course, FOR XML PATH('') made a strong case for itself. It was put into SQL2005 for exactly this purpose. But I noticed that the responses using this pattern also used the DISTINCT keyword, to stop there being duplicate entries in the results.
Analysis Services 2005 delivers new features -- start to finish
In this series of tips about Microsoft Analysis Services (MSAS) 2005, I'll be discussing some of the new features available and how they affect migration from the existing applications developed with MSAS 2000. The new Analysis Services tool is considerably different from previous versions, so each tip will only cover a small subset of the new features.
Twiddling the knobs: The Default Backup directory
Whenever I attempt to set myself up as an expert on anything, a Higher Being seems to belabour me with a metaphorical baseball bat for my lack of humility. In consequence, this Blog entry is really more of a question than a nugget of information. How does one change the default backup directory for a server by using the SSMS or Enterprise Manager, without changing all the default directories at the same time?. What is the TSQL to do it? (the editor will send a Simple-Talk goodie bag for the best correct solution)
Pop Rivett and the Case of the Rogue SPIDs
Pop Rivett put down his violin, stretched back in his fireside chair, and stared severely at me. "My dear fellow, you know my methods. The mystery of the rogue SPIDs has been one of the more exacting tasks it has been my fortune to be given.
SQL Server Wait Events: Taking the Guesswork out of Performance Profiling
Measuring what is actually happening is always the best course of action when investigating performance issues on databases, rather than relying on cache hit ratios, or best practices, or worst of all, guesswork. In order to solve performance issues, you may take a gamble on buying a bigger box with the fastest CPUs, only to discover that it doesn't achieve anything. Understanding and measuring is the key to improving database performance. If, for example, you measure high log write times, then you know exactly what you should do: go look for ways to speed up your log drive(s), or take away the load on the log drives from other processes or queries. This article introduces some techniques that will allow you to pinpoint exactly where the performance issues are in your system, so you'll know exactly where to spend your time (and money) in solving them.
Are you ready for Data Warehouses?
Data warehousing is being used more and more everyday and longtime data warehouse DBA Janet Wong brings us a short look at her first project and some thoughts about warehousing in general.
Five Realtime DTS Examples
SQL Server 2005 includes an amazing ETL environment in Integration Services, but many DBAs will be using DTS and SQL Server 2000 for years to come. Jambu Krishnamurthy brings us a few handy examples of how you can customize your DTS environment.
The Effect of NOLOCK on Performance
Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system.
A Handy Server Property in SQL2005
One frequenctly asked question is, "How do I find out via T-SQL which cluster node is running this SQL Server instance?"
SQL Server Speaks Almost As Many Languages As I Do
Saw this error while running a job
NULL Trouble In SQL Server Land
I am seeing a lot of searches for SQL + Nulls from this site so I decided to blog about it. Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on ,not to off.
SQL Server Trace Event Definitions
If you are using Event Notifications or capturing events in Profiler you undoubtedly have noticed that MS have used the same columns (xml elements) for storing different data for each event. For example there is an element called "integer data". You may ask what is stored in here cos it ain't obvious from the name of the element.
Change the Status of Table Items
Test your SQL Server savvy in this month's Reader Challenge. Submit your solution in an email message to challenge@ sqlmag.com by April 12. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. (Make sure you're signed up for this free weekly email newsletter at http://www.sqlmag.com/email.) The first-place winner will receive $100, and the second-place winner will receive $50.
Share Your DATETIME Thoughts
We've received some interesting feedback to Itzik's series about datetime calculations. Remember that you can share your thoughts about any of our articles in the SQL Server forums (http://sqlforums.windowsitpro.com), and you can leave comments specifically about Itzik's columns in the T-SQL subforum (http://sqlforums.windowsitpro.com/web/forum/categories.aspx?catid=80&entercat=y). We thought we'd share a couple of responses that Itzik has received from readers.
Do You Know Your MVPs?
Where do you go for expert SQL Server technical guidance? MSDN or TechNet, SQL Server Magazine, Microsoft newsgroups and MSDN forums, your local user group, technical conferences, or regional education events? If so, chances are you've been helped by a Microsoft Most Valued Professional (MVP).
Review: Extrusion-Prevention Systems
Customer lists, sales figures, R&D, credit-card numbers ... the list of data you absolutely do not want public gets longer all the time. So why do most organizations choose databases to fit the needs of other applications, say, ERP systems, rather than focusing on security? Even if impenetrability was a selling point, you likely found out all too quickly that the database vendor's security model didn't mesh with your real-world network infrastructure.
SQL Apprentice Question: I have the following SQL schema
Incorrect syntax near the keyword 'As'
SQL Apprentice Question: I'm trying to complete an If, Then statement within a script that I put together, and I keep getting an "Incorrect syntax near the keyword 'As'" error toward the end of the statement.
What I learned at the MVP Summit last week - and I can tell you about!
Rodney R. Fournier
The 2007 MVP Summit in Redmond/Seattle was awesome. Lots of great content. Here is what I learned: 1) SQL 2005 can cluster with Standard Edition (ok I knew that). What I learned was that you can have multiple SQL instances (that run on 2 nodes only) within any size cluster. So, if you have a 4 node cluster you can run 2-3 instances with multiple copies of SQL 2005 Standard Edition. Cool! Think of the savings.
Calculating R-Squared for Microsoft regression models
This tip shows you how to compute the R-Squared and Adjusted R-Squared metrics for Microsoft regression models using a stored procedure. R-Squared (a.k.a. Coefficient of Determination) is a well-known metric that measures the goodness fit of your regression model. Its value lies between 0 and 1. The closer R-Squared is to 1, the better your model is with respect to the training data. R-Squared can be calculated as:
Objectify Data With ADO.NET vNext
The ADO.NET vNext August 2006 Community Technical Preview reincarnates ObjectSpaces' object/relational mapping technology and OPath query language as the Entity Framework, Entity Data Model, Entity SQL, and LINQ to Entities for the Visual Studio "Orcas" release.
Program SQL Server 2005's Service Broker
SQL Server 2005's versatile Service Broker infrastructure enables asynchronous messaging between databases and server instances, handles database server event notifications, responds to database change notifications, and sends Database Mail.
Add Reports and Charts to Web Pages
The ASP.NET 2.0 version of Visual Studio 2005's ReportViewer control and its Report Designer let Web page developers quickly embed, format, and export interactive reports and charts without running a SQL Server Report Server.
New Entity Data Model Graphic Designer Prototype
Microsoft's ADO.NET vNext team posted on MSDN the code for the first Entity Data Model (EDM) Designer Prototype CTP on September 27, 2006. Pablo Castro demoed this prototype in his September 1, 2006 "ADO.NET Entity Framework: What. How. Why." Channel 9 video. The video coincided with the release of the ADO.NET vNext August 2006 CTP, which is an EDM Designer prerequisite.
Documentation for SQL Server CE 3.5 Sync Demos
Rafik Robeal has posted detailed, illustrated documentation for the four SQL Server 3.5 synchronization demos to his SyncGuru Web site. My January 26, 2007 Microsoft Releases Synchronization Services CTP, Three Sample C# SSCE Sync Services Projects, and Conflict Resolution with Sync Services posts describe the demos and related Sync Services technologies.
Sync Services for ADO.NET Overview
Steve Lasker's lengthy Q&A on OCS & Sync Services for ADO.NET post covers use of merge replication, Remote Data Access (RDA) and Sync Services for ADO.NET for synchronizing data between servers and clients (or publishers and subscribers) of Occasionally Connected Systems. As you'd expect, the emphasis is on Sync Services for ADO.NET with SQL Server Compact Edition (SSCE) v3.5.
Mike Taulty Dissects LINQ to SQL
Thanks to Julie Lerman's Deconstructing LINQ to SQL post, I learned about Mike Taulty's two-part series that digs into the inner workings of the LINQ to SQL API:
Lighten Up Your Local Databases
Put local data storage on a resource diet and gain performance with the newly upgraded (and free) SQL Server 2005 Compact Edition.
SSCE Sync Designer Q&A and Screencast
Sync Services pilgrims working with the Sync Designer preview in the Orcas March 2007 CTP had many of their questions answered by Steve Lasker's Additional Q&A on the Visual Studio Orcas Sync Designer post of March 21, 2007, which supplements Steve's original Q&A on OCS & Sync Services for ADO.NET post of March 18, 2007. First look at the Visual Studio Orcas Sync Designer and Going N Tier w/WCF, Synchronizing data using Sync Services for ADO.NET and SQL Server Compact Edition are a pair of screencast posts (dated March 22 and 23, 2007), which cover the Sync Designer that's scheduled to debut in Visual Studio Orcas.
A Sync Services Bidirectional Test Harness
The Microsoft Synchronization Services (Sync Services) 1.0 API (runtime) requires a substantial amount of developer-authored code to define the basic elements required to perform bidirectional synchronization between an SQL Sever 2005 Compact Edition (SSCE) client and an SQL Server 2005 [Express] or other RDBMS server. The VB code (without empty lines or comments) to implement bidirectional synchronization for a pair of simple tables is about 95 lines if you take advantage of the runtime's CommandBuilder or about 165 lines if you don't.
OLAP Design Best Practices for Analysis Services 2005
This paper outlines the recommended best practices for designing OLAP databases in Microsoft SQL Server 2005 Analysis Services to better meet the functional and performance needs of users. Designing a database for Online Analytical Processing (OLAP) databases is very different from designing a database for Online Transaction Processing (OLTP) and requires a different approach for achieving optimal results.
SQL Server 2005 Integration Services - Part 45 - Bulk Insert Task and Format Files
In the previous article of our series dealing with SQL Server 2005 Integration Services, we have presented a straightforward approach to importing data from delimited text files into database tables and views, using Bulk Insert Control Flow task (which, in turn, leverages T-SQL BULK INSERT functionality) with its basic formatting options. While such an approach might work well in some cases, its lack of flexibility is likely to become an issue (as was the case with our sample data file). One way to address such situations is to employ format files, which, while slightly more complex to implement, offer additional processing options. We will cover two types of such files, describing in more detail their characteristics and providing examples of their implementation.
Top 10 SQL Server indexing tips to improve performance
Significantly improve your SQL Server performance through proper indexing choices. We've tallied our most popular tips on the topic of indexes so you'll be able to quickly get the answers to your indexing questions. Learn how to weed out valueless indexes, design high performing indexes and query your existing SQL Server indexes.
Alias to be or not to be
EDIT: This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around.
Table partitioning with SQL Server 2005
Performance tuning is the ultimate responsibility of every valuable SQL Server administrators. Although ensuring data security and availability are our topmost goals, if the applications don't perform to the users' expectations, DBAs often get the credit, or blame, for poor design and implementation. SQL Server 2005 offers numerous improvements in terms of database performance, not the least of which is table partitioning. If you haven't examined the table partitioning feature, do yourself a favor and take time to read this tip.
Creating cross tab queries and pivot tables in SQL
Sometimes, you just absolutely have to generate a cross tab in SQL. It won't do to have the reporting system do it, nor is it feasible to build that functionality into the application. For example:
The SQL Server 2005 XML Temptress
SQL Server 2005 has greatly expanded the XML capabilities of this platform, but should you use it in your database? New author and MVP Simon Munro brings us a look at how he has used XML and where it might fit in your database.
Saving a Sales Order Part 1
How many times have you tried to save a sales order to your database? For many DBAs this is a common scenario and one of the challenges is the many round trips for the various line items. Jacob Sebastian brings us the first part of a four part series looking at how you can use XML to reduce the round trips in SQL Server 2000.
A Review of SQL Refactor
Longtime author Dinesh Asanka takes a look at one of the new tools on the market, SQL Refactor from Red Gate.
Don't Forget the Business Transaction Throughput and Response Time
People often rely on rules of thumb to tell whether there is a problem. DBAs are no exception. For instance, we are told that if the I/O latency (i.e. Avg. Disk sec/Read or Avg. Disk sec/Write) is greater 20 milliseconds, it's bad. In addition, if the current disk queue length is significant, that adds to the evidence of the I/O subsystem being stressed.
SQL Server 2005 Security Overview for Database Administrators
SQL Server 2005 is the first version of this server software released since Microsoft developed and implemented its Trustworthy Computing initiative. The software is now secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important new security features in SQL Server 2005. It tells you how, as an administrator, you can install SQL Server securely and keep it that way even as applications and users make use of the data stored within.
Protect Sensitive Data Using Encryption in SQL Server 2005
SQL Server 2005 is built up of layer upon layer of security, designed to provide defense in depth. SQL Server uses strong encryption to provide the best protection for data, a nearly inviolate barrier to exposure. This paper explores the encryption features in SQL Server 2005 that you can use to protect your data. Key management is the hardest part of encryption and keeping data protected. SQL Server can handle key management for you or you can do it yourself. SQL Server implements a key hierarchy that you can use to create three types of keys using a variety of encryption algorithms to secure your data.
SQL Server 2005 and Oracle 10g Security Comparison
This paper compares the security features available in SQL Server 2005 and Oracle 10g R2. It shows that the same security features exist in both databases, but that SQL Server 2005 is significantly cheaper to purchase and own than Oracle 10g for the same functionality.
Waiting and Blocking Issues
I will start this chapter on blocking by talking about waiting. Why would I start a chapter on blocking with waiting? Well, the two are very much related, and they are often treated as synonyms. Because they are so related, the SQL Server concepts and tools related to each are intermingled; therefore, it is important to distinguish one from the other. Conceptually, waiting usually refers to an idle state in which a task is waiting for something to occur before continuing execution. This "something" might be the acquisition of a synchronization resource, the arrival of a new command batch, or some other event. Although this description is generally accurate, there is an important caveat: Not all tasks identified as waiting within SQL Server are in fact idle. This is because the waiting classification is sometimes used to indicate that the task is executing a particular type of code. This code is often outside the direct control of SQL Server. In effect, the SQL Server task is waiting for the completion of the external code.
Two options for scaling out your SQL Server system
If you decide to scale out rather than scale up your SQL Server environment, read about two options for scaling out to allow for high levels of scalability: horizontal data partitioning and vertical data partitioning. When I refer to scaling out a database system, I am really just talking about separating or partitioning the database system in a manner so you can take those parts and place them on separate database servers. This allows you to spread processing power across as many servers as necessary to accommodate expanding growth. However, additional features and functionality require additional complexity. A scale out database scenario is not a particularly easy one to design or administer. You must answer many difficult business and technology-driven questions before you can successfully implement a scale out of a database system. This article presents two options for scaling out your database system to allow for high levels of scalability: horizontal data partitioning and vertical data partitioning.
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
I have seen many people wonder why (or complain that) their IDENTITY or AUTONUMBER / AUTOINCREMENT column has gaps in the sequence. Before I explain situations which can cause gaps, I'd like to express that you really shouldn't care about gaps. IDENTITY values are a surrogate row identifier, and nothing more. They should not have any meaning external to the database and/or application. You should not identify customers, for example, by a CustomerID you generated artificially. So you should not rely on there being a CustomerID #35 just because there is a CustomerID #36. Another thing people do is use this number to determine how recently a row was inserted, or compare ... if you want to track this data, store the timestamp of when the row was added (see Article #2499 and Article #2448 for information).
How do I use a variable in an ORDER BY clause?
Often, we want to allow users to determine how their data will be ordered. So, let's say we have a table like this:
Should I use a #temp table or a @table variable?
In a stored procedure, you often have a need for storing a set of data within the procedure, without necessarily needing that data to persist beyond the scope of the procedure. If you actually need a table structure, there are basically four ways you can "store" this data: local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name).
Самые популярные темы недели
Вышел русский SQL Server 2005!!!
Конфигурация внешних RAID массивов под большие объемы данных > 1TB
рефреш больших наборов - покритикуйте
Не боян еще? SQL 2005 SP2 выложили
Непонятности с использованием XLOCK хинта в select
Индексы и хинты - проясните ситуацию
временные таблицы и те кто ими пользуются...
Обновил SQL2000 до 2005 и резко упала производительность
Проблемы с SqlServerAgent
убить сессию без sysadmin роли?
А где обещанный full-text search с учетом русских словоформ в MS SQL 2005? (((
Сервер с двухядерным процессором или двумя простыми
Избитый вопрос, но все же! (размещение файлов БД)
Binary data большого размера в SQL. VC++ management.
Помогите составить запрос
dbcc shrinkfile (@file, emptyfile) тормозит
Помогите разобраться с планом
Восстановленеи БД из suspecta
Доступ к временной таблице созданной другим процессом
Select внутри IF BEGIN...END
Вопросы остались без ответа
xp_cmdshell под админом
Сново try catch
документы с аттачментами, сохранение
Вопрос о FOR XML Path