СОДЕРЖАНИЕ

1. СТАТЬИ 1.1. Основы I/O в SQL Server 2000 (продолжение) 2. ССЫЛКИ НА СТАТЬИ 2.1. Статьи на русском языке 2.2. Англоязычные статьи 3. ФОРУМ SQL.RU 3.1. Самые популярные темы недели 3.2. Вопросы остались без ответа

СТАТЬИ

Основы I/O в SQL Server 2000 (продолжение)

По материалам статьи Bob Dorr: SQL Server 2000 I/O Basics
Перевод Александра Гладченко

Сброс страниц данных на диск

Речь пойдёт о трёх основных механизмах, которые провоцируют сброс страницы данных на диск. Однако, все они использует одну и ту же встроенную подпрограмму работы с буферным пулом, с помощью которой происходит передача данных:

• Отложенная запись (least recently used - LRU и основанная на вытеснении памяти)

• Контрольная точка (на основании recovery - интервала)

• Безотложная запись (базируется на нерегистрируемом I/O)

Для эффективной записи при сбросе на диск используется WriteFileGather. Это позволяет SQL Server связывать последовательно расположенные грязные страницы в один запрос на запись.
SQL Server использует следующую последовательность сброса на диск одной страницы:

1. На страницу накладывается краткая блокировка, что позволяет предотвратить возможные её последующие изменения.

2. Гарантированный сброс на диск долговременного носителя записей журнала транзакций, относящихся к хранимому в заголовке страницы LSN.

3. Установка правильных параметров для вызова WriteFileGather.

SQL Server использует следующую последовательность действий, при необходимости сброса на диск следующей страницы, и повторения этих действий для нескольких страниц (до 16-ти страниц в целом, включая первую страницу).

1. Выполняется поиск по хэшу (hash lookup) следующей страницы. Например, если страница, которая сбрасывается на диск имеет номер 100, SQL Server ищет в массиве буферного хэша страницу с номером 101.

2. Если эта страница не найдена, то устанавливается конец цельного блока I/O, и отправляется запрос на этот I/O.

3. Если страница будет найдена, на неё налагается краткая блокировка, что позволяет предотвратить её дальнейшие изменения, т.к. она может быть "грязной".

4. Выполняется проверка, чтобы убедиться, что страница является "грязной" и должна быть сохранена. В противном случае снимается краткая блокировка и объявляется конец непрерывного блока I/O, как это задано и представлено асинхронным запросом на I/O.

5. Если страница "грязная", то всё повторяется с шага 1.

После того, как будет определен набор страниц, которые нужно сбросить на диск, вызывается функция WriteFileGather, которая отправляет (Async / OVERLAPPED) запрос на I/O с привязкой к функции повторного вызова, которая завершит операции I/O.
Когда SQL Server определяет, что HasOverlappedIoCompleted возвращает TRUE, используется функция GetOverlappedResults, которая собирает в системе информацию о завершении операции, и вызывает функцию повторного вызова. Функция повторного вызова делает соответствующее заключение об успешности операции I/O и снимает краткие блокировки на каждой из задействованных страниц.

Программа отложенной записи (Lazy Writer)

Программа отложенной записи в SQL Server 2000 и SQL Server 7.0 пытается определить расположение до 16-ти уникальных страниц нуждающихся в перемещении для возврата их в число свободных страниц. Если счётчик ссылок страницы дойдёт до нуля, она может быть возвращена в качестве свободной. Если страница отмечена как "грязная", её записи журнала и страницы данных будут сброшены на диск.
Таким образом, программа отложенной записи может единовременно сбросить на диск 16*16 страниц. Это будет достаточно эффективно, потому что многие из этих страниц останутся в буферном пуле SQL Server, но будут находиться после этого в состоянии - "свободна". I/O выполняется в фоновом режиме относительно основного SPID (идентификатор серверного процесса). Когда программа отложенной записи нуждается в дополнительных буферах для своего списка свободных страниц, нет нужды сбрасывать на диск буфера, просто выполняется высвобождение хэша и возврат в список свободных страниц.

Контрольная точка (Checkpoint)

Процесс контрольной точки в SQL Server 2000 периодически проходит по буферному пулу, анализирует буферы, которые содержат страницы указанной базы данных, и сбрасывает на диск долговременного носителя все "грязные" буферы. Это делает короче процесс регенерации (recovery), потому что операции отката назад потребуют для своего исполнения меньших физических затрат.
Как говорилось ранее, процесс контрольной точки использует тот же самый подход к I/O, отправляя до 16 страниц в одном запросе на I/O. Когда запрос на I/O отправлен (OVERLAPPED), контрольная точка не ждет немедленного исполнения каждого I/O. Контрольная точка продолжает отслеживать посылаемые и исполняемые запросы на I/O, но пытается при этом поддерживать высокий уровень исполнения незавершённых запросов на I/O (например, 100 постоянно обслуживающихся не завершённых запросов на запись). Это повышает производительность I/O и уменьшает время исполнения контрольной точки.
До появления WriteFileGather, SQL Server сортировал буферы обслуживаемой базы данных в порядке страниц и также в порядке страниц создавал запросы на I/O. Это порождало множество физических запросов I/O, потому что порядок страницы для сброса не соответствовал непрерывному расположению в памяти. Однако, довольно часто механизмы подсистем физического уровня предоставляли страницам такое физическое местоположение, которое располагало их в непосредственной близости, что способствовало достаточно быстрому исполнению запросов на I/O.
В более позднем дизайне, элеваторный поиск может быть проблематичен. Сбор нескольких запросов на I/O в порядке страниц обычно приводит к порядку, близкому к порядку на диске. При интенсивной нагрузке подсистем множеством запросов на I/O, упорядоченных таким образом, дисковод может обслужить эти запросы на I/O до запросов, которые могли быть не завершены до этого.
С появлением WriteFileGather, SQL Server может перемещать буферный пул, не требуя какого либо физического соотношения с порядком расположения страниц на диске. Собирая группы по 128 Кб (шестнадцать страниц по 8 Кб), SQL Server может передать блоки данных, используя для этого намного меньше физических запросов на I/O. Это позволяет процессу контрольной точки поддерживать хорошую скорость, и в то же время для запросов на I/O, которые носят случайный характер, поддерживать элеваторный поиск, который может повлиять на другие операции I/O. Все базы данных, кроме tempdb, обслуживаются контрольной точкой. Tempdb не требует регенерации (она пересоздаётся при каждом запуске SQL Server), поэтому сброс на диск страницы данных не оптимален для tempdb, и SQL Server старается избегать этого.
Контрольная точка защищает систему от наводнения операциями I/O, преобразуя их в последовательную форму посредством процессов контрольной точки. Единовременно, исполняться может только одна контрольная точка. Процессы контрольной точки и программа отложенной записи взаимодействуют друг с другом, чтобы управлять внутренними механизмами очереди на I/O.

Безотложная запись

Microsoft SQL Server 2000 использует безотложную запись для страниц данных, связанные с не регистрируемыми операциями (обычно это bulk insert / select into). Это предоставляет возможность экземплярам I/O асинхронно сохранить на диск "грязные" страницы без нежелательного образования больших частей грязного буферного пула. Процессами контрольной точки используется тот же самый механизм отправки запросов на операции I/O, как и у отложенной записи.
Microsoft SQL Server 7.0 не имеет возможности выполнять безотложную запись, вместо этого, контрольная точка проходит во время завершения транзакций, чтобы сбросить на диск все буфера базы данных. Это может спровоцировать много нерегистрируемых операций, которые будут преобразованы в последовательную форму, потому что может быть активна только одна контрольная точка.
Важно: Отложенная запись, контрольная точка и безотложная запись не ожидают немедленного завершения I/O. Они всегда отправляют запросы на I/O посредством WriteFileGather с опцией OVERLAPPED, и продолжают работать дальше, чуть позже проверяя успешность завершения I/O. Это позволяет SQL Server максимизировать ресурсы процессоров и I/O для соответствующих задач.

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[В начало]

ССЫЛКИ НА СТАТЬИ

Статьи на русском языке

Конфигурационное управление проектами разработки программного обеспечения
Дмитрий Лапыгин, Александр Новичков
CASE: Рано или поздно любой долгосрочный проект, связанный с разработкой программного обеспечения, разрастается до необъятных размеров, становясь трудно управляемым и трудно прогнозируемым. Руководство больше не в состоянии отслеживать конкретную деятельность подчиненных. Но, пожалуй, самая главная проблема в том, что руководящий состав не имеет четкого представления о качестве выпускаемого изделия. Подчиненные же, в свою очередь, лишены всестороннего осознания поставленных проектных задач, руководствуясь в своей работе не научной базой, а вдохновением и порывами души...

Англоязычные статьи

Top Tip SQL Server Reporting Services fields cannot be used in headers or footers - work around
Mat Stephen's WebLog
I'm grateful to Chris Hayes for this top tip. Reporting Services doesn't like you using data fields in its headers and footers - here are two ways to work around the problem
Adding security groups to Reporting services security doesn't take effect until IIS is restarted
Tudor's Reporting
If you grant Reporting services permissions (via http://servername/Reports | folder properties | security) to Windows security groups, you may find out that people belonging to those groups don't immediately get the expected access rights
Secure reports, parameters and User!UserID
Tudor's Reporting
I have seen enough questions on how to protect parameters passed to reports to warrant a blog entry
Reporting Services SP1 and rsInvalidDataSourceCredentialSetting errors
Tudor's Reporting
If you upgraded to RS SP1 and noticed some of your reports that used to work fail now with the following error: “The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)” here is the what to do
Microsoft SQL Server Reporting Services - measuring and improving performance
Tudor's Reporting
Optimize your report queries. Usually the bulk of report execution time is spent executing queries and retrieving results. If you are using SQL Server, tools such as Query Analyzer and Profiler can help you optimize your queries and Database Tuning Advisor can suggest better indexes for your database
Starting with SQL Server 2005 WMI with help of Scriptomatic
Michiel Wories' WebLog
SQL Server 2005 introduces a new WMI Provider that allows configuration of Services, Client Network Protocols, and Server Protocols. This is a great improvement for DBA that are managing many SQL Servers, as WMI allows you to query and modify SQL Server configuration settings remotely
In IDW14, A Slight Change In Syntax For ENCRYPTION_PASSWORD
scalabilityexperts.com
Hey folks, watch out for a little twist in some syntax with the latest version of SQL Server 2005, build 1116
SQL Server 2005 Reporting Services--Worth the Upgrade
Douglas McDowell
More than a year ago, Microsoft released SQL Server 2000 Reporting Services as an add-on to SQL Server 2000. Today, Microsoft announced the newest features in Reporting Services Service Pack 2 (SP2), so I thought now would be a good time to clear up a few misconceptions about Reporting Services 2005. Half the people I talk to think that the next Reporting Services release will be a whole new product with dramatic core changes. The rest of the people I talk to think that because we've seen so much added functionality in Reporting Services 2000 SP1 and SP2, Reporting Services 2005 will be the same as Reporting Services 2000. Although Reporting Services' core architecture isn't changing, the next release has plenty to get excited about. Recently, I talked to Microsoft Group Program Manager for Reporting Services Brian Welcker, who confirmed that the company is adding lots of functionality to Reporting Services 2005; it's worth the upgrade
Using Transactions in ADO.NET
netbard
An introduction to SQL Server transactions and how to use them in ADO.NET
Microsoft Releases Oracle to SQL Server Migration Guide
Paul Ballard
Microsoft has released a migration guide document for moving Oracle on Unix database implementations to SQL Server on Windows. It also includes Office templates for managing the migration including project structure, test plan, and deployment plan documents
Solution for Migrating from Oracle on UNIX to SQL Server on Windows
Microsoft
Technical and process guidance to migrate Oracle databases and associated database applications to Windows and SQL Server 2000
Data Access Strategies Using ADO.NET and SQL
John Papa
When your goal is a scalable and efficient enterprise solution, you need to develop an efficient data-access strategy. You can't just do some testing on your production machines and rely on the results. While an application can exhibit excellent response times when serving a few users, performance problems can surface when the load increases. So when I am asked to perform architectural and code reviews, there are several coding patterns I look for. In this column, I will share some of them as they relate to data access using ADO.NET. I'll discuss efficient ways to implement ADO.NET database connections to take advantage of connection pools and will present tips on how to use various ADO.NET objects to open and close connections and to manage connection state for you. I'll discuss connections and the DataAdapter and DataReader and move on to stored procedures, parameterized queries, and SQL injection attacks
What is TSQLMacro?
adam machanic
TSQLMacro is a free development tool for SQL Server programmers. It provides C/C++-style macro-expansion capabilities for TSQL stored procedures, user-defined functions (UDFs), triggers, and views. This provides great flexibility in a variety of development situations that are not easy to handle without macros. Some examples include multi-environment challenges, embedded debugging and production rollout challenges, and code reuse challenges. Please refer to the TSQLMacro Introduction for a complete analysis of these issues and how TSQLMacro helps to solve them
Another Reason Not to Develop with Admin Privileges: DBO in SQL Server
Don Kiely's
Today I stumbled across one of the very specific reasons why developing code as a member of the local or domain Administrators group is a bad idea
Yet Another Petition to Microsoft: XQuery in the .NET Framework
Don Kiely's
Petitions asking Microsoft to do or not do something are all the rage these days, starting--in recent memory--with the infamous VB6 and VBA petition
Replacing an MSMQ queue with a SQL Server 2005 queue
Write Ahead Blog
Today, we did a short presentation on Service Broker for a customer who is interested in using the Service Broker technology. Their current application uses a large backend SQL Server database that is accessed from several mid-tier servers. In one of their scenarios, requests are queued to an MSMQ message queue and an NT service periodically reads these requests from the queue and performs some database operations on the backend. Switching from MSMQ to SQL Server 2005 Service Broker would give them the immediate advantages of persisting messages along with the data in the database and thus provide a single backup/restore solution. And they also get the exactly once in-order semantics of the conversations, internal activation to get rid of the polling NT service, etc.
Identifying Long Running SQL Server Agent Jobs
Gregory A. Larsen
In my article last month, I wrote about different methods you could use to identify the state of a SQL Server Agent job. One of the methods I discussed was using the undocumented extended stored procedure (XP) "xp_sqlagent_enum_jobs." In this article, I will show you how to use the "xp_sqlagent_enum_jobs" XP to help identify long running jobs
Give me parameterized SQL, or give me death
Coding Horror
I have fairly strong feelings when it comes to the stored procedures versus dynamic SQL argument, but one thing is clear: you should never, ever use concatenated SQL strings in your applications. Give me parameterized SQL, or give me death. There are two good reasons you should never do this
The bloated world of Managed Code
Coding Horror
Mark Russinovich recently posted a blog entry bemoaning the bloated footprint of managed .NET apps compared to their unmanaged equivalents. He starts by comparing a trivial managed implemention of Notepad to the one that ships with Windows
Because Information is Beautiful
Coding Horror
The Edward Tufte books are well known classics now, but I distinctly remember my first encounter with The Visual Display of Quantitative Information in 1995. At the time I was working for a market research company in Denver. I noticed the book sitting on the president's desk while I was in his office doing some typical small business IT stuff. I had never heard of it, and I was intrigued. I started casually paging through it-- and I was absolutely enthralled. I couldn't put it down. After Karl arrived, I told him how amazing the book was; I had to have my own copy. He expressed some surprise that he had no luck getting his market analysts to look at the book, but his crazy IT guy just happened to see it in passing and treated it like some new kind of religion
SQL CLR User Defined Types and Operator Overloading
Hans VB's WebLog
Just talked with Shaun Hayward. Someone who onviously very passionate about coding. A (long) while ago he asked me for my opinion on Operator Overloading for User Defined types in SQL Server 2005
SQL 2005: .NET vs. T-SQL, the meaningless performance test
Hans VB's WebLog
When talking about SQL Server 2005 - CLR integration I consistently got the question: 'Which code runs faster? T-SQL or IL'. The answer is obviously: 'It depends'
Visiting SQL - Part 1
Yves Reynhout's Blog
Apparently, after some months of intensive work, I'm dieing to share (and reflect on) some of the things I've worked on
Visiting SQL - Part 2
Yves Reynhout's Blog
In part 1 I explained how I got a "typed model" of a database schema for building SQL in code. By itself I found this useful, but I still hated the string concatenation involved (not to speak of the scattering of all this string manipulation). So I came up with the idea to build a hand-made object model for all SQL query related objects. This model would allow me to build SQL statements in a typed and database-agnostic fashion, integrating nicely with my typed database schema model. To give you a rough idea I included some schematics
MDX Solutions Second Edition
Chris Webb's BI Blog
I've been quite busy recently working on the second edition of MDX Solutions, along with the legendary George Spofford and two others: Siva Harinath and Francesco Civardi. I've only got a few chapters to update and one to write from scratch but it's already dominating my evenings - yesterday, for example, I spent hours trying to get a complex CREATE CUBE statement working and found that the problem was a missing underscore in a column name. Argh! No pain, no gain I suppose
Other Row Paging Technique Using T-SQL
bluechip_asia
Simple Row Paging Using T-SQL at Stored Procedure
Tips for Optimizing Data Manipulation Language
Kalpesh Thaker
SQL Server does a wonderful job of choosing the optimal query path for your TSQL, but there are a few things that you should be considering when you write code. Kalpesh Thaker brings us another optmization article that gives some of the things he has learned over the years about writing more efficient and better performing TSQL
Customer Intelligence: Driving Toward Action: The Strategy Stage
Larry Goldman
In December, we talked about the life cycle that organizations go through to transform data into action: data, information, insight, strategy and action. (See the December issue of DM Review, p. 46 or www.dmreview.com/article_sub.cfm?articleId=1014621.)
Designing a Technology Infrastructure for Analytics
Sudip Chakraborty
This article provides an overview of the four major technology selection decisions that need to be made by technology managers and directors who are responsible for designing a technology infrastructure that will support the analytical processing needs of the business.
Information Strategy: Building a Culture of Sarbanes-Oxley Sustainment
Jane Griffin
In last month's column, I talked about building a program of Sarbanes-Oxley (SOX) sustainment. One of the key attributes of the sustainment program is financial information transparency that reveals the level of information quality throughout the organization. Establishing the governance processes that define data standards, audits and controls are critical steps to ensure a high level of information quality throughout the organization
Database Integration Better Late than Never
Peter Coffee
Microsoft Corp.'s planned integration of database-oriented programming tools pays belated homage, one might say, to computer scientist Niklaus Wirth's famous assertion that "Algorithms plus data structures equal programs."
Download Reporting Services Scripter
Jasper Smith
Reporting Services Scripter is a .NET Windows Forms application that enables scripting of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules) and server side report parameters
Security Tools for Development
Sergey Simakov blog
Last friday at Microsoft Moscow office Ivan Medvedev (from SWI team) made a presentation about Security Tools for Software Development. He mentioned new Threat Modeling tool, AppVerifier, PreFast, FxCop, and new Whidbey compiler switches
SQL Server Encryption -- Symmetric Keys
SQL_Bee's WebLog
SQL Server 2005 supports symmetric encryption of data with these new built-in functions
T-SQL Enhancements - INTERSECT and EXCEPT
The SQL Doctor is In
INTERSECT and EXCEPT are not the most important changes in T-SQL for 2005, but when you need them they will save you tons of time. If you have used UNION in the past, you will recognize how these funtions work. They take two like sets as input and combines them to make a single set. All of the columns in the sets must be equal in type and name
SQL Server I/O: Creating Hierarchies
Buck Woody
The concepts of relational database theory and hierarchical document formats seem miles apart. But in reality, it's all just about looking at data in multiple ways
Whidbey's Security Off Model
.Net Security Blog
Although the v1.0 and v1.1 versions of CasPol provided a switch to disable the CLR's security system, running without CAS enforcement on was never a scenario that we encouraged for obvious reasons. The choice to disable security was a system wide switch that affected any managed application on any version of the runtime, and made running managed code incredibly unsafe
Trusting Applications with their Strong Name
.Net Security Blog
Last time I talked about reasons that you might want to strongly name your application's entry point. The most obvious reason is so that you can setup your security policy to increase the level of trust given to that assembly by the default policy. You would do this by signing the entry point exe, and other assemblies that make up the application, and then using the StrongNameMembershipCondition to create a code group that provided the trust level you wanted for the application
When to Strongly Name an Application Entry Point
.Net Security Blog
Junfeng wonders why you might want to strongly name an exe. Sometimes strong naming your exe can be a very useful, but like any feature it's not necessarily always the tool you need for the job. For instance, when running a simple managed .exe off of your local machine, there's probably not much of a reason to give it a strong name. Junfeng points out that strongly named assemblies are often used when there is a need to share assemblies between applications, by allowing the strongly named assembly to live in the GAC. And it is true that adding an .exe to the GAC isn't a very common scenario; however this is only one of the reasons to provide an assembly with a strong name. Since strong names have many other uses, there are several scenarios under which you would want to strongly name your application's entry point. Some of these are
Getting to Know the SQL Server Manager Tools
Bill Ramos
This is the first of seven articles that I'll be presenting as part of the upcoming SQL Server Web cast week on December 6th from 1 to 2pm PST. Check out http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263410&culture=en-us for details. This web cast will give attendees an overview of the new Management Tools for the SQL Server 2005 release. I'll be demonstrating the features using the upcoming November CTP release of the tools and I'll point out some of the differences between this current release and Beta 2
Configuration – The Experience
Bill Ramos
Configuration is all about getting SQL Server services installed and running in your environment. The setup program is the first point of contact for configuring SQL Server, but before going to deep, I highly recommend looking over the books on-line topic: “Before You Install SQL Server 2005”. In this topic, you will find links having to do with: security considerations; where your database files should be located; what type of Windows accounts you should use for the various services; and working with failover clustering
Help Improve SQL Server 2005 Setup, take our survey
nikop's WebLog
The Yukon setup team is looking for feedback related to your setup and installation experience with SQL Server 2005. We've setup a survey that asks for a few demographics as well as your experience and any issues with the setup of the Betas or CTPs. We are looking for your impressions, so if possible, do an install and take the survey soon afterwards. Save your errors, jot some notes, etc., while installing SQL Server 2005
Error "not enough space to extract this package" seen when trying to install the SQL2005 download packages
nikop's WebLog
There is a possiblility that after you've downloaded the SQL 2005 installation package to your local machine, during extraction of the files to your local drive you will get an error that looks like the following screenshot below. If you get this, it usually means that even though you might have told the extraction utility to put your files onto a different partition, it uses the system environment variable that points to your TEMP space first, and then moves them to your selected folder. Try performing these steps
Setup has detected incompatible components...
nikop's WebLog
Did you just download and tried installing the Feb CTP build? Did setup complain that it found existing incompatible components? Along with your dowload package is a tool on the CD that will scan your machine and allow you to remove previous versions of SQL Server components and the dotnet framework. You can run this tool or you can try removing the previous versions of SQL 2005 or VS 2005 from Add/Remove programs. The tool is called "Build Uninstall Wizard" and here are some steps to running it
Using Exotic Joins in SQL Part 1
Chris Cubley
When most developers think of joins, they think of a.SomethingID = b.SomethingID. This type of join, the equijoin, is vitally important to SQL programming; however, it only scratches the surface of the power of the SQL join. This is the first in a series of articles that will look at several different types of exotic joins in SQL. This article will focus on using the BETWEEN operator in joins when dealing with range-based data
Data on the Outside vs. Data on the Inside
Pat Helland
An Examination of the Impact of Service Oriented Architectures on Data. Pat Helland explores Service Oriented Architecture, and the differences between data inside and data outside the service boundary. Additionally, he examines the strengths and weaknesses of objects, SQL, and XML as different representations of data, and compares and contrasts these models. (26 printed pages)
DAC - Dedicated Admin Connection in SQL Server 2005
ExtremeExperts.com
In mids of loads of new features introduced in SQL Server 2005, this is one feature that interests the DBA's the most. If you work with large number of servers and large number of databases you might want to have immense control the way you monitor and maintain SQL Server instances. In the past I've seen times when people say the server has frozen and I am not able to connect. To eliminate such problems "Welcome SQL Server 2005 - DAC"
What's new in MDX in CTP15
Mosha Pasumansky
In the past our team was critisized in the newsgroups, that whenever we release new CTP, the release notes for Analysis Services are pretty much empty. We will try to make it better, but in the meantime, I decided to write what I thought was interesting or important in the blog. Although there were many interesting and/or important changes in the last 2 months since April CTP14, I decided only to focus on MDX related ones
Diagrams are Back!
Hitachi Consulting
One of the most (ahem) active threads in the SQL 2005 beta newsgroups was discussion around the loss of the trusty database diagramming tool, formerly found in SQL Enterprise Manager. I am happy to report that as of the April Community Technical Preview build, diagrams are back in the SQL Management Studio. Even though Microsoft has stated that the primary emphasis for diagramming will be with both Whidbey and with Visio Enterprise Architect, it’s nice to have this often used functionality returned to the tool-set that many of us use day to day to create or understand Star or Snowflake Schemas. While there are many tools that offer more extensive diagramming feature sets, the integration and ubiquity of the Database Diagramming Tool makes it invaluable. Personally, I use it to visually understand the layout of Star and Snowflake schemas that I am becoming familiar with. I also use it to create prototype relational databases for proofs of concepts
SQL Server: Tuning your SMO Application for great performance -- PART 2
Michiel Wories' WebLog
In the previous post I provided you with a fundamental tool to minimize the amount of SQL statements emitted and therefore limit expensive network round-trips (and SQL statements) using the Server.SetDefaultInitFields() method. In this post I will provide you with some more detail about this method, alongside with a recommendation about its use
Sql Server: Everything you always wanted to know about SMO Connections
Michiel Wories' WebLog
There are several ways of controlling connections in SMO -- you can control pretty much any aspect of a SMO connection
Be Aware: Using AWE, locked pages in memory, on 64 bit
Slava Oks's WebLog
We have already talked about Windows AWE mechanism on 32 bit and how SQL Server utilizes it. Today I would like to go over AWE & related mechanism on 64 bit platforms
Microsoft licensed Mvp.Xml library
Oleg Tkachenko's Blog
On behalf of the Mvp.Xml project team our one and the only lawyer - XML MVP Daniel Cazzulino aka kzu has signed a license for Microsoft to use and distribute the Mvp.Xml library. That effectively means Microsoft can (and actually wants to) use and distribute XInclude.NET and the rest Mvp.Xml goodies in their products. Wow, I'm glad XML MVPs could come up with something so valuable than Microsoft decided to license it
XQuery in .NET 2.0 Petition - too late, guys!
Oleg Tkachenko's Blog
Almost 6 months after it's been announced that Microsoft won't ship XQuery implementation in the .NET 2.0, StylusStudio (maker of the namesake XML IDE) decided to run an online petition "XQuery for all" to urge Microsoft change the mind. Well, as a marketing action it's ok, but the petition itself is hopeless being ridiculously late. Asking Microsoft to include an implementation for a Working Draft technology, which they decided not to ship at least half a year ago and after Beta2 is out is a weird idea. Sorry guys, I won't sign it. Back in October 2004 - I would sign it for sure, but not now
Clustered Servers
KAREN'S SQL BLOG
Microsoft's Elden Christensen gave an excellent TechNet Webcast on Friday 4/29/05 on clustering, and I can highly recommend it as a Webcast replay to anyone who's interested in deploying clustered servers. Elden provided a wealth of information and insight not just about improvements in Windows Server 2003's SP1 release (a new quorum algorithm that should "eliminate all quorum corruption issues" and Vrfydsk as a replacement for CHKDSK, for example), but also answered scores of questions in a 20-30 minute long Q&A session following his slide/demo presentation
The CLR in SQL Server 2005
Steve Jones
One of the big changes in SQL Server 2005 is the integration of the CLR into the relational engine itself. This is probably the biggest reason for the delays in completing the product and it is a controversial decision. Steve Jones spends a few minutes looking at the pros and cons of having the CLR integrated and possible implications for DBAs
Monitor the CPU Usage of Your SQL Servers
Muthusamy Anantha Kumar
This article demonstrates the CPU Utilization of any SQL Server box by taking advantage of WMI and VBScript in order to find under utilized Servers. It is important to know which Servers use more CPU and during what period of time, to help the SQL Server database administrator to load balance
Generate Script for objects and dependent objects
Muthusamy Anantha Kumar
SQL Server database administrators often deploy object changes, such as table, procedure, trigger etc., on the production system. Usually when they do, they check for dependencies, just to make sure that those changes are not going to break the system
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
William Pearson
In this article, we will examine the TopCount() function, whose highest value lies in its capability to enable us to isolate best performers from among hundreds or thousands of fellow members. This ranking capability is critical in data analysis and decision support scenarios: In many business situations, we seek to report upon "best performers" for various reasons. TopCount() facilitates our doing so, allowing us to sort on a numeric value expression that we can provide. We can tell the function how many "top" members we wish to retrieve (say, the "top ten," or the "top twenty"), for a "custom-fit" approach, that matches our needs precisely
Can we move data from SQL Server to an Oracle server?
Brian Peasland
Of course you can. How you perform this depends on your requirements. Since you have SQL Server, you can use Data Transformation Services (DTS) to move data from SQL Server to Oracle connecting to the Oracle database through ODBC drivers. If this move will be required on a regular basis, then you can save your DTS routine as a DTS package and optionally schedule the DTS package to run regularly. DTS has tons of features so it can handle most of your needs
Kimberly Tripp Talks About SQL Server 2005 on DotNetRocks
Paul Ballard
In a recent episode of DotNetRocks, Carl and Richard talk to SQL Server expert Kimberly L. Tripp about the new features of SQL Server 2005 including how to use the CLR and XML features properly, new availability features, and changes to the T-SQL language
Online Petition to Save XQuery in .NET 2.0
Paul Ballard
Nearly a hundred Microsoft MVP's and many more Microsoft and XML developers have signed a petition urging Microsoft to support XQuery on the Microsoft .NET Framework 2.0 (Whidbey)

[В начало]

ФОРУМ SQL.RU

Самые популярные темы недели

Кто на чем пишет клиентов под SQL Server?
БД: логическая связь таблиц. Какие плюсы?
отправка сообщения с сервера MSSQL по всем ADO коннектам текущей БД?
данные из Paradox в SQL Server
Знает ли процедура собственное имя?
Deadlock между Select и Insert
генерация значения ключевого поля до insert
Прошу голосовать за SQL.RU в конкурсе Интернить 2005
Опять проблема с репликацией!
Запуск из триггера консольного приложения, которое делает запрос к БД
Предача массива в функцию (процедуру)
Kак лучше сделать лог для процедуры
Как работать с типом данных table?
MSDE / Agent
Что такое транзакция?
COLUMNS_UPDATED глюк или что???
Как быстрее сделать из нормальной SQL БД - Демо Версию
Использовать архиватор в MSSQL
DTS -имя файл как глобальная переменная
MSSQL: ODBC Connection String: Implicit Transactions

[В начало]

Вопросы остались без ответа

Ошибка Invalid Pointer
LoadXMLFromFile - не работает
Проблема при запуске dts package через xp_cmdshell
FAQ Сложение символьных полей в запросе
ReportingServer
Читайте: SQL Server 2005 Books Online
Процедура SQL-->MDB глючит
Timeout Expired Analysis Services
Непонятка...
Самый быстрый запрос?
зависает ЕМ при обращении к свойствам SQL server agent
Проблема с CDO - MAPI_E_LOGON_FAILED(80040111)
Скрытые булевы операторы для CONTAINS
Не устанавливается SQL Server 2005 Express Edition CTP

[В начало]