Por Dentro Sql

News from SQL Server 2005: SP3 arrived

2008-12-16T20:28:00Z

Read all about SQL 2005 sp3: http://msdn.microsoft.com/en-us/sqlserver/bb895958.aspx

... and the fixlist: http://support.microsoft.com/?id=955706

Happy patching !

Sql Server 2008 Feature Pack

2008-11-29T11:01:00Z

Some usefull extra tools/utilities/providers ...the Feature Pack for Sql Server 2008, for the october 2008 version: click here and Enjoy!

Sql Server 2008 - Cumulative Update Package 1 available

2008-09-23T20:16:00Z

You read all about it in Wesley's blog post:

http://blogs.msdn.com/wesleyb/archive/2008/09/23/cumulative-update-package-1-for-sql-server-2008.aspx

Microsoft Virtualization Roadshow

2008-09-21T16:44:00Z

Is an interesting idea: should we virtualize Sql Server or not? Or better in which circomstances should we virtualize Sql Server ... ? Definitely a User Group evening worth !!

In the mean time: learn more on virtualization on the Microsoft Virtualization Roadshow: www.getvirtualnow.be

WSSRA - Data Services link

2008-09-21T15:36:00Z

The Windows Server System Reference Architecture is, I could not write it better than just a copy paste from the website itself:

WSSRA is a detailed reference architecture, tested and proven in labs, that yields valuable implementation guidance for meeting the requirements of an enterprise. Customers can use this guidance to build highly available, secure, scalable, manageable, and reliable enterprise infrastructure

Because I always forget this link for blue prints: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/dataservices/igdsbp.mspx

For the complete reference http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/default.mspx

Microsoft Office Visio 2007 Professional SQL Server Add-in available.

2008-06-14T08:22:00Z

I did not play with the Sql Server add-in for Visio 2007 yet. Curious about your reaction for those you tried it out. You can download the add-in here.

Sql Server 2005 SP2 and the changed object_name function

2007-11-16T07:33:00Z

This enhancement to the object_name function is so wonderfull for the T-Sql guru's under us:

The object_name() function now accepts 2 arguments: the object_id as before and as a second argument the DATABASE_ID. Isn't this wonderfull? We can finally leave the current database context, without to play around with dynamic sql. Simply great!

IT-Forum Day 4

2007-11-16T07:23:00Z

It was a wonderfull day, full of Kimberly Trip and Paul Randal sessions. I will make it short today as they put all the slides and scripts on there website SQLskills.

So only some thoughts for today:

- I always thought that the default fill factor at the server level was 90%. It turned out that it is a 100%. I have to review some things ...

- What new for database mirroring in Sql Server 2008? "Only" 3 things:

* automatically repair of page errors. It is, if the principle gets a bad page, it is corrected with the page information of the mirror and vice versa.

* Log stream compression

* More performance counters available.

That is it (almost) for today, there is one tiny little thing which is so amazing that it urns a separate blog post: the change in function object_name

IT-Forum, Day 3

2007-11-15T07:22:00Z

Another interesting day, full information. Once I return and I find more time, I will work out some topics. For the moment I'll provide you today with some 2-sentence topics:

- Sql Server 2005: on logging in to Sql Server 2005 and you are not using any encryption, Sql Server will encrypt your login & password automatically. I forgot to ask if this is only with the native driver ...

- Sql Server 2005: Do you remember the application roles, with which you can make a difference in user-rights between different applications (in fact different connections)? Well, it is now recommended to use login-less users instead of these application roles. I will search this out , because I forgot why - too much information the last days :-).

- Sql Server 2008 will contain a SQL Provider for Powershell.

- Sql Server 2008: The boundaries for the new date-datatype are 0001-01-01 till 9999-01-01, the time-datatype goes to 100 ns. However you can define less precision if you want (less bytes to store).

- Sql Server 2008: the insert statement can now contain multiple rows, e.g.

insert Table1 values (1, 2, 3) , (10, 2, 30) , (100, 300, 400)

- Sql Server 2008: object dependencies do not lie anylonger (e.g. after a drop and recreate of a procedure which is allready depending on another). Although make sure that you always use the 2-part naming style, it is schema.objectname.

- Sql Server 2008: user defined data-types can now be as large as 2 Gb.

- Sql Server 2008 CTP5 (november 2007) will contain backup-compression. Backup-compression can be defined at the database level as well as in the backup-statement itself.

- Sql Server 2008 CTP6 (february 2008) will contain data-compression for DWH fact tables. Not for blob data. For blob data, it is recommended to use the new filestream datatype. Rule of thumb I heard here: put never files > 100 Mb in your database. Personally, I am not pro putting files in your database anyway, although it might have some advantages (transactional consistency). This last one is included in the filestream datatype, so happy again.

I have to leave, the 1st session of today will start.

IT-Forum day 2

2007-11-14T07:14:00Z

Today, some nice-to-know's

- Sql Server 2005 SP3 (not known yet when it will come out) will probably contain an instruction to do an active checksum verification on all pages of a database

- Sql Server 2008 CTP (November):

* What concerns the features about Transparant Data Encryption in Sql Server 2008:

Tempdb will (implicitly) be completely encrypted. In later versions only those pages which will be encrypted in the destination database, will be encrypted in tempdb. So the tempdb-part of this feature will be implemented in 2 steps.

* About the framework (I forgot the name) to collect resource usage. I saw some usefull reports with history information about

- disk usage

- query statistics

- server activity: usage of cpu, memory, disk i/o and network

Not in the CTP yet, but in the RTM, MS will (try to) deliver a connector for monitoring Sql Server 2005 too.

- Creation of a plan guide will be much simplier in Sql Server 2008 than in 2005. Whatch for the procedure with the name "sp_create_plan_guide_from_cache". It will be available in the CTP foreseen for February 2008

- Just as with the resouce governor for I/O (mentioned in yesterdays post), bug-reporting and suggestions for new features can always be sent to Microsoft at connect.microsoft.com

Barcelona 1 day of IT

2007-11-13T07:16:00Z

As this is a technical blog I will not give a report of this night, so after only 4 hours in bed:

What do I remember from yesterday?

First half of the day, community work, nice conversations with other peers. And a lot of Belgians, we were 6 or 7!

Last half of the day was more sql related. I missed the keynote so I went to Francois Ajenstat's session about what's new in Sql Server 2008. this is part of what 2008 will bring us for the data-engine:

- This year (november) another CTP

- February 2008 a last CTP

- 2008 Q2 the RTM (with the same price as todays 2005)

- Transparant data encryption (so without your application to be rewritten - he said)

- Log file compression in relation to database mirroring (I am not sure what about logshipping - I will check this out)

- Possibility to apply rules to sql server through policies (naming conventions, configuration settings, ...)

- Resource governor to limit % cpu and memory usage for a certain workload

--> will be included in november CTP.

--> very important REMARK: if we want IO resource governor also included in Sql 2008, we have to argue to the product-team !

- Concept of identity (OO vs relational ?? - I will check this out)

- New (interesting) datatype: filestream to be viewed as an intelligent pointer to your files on the filesystem

- New long-expected split of date and time + a new data-type datetime2 which stores 7 decimal places iso 3 milli-seconds

- A spacial data-type to store geographic data. This one will not be an option which is to be paid as in Oracle (it seems) but available in-the-box from the Express Edition on.

- Full-text makes part of the sql engine and is not longer a separate service (I can not overview the consequence of this yet)

- Wauw, intellisense is there !!

- Performance monitor: We now the dmv's from Sql 2005. In 2008 this information is stored for later analysis.

- and a lot more, ...

So, dear reader, a lot to discover during this week!

o Pedro

Management Console and db_ddladmin

2007-10-20T16:45:16Z

We all know that the Management Console has sometimes its own logic ...

Well, if you are working with database roles and want to give a user only the db_DDLAdmin role, this user will not be able to change the structure of a table using the Management Console.

Glad to say, that with T-Sql you will be able :-)

This behaviour is remarked in at least Sql Server 2005 SP1 and SP2 (9.0.3054)

Look how you can REPRODUCE this behaviour (in your test environment of course):

1. Execute the following batch:

USE Master
CREATE DATABASE Test
CREATE LOGIN [test] WITH PASSWORD=N'T1st', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
db_
USE Test
CREATE USER [test] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_AddRoleMember 'db_DDLAdmin', 'Test'

2. Open the Object Explorer with the login "Test", expanding database "Test"

3. Create a new table

	you get the message

	Press <OK> and continue

4. Define some fields and save

	you get the warning

	Press <Yes> and the table is saved.

5. Select the table in the Object Explorer and R*, choose "Design"

	this message we know already

	Press <OK> and ... up the next messagebox

	Press <OK> and ... indeed you are not able to change anything in this table because the GUI is read-only.

SOLUTION

You can solve this by granting the permission "view definition" for this user

USE Test
EXEC sp_AddRoleMember 'db_DDLAdmin', 'Test'

When this user tries to change the design of the table, (s)he will still get the "Validation Warnings" message (see above, press <Yes>) and the new structure will be saved.

Do realise that you give an extra permission, but I suppose that in most situations, if you are allowed to create, alter and delete any object, you are also allowed to view its definition.

Tip: search ones in BOL after "VIEW DEFINITION Permission" and you will discover some other nice roles, e.g. the server role "VIEW ANY DATABASE" permission, which is granted to the public role by default (as consequence, by default, every user that connects to a Sql 2005 can see all the databases on that instance).

3-day workshop about performance on Sql Server 2005

2007-04-25T20:41:00Z

The company Consuran is organizing a 3-day workshop about performance on Sql Server 2005. The workshop is given by R. Meyyappan. He is really good in the performance stuff. When I followed the workshop some time ago, I got more insight into the internals of Sql Server 2005. For this reason I want to share the fact that he is comming back in Brussels from June 18 till June 20, 2007. Take a look at the agenda.

Sql Server 2005 Oracle provider on Itamnium (true 64 bit)

2007-02-19T22:11:00Z

It seems like a déjà vue, migrating Oracle data to Sql Server.

This time, some Oracle 10g tables (running on an Itanium server) to be migrated to Sql Server 2005 (temporary running on the same Itanium).

I thought, piece of cake, creating a linked server using "Oracle Provider for OLE DB" (the only provider currently available on the system) with the following statement:

EXEC master.dbo.sp_addlinkedserver @server = N'RX3600', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Ora2Sql'

followed by creation of a credential to login in Oracle.

Remembering a previous post, we have to set the option "Allow inprocess" on the provider OraOLEDEB.Oracle.

And to finalize the statement: SELECT * INTO destinationdb.schema.tablename FROM RX3600..schema.tablename

The result is that all the rows are transfered (no indexes, nor foreign keys are needed). After analysing the content however, data is correctly copied ... for character-based data-types, unfortunately not for the Oracle decimal data(-type). Once arrived in Sql Server, the decimal columns where filled with 0 (zero) ! :-(

According to "Installation of Visual Studio 2005 on the Intel Itanium (IA64) is not supported", I did not find the Visual Studio Manager to create a full SSIS package. Therefor I used "\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe".

When running this wizard, I selected:

- as origin data source: ".Net Framework Data Provider for Oracle"

- as "query": "SELECT * FROM tablename"

- changed the definition of the decimal datatype with correct precision and scale.

I got the correct data.

For your info, the driver was allready installed. If you would need it, you find the provider here, probably the 32-bit version.

CU, pds

Suspect database with torn page

2006-12-15T13:04:00Z

Today, I was "lucky" to deal with a suspect database on a Sql Server 2000 SP4. Always a little bit targeting to get it solved, isn't it?

This time, the database would not recover because of a torn-page detection in the data-file. After having taken an off-line backup (on-line backup on a suspect database does not work), I tried a lot of statement(-combinations):
starting with sp_resetstatus, dbcc checkdb() including the one with repair_allow_data_loss, attachdb (with and with-out ldf-file), update sysdatabases ...
Pushing my luck with dbcc rebuild_log, which rebuilds the ldf-file, hoping I could pass the recovery phase and copy data to another database. However, no luck ...
Finally, I got the golden tip from Microsoft (I have to admit I pushed the guy to the ultimate :-) because I would not hang in the telephone without another atempt to get the data back, so, thank you Lars!) : emergency mode for a database. You should not run this on your regular databases! Take a look in BOL under topic "sysdatabases", column "status".
With the database in emergency mode, I was able to get the data from 156 of the 158 tables save in another database. For the 2 remaining tables most data could be saved, except for the few rows being on the torn-pages (I gues 2 pages, 1 on a datapage, another on an page with indexes).

Lucky client, happy me !