Live Event on Windows / SQL Azure!

Do you like “walking on clouds”? Heard of Azure but not quite sure what it is and what its capabilities are? Join us at CDNUG on Wednesday May 22 2013 and learn more about Windows Azure and its services offered for the developer!

Code on premises, deploy on the cloud and many more are the demos that you are going to see live!

The event will take place at the local Microsoft Cyprus Office but it will be also streamed live!

So if it happens and you are in Cyprus during these days, feel free to pass by the local Microsoft Office and attend the event! If not, you can always watch it live online via Live Meeting!

Event Details
Topic 1: Windows Azure for Developers
Speaker: Valentinos Georgiades

Topic 2: Windows Azure SQL Database Overview
Speaker: Artemakis Artemiou (yep, that's me :)

Date: Wednesday, May 22nd, 2013
Time: 18:00 – 20:00 (Local CY Time - Time Zone Converter)
Duration: 2 Hours
Place: Microsoft Cyprus Office

Registration
Please register here: http://goo.gl/5wAOY

Live Meeting Link and Details
Attendee URL: https://www.livemeeting.com/cc/mvp/join?id=WN4DCQ&role=attend
Meeting ID: WN4DCQ
Subject: CDNUG Event - May 22 2013 - Windows Azure / SQL Database
Start Time: Wednesday, May 22, 2013 6:00 PM EEST
End Time: Wednesday, May 22, 2013 8:00 PM EEST


See you at the event, physically or virtually! :)

Read more on this article...

Mastering the Data World: Extracting Knowledge from a DBMS

We live in a Data World. I cannot tell you whether this is a good or a bad thing but I can tell you for sure that this is a fact.

Everything works based on data. In the enterprise, this data needs to be stored into powerful, scalable and robust data platforms (Relational Database Management Systems - RDBMSs) which allow the future processing and transformation of data into knowledge.

Transforming data into knowledge, in many cases, is not an easy task. It can involve complex queries, nested loops, aggregations, etc. thus requiring large design efforts and considerable amounts of execution time.

We many times find ourselves in the process of thinking of a complex query, struggling to satisfy a complex business requirement, always trying to find a way to “instruct” our data platform how to extract the required data/knowledge. Such processes can take hours, if not days.

In such cases, our way of thinking is directly focused on the end result thus trying to “capture” the solution in its whole. This is a process-driven approach towards the transformation of large amounts of raw data into knowledge.

The more complex the business requirement is the more complex and time-consuming this process-driven approach will be. It might sometimes end up to dead ends as well.

There are many times where I need to solve a quite complex business requirement when it comes to the transformation of data stored into SQL Server into knowledge.

It is then when the following thoughts pass through my mind:

abstraction...
principles...
data sets…
solve smaller issues...decomposition...
combine them...
solve the problem

The above thoughts are not random. They are actually based on well-established principles of Computer Science that are widely used in software development.

Abstraction is the process by which when you have a complex problem to solve in programming, you do not focus on the implementation details. You instead focus on the semantics and you build different abstraction layers.

Decomposition is the process by which you break a complex problem into smaller parts, you then deal with each one of those parts and in the end you combine them in order to form the solution to the original problem.

The question is why not applying Abstraction and Decomposition in the Data World or at least using them more? I am not saying to follow a strict data-driven programming paradigm rather than a method which takes into consideration the available data too and how it is organized in the DBMS. The suggested approach is described in the following diagram:

Diagram 1: Suggested Workflow for Transforming Data Into Complex Knowledge

As illustrated in the above diagram, the suggested workflow features the following steps:

1. Receive and analyze the business requirement (use abstraction).
2. Break the business requirement into smaller parts (use decomposition).
3. For each part:
• Write the SQL code
• Execute it
• Feed the data to the next part (you may use data import/export, integration services, etc.)
4. After the execution of all parts is completed you end up to the final data set that contains the result to the business requirement.

I am quite sure that in many cases we use the above approach without really identifying it or giving it a label.

However, when you are given a complex business requirement, especially in enterprise environments, you cannot just start writing code! I know, I like it too :) but you need to have a plan or else you might end up coding, testing, and re-coding SQL code all over without easily producing the final result!

So whenever you have a complex data challenge to face, you can break it into parts and work on each part cumulatively by developing it, executing it and feeding its results to the next part. This is the road to extracting complex knowledge out of your DBMS, this is the road to mastering the Data World!

Read more on this article...

Congratulations 2013 Microsoft MVP!‏

April 1st: Waiting for the magic email. For the email which says that my MVP status is renewed for another year and so the journey continues. A journey into the amazing world of the Microsoft MVP Award Program, connected directly to the source of one of the world's greatest technologies: SQL Server!

In the afternoon around 5pm a new email was added to my inbox saying:
-------------------------------------------------------------------------------------------------

Dear Artemakis Artemiou,

Congratulations! We are pleased to present you with the 2013 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.
---------------------------------------------------------------------------------------------------

Now if the above email isn't amazing then I don't know what is! :)

I feel very honored and happy receiving the MVP Award for one more year. I also feel very proud with being an active member of the SQL Server community thus exchanging knowledge with fellow community members mainly passionate with SQL Server!

My promise is that I will continue doing my best and exchange with you as much as technical knowledge possible, via all the available channels, thus contributing to an even more powerful SQL Server community.

I would like to thank Microsoft for strongly and continuously supporting the technical communities around the globe!

I would also like to say a huge "thank you" to the SQL Server Product Groups for developing such great technologies as well as everyone involved in the MVP Award Program!

Read more on this article...

Screencast: Working with Snippets in SQL Server 2012

Welcome to another screencast of the "SQL Server 2012 Screencast Series"!

In this short screencast we talk about code snippets in SQL Server 2012 Management Studio.

Code Snippets are being used for many years now by developers in Integrated Development Environments (IDEs) like Microsoft Visual Studio. Their benefits vary. Some of them are:

• Code Reuse
• Easier and Faster Programming
• A method of defining coding standards

SQL Server 2012 provides the functionality of Code Snippets similarly as offered in Visual Studio thus making writing code in SSMS 2012 an even richer experience.

In the screencast, you can see how you can easily create snippets using my freeware tool "Snippets Generator for SQL Server 2012" and how to import it and use it in SSMS 2012.

You can watch the screencast below or at the "SQL Server and .NET TV":

Read more on this article...

Encrypting SQL Server Databases

In corporate environments there is always the requirement/policy of data protection. Data is the most valuable asset in every organization, after its human resources of course, as it is with data where the organization’s business processes run and produce results.

SQL Server is one of the leading data platforms worldwide and as such, from SQL Server 2008 (Enterprise Edition) and later, all the necessary mechanisms for ensuring data protection are available within the Database Engine and can easily be used.

SQL Server 2008 introduced Transparent Data Encryption (TDE); a mechanism allowing the DBA to easily encrypt databases without affecting their operation as it allows full transparency to the database users and applications. Based on TDE, the DBA encrypts the database using a master key and a certificate. The user accesses the database transparently as the encryption is automatically maintained on the Database Engine-level and in the case of a potential data theft, i.e. someone illegally copies the database files (data and log files), when he tries to access the database it will not be allowed because the database is encrypted. You can find a simple example on how you can use Transparent Data Encryption in one of my older posts here.


As mentioned above, Transparent Data Encryption is available in SQL Server 2008 or later. However, if you would like to achieve database encryption in earlier versions of SQL Server, you can do it by using a special feature of Windows on the file system-level, that is Encrypting File System (EFS) which was introduced in version 3.0 of NTFS and provides file system-level encryption. EFS is available from Windows 2000 onwards. The way EFS works is transparent, yet very powerful. A high-level description of its operation is the following.


For encrypting files:
1. With a symmetric file encryption key (FEK) generated for this purpose, EFS encrypts the file.
2. Then it encrypts the FEK using the active Windows user's public key.

For decrypting files:
1. EFS decrypts the encrypted FEK using the Windows user's private key.
2. Then it decrypts the encrypted file using the decrypted FEK.

So, in the case you would like to encrypt a database in SQL Server 2005 or earlier using EFS you could achieve it as follows:

1. Log in to Windows using the user account that is used by SQL Server Database Engine.

2. Log in to SQL Server Management Studio (SSMS).

3. Detach the database from the SQL Server Instance.

4. Encrypt the database files (data and log files) on the file system-level ([File Properties] - [Advanced] - [Encrypt contents to secure data]).

5. Verify the encryption of the database files using the Windows command "cipher.exe" in the folder that contains the database files. It will return the value "E" for the encrypted files.

6. [CRITICAL] Export the Windows user's personal certificate with the private key ([Start] - [Run] – “certmgr.msc”) and store it on a safe location (i.e. removable storage).

7. Attach the database back to the SQL Server Instance.

8. If any problems, try to restart the SQL Server Instance.

Be extremely cautious when running the above procedure because in the case you change the user account that runs the SQL Server Instance and the new user is not authorized to access the database files, then you will lose access to your data. Always backup the user’s certificate/private key which can be used to decrypt the encrypted files. Always be careful when using data encryption mechanisms.

Also, if you encrypt database files and attempt to start the SQL Server service as any user other than the user that encrypted the database data/log files in the first place, the instance will not start and/or your database will be set to suspect.

A last thing you should have in mind when using EFS for encrypting SQL Server 2005 or earlier databases, is that EFS will affect the performance of SQL Server as there is an overhead whenever decrypting the underlying database files. There are many parameters that can be taken into consideration when it comes to the SQL Server performance degradation such as: the supporting storage system and its RAID levels, the OS configuration and more.   

Related Microsoft Articles:

• Transparent Data Encryption (TDE)
• How to Export a Certificate
• Encrypting File System on TechNet Library
• Export a certificate with the private key

Read more on this article...

T-SQL Tip: Retrieving Security-Related Info for SQL Server Logins

--
-- Retrieves Security-Related Information
-- for all the SQL Server Logins
--
-- SQL Server versions  supported: SQL Server 2005 or later
--
SELECT 
[name] as LoginName,
LOGINPROPERTY ([name] , 'DefaultDatabase') as DefaultDatabase,
LOGINPROPERTY ([name] , 'DaysUntilExpiration') as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsExpired'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsLocked'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsLocked,
LOGINPROPERTY ([name] , 'PasswordLastSetTime') as PasswordLastSetTime,
LOGINPROPERTY ([name] , 'PasswordHashAlgorithm') as PasswordHashAlgorithm
FROM master..syslogins

For more info, check out the following links:

• sys.syslogins
• LOGINPROPERTY

Read more on this article...

Snippets Generator v2.0 is Out!

I'm very happy to announce the general availability of version 2.0 of Snippets Generator, one of my open-source projects in CodePlex.

Snippets Generator allows the user to easily create T-SQL snippets for SQL Server 2012! You can create a snippet from scratch or by using one of the snippet templates provided by the tool.

Snippets Generator started as one of my blog posts back in January 2011 and moved onto CodePlex in December 2011. The first stable version was released on August 2012 and today, I'm releasing Snippets Generator v2.0! Of course, as always, the tool is open-source and free to everyone as it is under the Microsoft Public License (Ms-PL).

This new version (2.0) includes the following improvements/enhancements:
- Improved Graphical User Interface: More user-friendly environment.
- Snippet Templates: A library of T-SQL snippet templates that can be used as a starting point for creating your own snippets.

Screenshots:

Snippets Generator v2.0 - Main Screen

Snippets Generator v2.0 - Snippet Templates Library

Read more on this article...

Selected Tweets of Year 2012!

Hello friends,

Another year has come to an end and as being faithful to the "tradition" of posting in the last day of every year my selected tweets for the year that ends, here I am, posting the "Selected Tweets of Year 2012!".

It was another great year of interaction with you, the SQL Server Community. We saw together and discussed exciting new releases such as SQL Server 2012 and other related technologies, we discussed common issues and ways to overcome it, as well as interesting approaches to utilizing existing technologies for achieving great things.

It is a privilege to communicate with you, via all possible channels like blogs, forums, offline and online events, conferences and of course, Twitter! Here are the tweets!

Blog Posts/Articles and Announcements
[Blog] Listing Directory Contents using T-SQL - http://bit.ly/AfWuP1  #MVPBuzz #in #fb #sqlserver

[Blog] How to retrieve size information for all the tables in a #SQLServer #Database - http://bit.ly/yFb9r8  #MVPBuzz #in #fb

[Blog] Retrieving Log Space Information within a #SQLServer Instance - http://bit.ly/zxJaq3  #in #fb #MVPBuzz

[Blog] Retrieving Log Space Information within a #SQLServer Instance - The Stored Procedure! - http://bit.ly/wiKw4X  #in #fb #MVPBuzz

[Blog] Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=10.0.0.0, ... - http://bit.ly/IUVPM6  #in #fb #MVPBuzz

[Blog] SQL Server 2012: Sequence Objects - http://bit.ly/LWyDO6  #MVPBuzz #in #fb #SQLServer

Want to know more on Contained Databases on #SQLServer 2012? Read my article on @MVPAward blog! http://ow.ly/chuVQ  #MVPBuzz #fb

[Blog] Migrating to a Contained Database in SQL Server 2012 - http://bit.ly/NB6Bua  #MVPBuzz #fb #Microsoft #SQLServer2012 #Denali

[Blog] New Post Series: T-SQL Tips - http://bit.ly/S0EzIb  #MVPBuzz #SQLServer #fb

[Blog] T-SQL Tip: Retrieving Database File Sizes - http://bit.ly/OXh8RV  #MVPBuzz #SQLServer #fb

[Blog] T-SQL Tip: Retrieving SQL Server Instance-Related Information - http://bit.ly/QXsbM4  #MVPBuzz #SQLServer #fb

[Blog] Getting the Disk Usage Statistics for all Tables in a Database - http://aartemiou.blogspot.com/2012/08/getting-disk-usage-statistics-for-all.html #MVPBuzz #SQLServer #fb

[Blog] T-SQL Tip: How to Create a Simple Linked Server Between SQL Server Instances - http://aartemiou.blogspot.com/2012/09/t-sql-tip-how-to-create-simple-linked.html #MVPBuzz #fb

Proudly Presenting: SQL Server 2012 - The Database Engine [TRAILER]: http://youtu.be/wGbdJQearCo  - Enjoy!!! #MVPBuzz #sqlserver #fb

[Blog] SQL Server 2012 - The Database Engine [TRAILER] - http://bit.ly/TmbkzH  #MVPBuzz #sqlserver

[Webcast] SQL Server 2012: The Database Engine - http://bit.ly/PBVHcf  #MVPBuzz #fb #sqlserver #Denali

[The SQL Server and .NET TV] Webcast on SQL Server 2012: The Database Engine - http://youtu.be/dPAmOvtZ4QU  #fb #MVPBuzz

[Blog] There was an unexpected failure during the setup wizard - http://aartemiou.blogspot.com/2012/11/there-was-unexpected-failure-during.html #SQLServer #MVPBuzz #fb #Troubleshooting

Blogged: Argument data type ntext is invalid for argument ... - http://bit.ly/V8IoMA  #SQLServer #MVPBuzz #fb

Blogged: Handling Disk Space Issues During Heavy Index Rebuild Operations - http://bit.ly/YyE53S  #SQLServer #MVPBuzz #fb

[Blog] Internal Query Processor Error: The query processor could not produce a query plan - http://bit.ly/U0MXeY  #SQLServer #MVPBuzz #fb

[Blog] T-SQL Tip: Getting the File Locations for all DBs in a #SQLServer Instance - http://bit.ly/ZEFSEV  #MVPBuzz #fb

[Blog] T-SQL Tip: Getting all the Records from all Tables in all User Databases - http://bit.ly/UPaIoK  #MVPBuzz #SQLServer #Tips #fb

[Blog/Screencast] Migrating to a Contained Database in #SQLServer 2012 - http://bit.ly/Wxkxg4  #MVPBuzz #fb

[The SQL Server and .NET TV] Migrating to a Contained Database - https://www.youtube.com/user/sqlserverdotnetblog #SQLServer #MVPBuzz #fb


News
April 1st 2012: #SQLServer 2012 generally available!!!

I'm a #SQLServer #MVP for the fourth year in a row! A HUGE thanks to you, the Community for supporting my efforts! #MVPBuzz #in #fb

[Blog] #Microsoft #SQLServer #MVP for the fourth year in a row! - http://bit.ly/H8dfoo  #MVPbuzz #in #fb - thank you all!

My blog has reached 200.000 visits! First step in celebrating it? Webcast on #SQLServer 2012 coming up this month! #in #fb #MVPBuzz

The first stable version of Snippets Generator for #SQLServer 2012 now available! - Get it on #CodePlex! http://snippetsgen.codeplex.com/  #MVPBuzz #fb

[Blog] Snippets Generator v1.0 - Stable Version Now Available! - http://bit.ly/SN8dB2  #MVPBuzz #fb #Community @SQLServer

My YouTube Channel (The SQL Server and .NET Blog TV) got some lifting! :) Check it out! - http://www.youtube.com/user/sqlserverdotnetblog … #MVPBuzz #SQLServer

My blog now has more than 250.000 visits! Let the celebrations begin! http://aartemiou.blogspot.com  :) #MVPBuzz #SQLServer #fb

[Blog] Celebrating 250.000 Visits! - http://bit.ly/TbWBaW  - Thank you #SQLServer #Community! You Rock!!! #fb #MVPBuzz

Microsoft announces project name "Hekaton"! In-memory processing for OLTP! Wow! Live demo #now: http://www.sqlpass.org/summit/2012/  #MVPBuzz #SQLServer

#SQLServer 2012: Learn more now from The SQL Server and .NET TV! http://youtu.be/dPAmOvtZ4QU  #Webcast #MVPBuzz #fb

Hello friends! Working on something massive! Yes, it is #SQLServer-related! ;) More news soon! #MVPBuzz #fb

[Personal Website Updated] Webcasts and Screencasts: http://www.aartemiou.com/webcasts_screencasts.html | Talks and Publications: http://www.aartemiou.com/talks_publications.html  #SQLServer #MVPBuzz


Miscellaneous
[The SQL Server and .NET TV] Subscribe and stay up to date with the latest Webcasts on #sqlserver! http://bit.ly/Rk7abL  #MVPBuzz #fb

[Celebrating 250.000 Visits!] Q&A Session on SQL Server - http://bit.ly/RFeHpo  #MVPBuzz #sqlserver #fb

Perfect documentation on #SQLServer #Database Engine Locking: http://msdn.microsoft.com/en-us/library/ms190615(v=sql.105).aspx

My warmest wishes for a happy new year! A year full of health, love and creativity! One thing is for sure: together, we can make 2013 even more awesome!

Read more on this article...

Screencast: Migrating to a Contained Database in SQL Server 2012

Contained Databases is an exciting new feature shipped with SQL Server 2012.

Among other, it introduces a more robust security model where you can access databases without using SQL Server Logins. You just need to use the Contained User and you can have full access to the database.

This allows you to easily migrate Contained Databases to other instances of SQL Server 2012 without having to worry about any SQL Server logins, orphaned users, etc., for the simple reason that SQL Server Logins are not necessary when using Contained Databases!

I have just prepared a screencast with live demos showing how you can migrate an existing database to a Contained Database and thus achieve portability.

I'm making the screencast available here as well as on "The SQL Server and .NET TV":



Also, below you can access the slides.

Migrating to a Contained Database in SQL Server 2012 from artemakis

Wishing you Happy Holidays and a Happy New Year!!!
Read more on this article...