If it was not clear by my recent post Auditing SQL Server, I have compliance and security on my mind lately. This post is no different.

While SQL Server has had encryption for some time, implementation takes significant planning and can require modification of application code. With SQL Server 2008, Microsoft introduced Transparent Data Encryption (TDE). This feature allows for encryption of the actual data and log files and any subsequent backups of those files. TDE avoids the need for application modification by performing the encryption and decryption in real time and at the database level.

Note: TDE is an Enterprise Only feature

Before covering how to enable TDE and encrypt the database, it needs to be noted that the certificate used to encrypt the database needs to be backed up and available in the event a recovery is necessary. This needs to be included in DR planning before TDE is enabled.

The first step toward TDE is creation and subsequent backup of the Master Key.

USE master
GO
--Create the Master Key Encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyP@$$w0rd'
GO
--Backup the Master Key
BACKUP MASTER KEY TO FILE = N'C:\SQLCertBackup\MasterKey'
    ENCRYPTION BY PASSWORD = 'MyP@$$w0rd'
GO

Once the Master Key has been created, the Server Certificate can be created. This certificate is stored in the master database.

USE master
GO
--Create the Server Certifiacate
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Certificate'
GO
--Backup the certificate
BACKUP CERTIFICATE MyServerCert TO FILE = N'C:\SQLCertBackup\MyServerCert'
GO

With the Master Key and the Certificate created, the database is ready to be secured through the creation of the database encryption key. This key is secured by the certificate created above.

--Create the Database Encryption Key
USE AdventureWorks2012
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER
    CERTIFICATE MyServerCert
GO

The final step is to actually enable encryption of the database.

--Enable encryption on the database
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON
GO

TDE and Performance

Understanding that TDE works by encrypting and decrypting in realtime naturally leads to questions regarding performance. Microsoft BOL claims that it will only increase CPU utilization by 3-5%. Some independent testing by Rob Garrison seems to support Microsoft’s claim. You can check out his testing here.

Additional items to consider when implementing TDE:

• During the initial implementation of TDE, the entire database and log files will need to be encrypted. How long this will take is dependent upon many factors including size of the database and server specifications.
• Enabling TDE on an instance will encrypt the tempdb for the entire instance.
• Backup compression will not be as effective because encrypted data does not compress well.
• FILESTREAM data is not encrypted using TDE.

Conclusions

TDE gives the DBA a way to secure the database without having to alter applications connecting to it. It is important to understand the effects before implementing. If applications connecting to the database already cause a heavy load on the CPU it is important to test how TDE will effect performance.

If you are using TDE in production, I would love to hear about your experience in the comments below.

Permalink | Leave a comment  »

So this is the third of of the four SQL Server 2012 beta exams that I am taking.  I have blogged about my experiences with the other exams and shared the study notes that I have used.  This post is no different.  For more information no the other exams I have taken to date, see the links below. 

Querying Microsoft SQL Server 2012 (71-461)
Designing Database Solutions for Microsoft SQL Server 2012 (71-465)

The exam was pretty good.  I have nit picky issues with the way some of the questions were worded but overall I felt the test did a good job of covering the material. 

My study notes listed below are based off of the Microsoft Training Catalog.  You will not that I did not covey everything listed in the catalog.  I didn't have time to get it all listed out. 

Install and Configure SQL Server (19%)
   * Plan installation
      * Evaluate Installation Requirements
      * Drive best practices
         * Disk configuration primer: http://www.mssqltips.com/sqlservertip/1328/hard-drive-configurations-for-sql-server/
      * Service Accounts
         * These should be Managed Service Accounts
   * Install SQL Server and related services
   * Implement a migration strategy
   * Configure additional SQL Server Components
      * Configure full-text indexing
         * allows for full-text queries against character based data
         * More information about configuring full-text indexing can be found here: http://msdn.microsoft.com/en-us/library/cc879306.aspx
      * Configure FILESTREAM
         * This can be done through the GUI or through TSQL and the sp_configure command
         * More information on FILESTREAM can be found here: http://msdn.microsoft.com/en-us/library/cc645923.aspx
      * Configure FileTable
         * FILESTREAM must be enabled to use FileTable
         * Enable Non-Transaction Access at the Database Level
         * Specify a directory for FileTables at the Database Level
         * More information on FileTable: http://msdn.microsoft.com/en-us/library/ff929068(v=sql.110).aspx
   * Manage SQL Server Agent
      * Create, maintain, and monitor jobs
         * More information on creating jobs: http://msdn.microsoft.com/en-us/library/ms190268.aspx
      * Monitor Jobs
         * Job activity can be viewed in the Job Activity Monitor
         * Job activity can also be queried by running the sp_help_jobactivity
         * More information can be found here: http://msdn.microsoft.com/en-us/library/ms187449.aspx
      * Administer jobs and alerts
         * An alert  is an automated response to an event
         * Alerts can respond to
            * SQL Server events
            * Performance conditions
            * Windows Management Instrumentation (WMI) events
            * More information on alerts can be found here: http://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

Optimize and Troubleshoot SQL Server (14%)
   * Identify and resolve concurrency problems
      * Deadlock Trace Flags
         * Profiler Trace Flags 1204 and 1222 return information that is captured in the error log
         * More information regarding Deadlocks can be found here: http://msdn.microsoft.com/en-us/library/ms178104.aspx
      * Reporting database infrastructure (replicated databases)
         * Transactional replication is typically the form used for data warehousing and reporting
         * More information can be found here: http://technet.microsoft.com/en-us/library/ms151198.aspx
      * Monitor via Dynamic Management Views
         * DMVs and DM Functions return server state information
         * More information regarding DMVs can be found here: http://technet.microsoft.com/en-us/library/ms188754.aspx
      * Diagnose Blocking
         * Information regarding blocking can be found here: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/14/blocking-blockers-and-other-b-words-a-month-of-activity-monitoring-part-14-of-30.aspx
   * Collect and analyze troubleshooting data
      * Monitor using profiler
         * Profiler manages traces and allows for the replay of trace results
         * More Information on Profiler can be found here: http://msdn.microsoft.com/en-us/library/ms181091.aspx

Permalink | Leave a comment  »

Design Database Structure (29%)

• Design for business requirements
• Design physical database and object placement
• Log files and data should be isolated at the physical disk level
• Logical vs. physical design: http://sqlserverpedia.com/wiki/Physical_vs._Logical_Design
• Filegroups
• Database objects and files can be grouped together in file groups for allocation and administration purposes.
• This is for data files only.  Log files are managed separately.  
• No file can be a member of more than one file group.
• Two Types
• Primary
• User Defined
• More information regarding file groups can be found here: http://msdn.microsoft.com/en-us/library/ms179316.aspx
• Design a table and index partitioning strategy
• Partitioning can have manageability and performance benefits
• More information re: Table Partitioning: http://msdn.microsoft.com/en-us/library/ms190787.aspx
• Partitioned Indexes
• Aligned Index
• Nonaligned Index
• More information regarding Partitioned Indexes: http://msdn.microsoft.com/en-us/library/ms190787.aspx
• Design a migration, consolidation, and upgrade strategy
• Upgrade with minimal downtime
• If Mirroring or clustering is in play this can be achieved by doing rolling upgrades
• Contained Databases
• This is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
• Metadata that describes the database is maintained inside the database and not in the master database
• All metadata are defined using the same collation
• User authentication can be performed by the database
• More information on contained database: http://msdn.microsoft.com/en-us/library/ff929071.aspx
• Contained databases are extremely useful with AlwaysOn for moving databases and users.
• Design SQL Server Instance
• Affinity Masks
• The Affinity Mask will change how SQL Server accesses CPUs.
• More information regarding Affinity Mask http://msdn.microsoft.com/en-us/library/ms187104.aspx
• The IO Affinity mask is a targeted optimization
• More information on the IO Affinity mask can be found here: http://blogs.msdn.com/b/psssql/archive/2010/11/19/how-it-works-io-affinity-mask-should-i-use-it.aspx
• Clustering
• SQL Server clustering allows multiple physical servers to host a single SQL Server Instance.  
• It leverages Windows Clustering technologies
• Microsoft Distributed Transaction Control
• Coordinates transactions that update two or more transaction-protected resources
• More information regarding regarding Distributed Transaction Coordinator: http://technet.microsoft.com/en-us/library/cc759136(v=ws.10).aspx
• SQL Server memory allocation
• http://msdn.microsoft.com/en-us/library/ms178067.aspx
• Design backup and recovery
• More information regarding Backups and Recovery: http://msdn.microsoft.com/en-us/library/ms187048.aspx

Design Database and Database Objectives (32%)

• Design a database model
• Design a logical schema
• Design a normalized database
• Design data access and data layer architecture
• Relational Model
• Normalized data model
• Database schema
• EAV modeling
• Star-Schema
• Collation
• These are the rules that govern the use of characters for a language or alphabet.
• ANSI NULLS
• This changes the way that NULLs behave
• Based off of the ANSI SQL-92 standard
• More information regarding NULLs can be found here: http://msdn.microsoft.com/en-us/library/ms187048.aspx
• QUOTED IDENTIFIER
• Causes SQL Server to follow the ISO rules regarding quotation mark delimiting
• More information can be found here: http://msdn.microsoft.com/en-us/library/ms174393.aspx
• Design Tables
• Common Table Expressions
• Temporary result set
• Confined within a single query
• More information regarding Common Table Expressions can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx
• Transactions
• Sequence of operations performed as a single logical unit of work
• Primer on Transactions can be found here: http://www.sqlteam.com/article/introduction-to-transactions
• Views
• Essentially a virtual table
• If the view is indexed it will become a stored set of data
• Information regarding designing views can be found here: http://msdn.microsoft.com/en-us/library/ms189918.aspx
• Temporary Tables vs. Table Variables
• Transaction logs are not recorded for table variables
• Procedures with temporary tables cannot be pre-compiled
• Primer here: http://www.sql-server-performance.com/2007/temp-tables-vs-variables/ (Note, this is reflective of SQL Server 2000 and 2005)
• Filestream
• This is a way to store unstructured data in the database but store it separate from the structured data.  This means SQL Server can us the NTFS file system to store the files. 
• Files are stored using varbinary(max).
• While these files are stored on the NTFS file system they should not be accessed from the file system unless the OpenSqlFilestream API is used.
• More information on Filestream can be found here: http://technet.microsoft.com/en-us/library/bb933993.aspx
• FileTable
• This is built on top of Filestream technology
• Allows windows file manipulation of SQL Server unstructured data
• Allows SQL Server functionality including full-text search and semantic search to be used and access via SQL Server management tools.
• More Information regarding FileTables can be found here: http://msdn.microsoft.com/en-us/library/ff929144(v=sql.110).aspx
• Semantic Engine
• Video on the Microsoft Semantic Engine from the SQL Server team: http://www.microsoftpdc.com/2009/SVR32
• “The Semantic Engine is a technology that enables us to mine unstructured data and query it using tools that are already familiar to SQL Server professionals. It extracts and indexes pertinent information from data sources that are presented to it through data adapters. Adapters may exist for documents, audio, video, web pages and a plethora of other unstructured or partially-structured data sources” via: http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/22/microsoft-semantic-engine-uncovered-the-harnessing-of-unstructured-data.aspx
• Sequences
• SEQUENCE is similar to IDENTITY but it is not bound to a specific table.  Much of the difference is in the way the Metadata is stored for the numbers.
• SEQUENCE can be used during a stored procedure.
• Values can be appended to the front of a sequence.
• More information can be found here: http://technet.microsoft.com/en-us/magazine/hh407114.aspx
• Data Compression
• Can be better for IO but will use CPU overhead
• Compression is an Enterprise edition only feature
• More Information on Data Compression can be found here: http://msdn.microsoft.com/en-us/library/cc280449.aspx
• Design for concurrency
• Design TSQL stored procedures
• Design a management automation strategy
• Design for implicit and explicit transactions
• TRY…CATCH
• This is a way to implement error handling inside of TSQL. 
• When an error condition is detected inside of the TRY block, control is passed to the CATCH block for error processing.
• More information on TRY…CATCH can be found here: http://msdn.microsoft.com/en-us/library/ms179296.aspx
• COMMIT TRANSACTION
• Marks the end of a successful transaction
• Makes all data modification performed since the start of the transaction permanent
• More information on COMMIT TRANSACTION can be found here: http://msdn.microsoft.com/en-us/library/ms190295.aspx
• THROW
• Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct
• If TRY…CATCH is not available the session is ended.
• THROW is a new feature in SQL Server 2012
• It allows for error handling without the need to use the RAISERROR statement that requires the use of the sys.messages table.
• THROW error numbers need to be higher than 50000 to avoid replicating system errors.
• More inofmraiton on THROW can be found here: http://msdn.microsoft.com/en-us/library/ee677615.aspx
• More examples of THROW can be found here: http://cm-bloggers.blogspot.com/2011/02/using-new-throw-keyword-in-sql-server.html

Design Database Security (15%)

• Design an application strategy to support security
• Design security
• Schemas and schema security
• SQL Server vs. Integrated Authentication
• Integrated authentication leverages the Windows principal token in the operating system.
• This uses Kerberos security protocol
• Complexity requirements and account management is in a central location
• SQL Server Authentication cannot use Kerberos security protocol
• Is good for mixed environments that don't support windows auth.
• More information regarding SQL Server authentication can be found here: http://msdn.microsoft.com/en-us/library/ms144284.aspx
• Permissions and mirroring issues
• If both SQL servers in a mirroring scenario do not run under the same service account the service account for each server will need to be manually added to all the servers in the scenario.
• More information regarding logins and mirroring can be found here: http://technet.microsoft.com/en-us/library/ms366346.aspx
• Encryption

Permalink | Leave a comment  »

I have just finished the first of the Microsoft SQL Server 2012 Beta exams, Querying Microsoft SQL Server 2012 (71-461).  I took them for a couple of different reasons, I feel that certifications are important and it was an opportunity for me to upgrade my current certifications.  

My thoughts after finishing the exam, You don't want me to be your developer.  It was a good exam by I know my strengths are in database administration and not development.  There were some questions that were unclear, and some that I flat didn't know the answer of but they seemed positioned simlar to the tests from last year.  

If you are intersted in taking these exams you can register for them for free here.

I have mentioned before that use Evernote for a host of things.  Studying for these exams has been no different.  I have created a notebook that has my links to study notes.  I am going to post them here but I will share the notebook that will update as I update them.  You can see the notebook here.

The notes below and in my notebook are based off of the study guide posted in the Microsoft Training Catalog.

This exam is intended for SQL Server database administrators, implementers, system engineers, and developers with two or more years of experience who are seeking to prove their skills and knowledge in writing queries. Primary responsibilities may include but are not limited to:

Create database objects (24%)

• Create and alter tables using T-SQL syntax (simple statements)
  • Create and alter tables
    • Tables store data in the database
    • The CREATE TABLE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms174979.aspx
    • After a table is created you can change many of the options through the ALTER TABLE command.  
    • The ALTER TABLE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms190273.aspx
• Create and alter views (simple statements)
  • Vies are essentially virtual tables.  This virtual table is defined by a query.
  • Views have a max of 1024 columns
  • The CREATE VIEW TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187956.aspx
  • The ALTER VIEW TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms173846(v=sql.110).asp
• Design Views
• Create and modify contraints
  • Constraints define the way the Database Engine enforces the integrity of a database. 
  • Constraints are preferred to using Triggers, Rules, and Defaults
  • Keys are constraints
  • Constraint TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189862.aspx
• Create and alter DML Triggers
  • INSTEAD OF are executed in place of the usual triggering action. 
  • AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE
  • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms190267.aspx
  • CREATE TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx
  • Triggers are a way to enforce business rules and data integrity.
  • It is a special type of stored procedure that automatically takes effect.
  • There are three types: DML, DDL and logon
  • DDL triggers are invoked when a data definition language event takes place.
  • DML triggers are invoked when when a data manipulation language event takes place in the database.  This includes INSERT, UPDATE or DELETE statements that modify data.    
  • Logon triggers fire stored procedures in response to a LOGON event.
  • They can cascade changes through related tables in a database.  Constraints can do this more efficiently.
  • The CREATE TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx
  • The ALTER TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms176072.aspx

Work with Data (27%)

• Query data by using SELECT statements
  • Ranking Functions
    • Ranking functions return a ranking value for each row in a partition
    • The Ranking Function syntax for RANK can be found here: http://msdn.microsoft.com/en-us/library/ms189798.aspx
  • Join Basics
    • A good primer can be found on Pinal Dave's blog: http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
  • Except and Intersect
    • Returns distinct values by comparing the results of two queries
    • EXCEPT retunes and distinct values from the left query that are not also found in the right query.
    • INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand
    • Rules
      • The number and order of the columns must be the same in all queries
      • The data types must be compatible
    • Two NULL values are considered equal
    • More information can be found here: http://msdn.microsoft.com/en-us/library/ms188055.aspx
  • Dynamic SQL
    • Dynamic SQL is the term used when the SQL code is generated by a program before it is executed.  
  • CASE
    • Evaluates the list of conditions and returns one of multiple possible result expressions
    • The CASE statement can be found here: http://msdn.microsoft.com/en-us/library/ms181765.aspx
  • ISNULL
    • Replaces NULL with the specified replacement value
    • ISNULL is not a replacement for IS NULL. 
    • ISNULL TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms184325.aspx
  • COALESCE
    • Returns the first non null expression among its arguments
    • Expressions involving COALESCE with non-null parameters is considered to be NULL
    • The COALESCE statement can be found here: http://msdn.microsoft.com/en-us/library/ms190349.aspx

• Implement sub-queries
  • A subquery can be used anywhere an expression is allowed
  • A subquery will return a single value
  • More information can be found here: http://msdn.microsoft.com/en-us/library/aa213252(v=sql.80).aspx

• Implement data types
  • A data type is an attribute that specifies the type of data that the object can hold.  
  • Data types can be created through the CREATE TYPE command or through .NET Framework
  • More information on Data Types can be found here: http://msdn.microsoft.com/en-us/library/ms187752.aspx
  • GUIDs can cause performance issues when they are used as the clustered index key.  More information can be found from Glenn Berry here: http://www.sqlservercentral.com/blogs/glennberry/2010/03/22/why-uniqueidentifier-is-a-bad-choice-for-a-clustered-index-in-sql-server/
  • GUIDs are also a larger data type than necessary.

• Implement aggregate queries
  • Aggregate functions return a single value summarizing a given data set.  All aggregate functions are deterministic.  
  • Examples: COUNT, MAX, MIN, AVG, SUM, etc
  • More information on aggregate functions can be found here: http://sqlserverpedia.com/wiki/Built-in_Functions_-_Aggregate_Functions

• Query and manage XML data
  • SQL Server 2012 has changed the Data Type Conversion by the XML value Method Has Changed.  More information regarding this change can be found here: http://technet.microsoft.com/en-us/library/ms143359(v=sql.110).aspx
  • TSQL supports a subset of the XQuery language that is used for querying the XML data type.

Modify Data (24%)

• Create and alter stored procedures
  • Stored procedures accept input parameters return values
  • They contain programming statements that perform operations in the database
  • The CREATE PROCEDURE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187926.aspx
• Modify data by using INSERT, UPDATE, and DELETE statement
• Combine Datasets
  • Datasets can be combined by UNION, EXCEPT, and INTERSECT
  • ORDER BY is only allowed at the end of the statement and cannot be used within the individual queries
  • GROUP BY and HAVING clauses can be used only within individual queries not on the final result set.
  • The FOR BROWSE clause cannot be used.
  • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms191523.aspx
• Work with functions
  • Apply built-in scalar functions
    • Provided by SQL Server.
    • Cannot be modified
    • Access system information without accessing the system tables
    • Used to perform common tasks like SUM, GETDATE, or IDENTITY
    • MSDN Library Reference:http://msdn.microsoft.com/en-us/library/ms177499.aspx
  • Apply ranking functions
    • Retuns a ranking value for each row in a partition
    • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx

Troubleshoot and Optimize Queries (25%)

• Optimize queries
  • Statistics
    • The query optimizer uses statistics to create query plans that improve query performance. 
    • They contain statistical information about the distribution of the values in one or more columns of a table or indexed view
    • More information regarding statistics can be found here: http://msdn.microsoft.com/en-us/library/ms190397.aspx
  • Query Plans
    • SQL Server 2012 includes additional information over 2008R2.  
    • Additions to the SELECT Statement
      • MemoryGrantInfo is now included
      • OptimizedHardwareDependentProperties is now included
      • Warnings are now included to show things that will effect plans
      • Some more information can be found here: http://www.sqlservercentral.com/blogs/scarydba/2011/12/12/changes-to-sql-server-2012-execution-plans/
    • Plan Guide information can be found here: http://technet.microsoft.com/en-us/library/ms190417(v=sql.110).aspx
  • DMVs
    • DMV information can be found here: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/18/sql-server-2012-changes-to-system-objects-in-rc0.aspx
  • Hints 
    • options or strategies for enforcement by the query processor. 
    • Join Hints
      • LOOP, HASH, MERGE, REMOTE
      • More information on Join Hints can be found here: http://msdn.microsoft.com/en-us/library/ms173815.aspx
    • Query Hints
      • More Information on Query Hints can be found here: http://msdn.microsoft.com/en-us/library/ms181714.aspx
    • Table Hints
      • Table Hints override the default behavior of the query
      • More information on table hints can be found here: http://msdn.microsoft.com/en-us/library/ms187373.aspx
    • Statistics IO
      • Setting STATISTICS IO is ON the query statistics are displayed when it is run
      • Statistics will show information regarding physical and logical reads.
      • More information can be found here: http://msdn.microsoft.com/en-us/library/ms184361(v=sql.110).aspx
    • Parameterized Queries
      • TSQL that accepts parameters
• Manage transactions
• Evaluate the use of row-based operations vs. set-based operations
• Implement error handling

Create and alter indexes

• Index allows specific data to be located quicker.
• Indexes are on-disk structure associated with a table or view.
• There are two types of indexes: Clustered and Nonclustered
  • Clustered
    • Sort and store the data rows in the table. 
    • Like 'The Highlander', there can be only one
  • Nonclustered
    • Contains key values and a pointer to the data roy that contains the key
    • Can have more than one.
• Indexes, if properly designed, can reduce disk I/O and improve system performance by reducing the use of system resources. 
• CREATE INDEX TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms188783.aspx

Create and alter stored procedures

• Stored procedures accept input parameters return values
• They contain programming statements that perform operations in the database
• The CREATE PROCEDURE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187926.aspx

Create and alter user-defined functions (UDFs)

• These functions are used to bundle code for reuse within SQL Server.  
• TSQL Errors cause the function to stop.
• Benefits of UDFs
  • Allow modular programming
  • Allow faster execution
  • Can reduce network traffic
• The CREATE FUNCTION TSQL can be found here:http://msdn.microsoft.com/en-us/library/ms191320(v=sql.110).aspx

Permalink | Leave a comment  »

The Houston SharePoint User group reached out to other area user groups this year and asked if we would like to participate in a joint user group event around Christmas time. We, of course, thought this was a fantastic idea. What is better than having community get together? Having multiple communities get together for a great event. Thus the joint Casino Night was born.

When: Wednesday, December 14th, 2011

Where:Norris Conference Center, Red Oak Ballroom

Registration: Link

Special Notes

The registration email contained some special restrictions. Please see below:

Special restrictions may apply, some offers not valid in all states, see in-store for details, this offer not valid in Iceland or the Turkish Islands, no purchase necessary when purchasing, if you experience certain conditions longer than 5 to 6 six hours please see your doctor.

Special Thanks

While I was not involved with sponsorship for this event Red Gate reached out to the community looking for special events at the end of the year. I responded with information for the event and they loved the idea. They loved it to the point that they helped sponsor the event. Just another reason I love being a part of the SQL Community.

I have been selected to speak at SQL Saturday #97 in Austin, TX on October 1st.  I will be presenting a bonus topic, Adventures in Project Management.

I am very much looking forward to presenting is Austin.  Reviewing the lineup, it will be an event not to be missed.  Some of my favorite people will be there: AJ Mendo (Blog | Twitter), Ryan Adams (Blog | Twitter), Joe Celko  just to name a few.  Wes Brown (Blog | Twitter) and his leadership team are shaping up a great event.  Don’t miss out, get registered today.

Adventures in Project Management Synopsis

Join Jonathan Gardner in a discussion about the project management and how understanding the process can increase the chances of delivering a top quality work product.  Learn the questions to ask that will help prevent scope creep and manage stakeholders from a PMI certified Project Manager.

As Cochair of the planning committee I am very pleased to announce that we are officially having a SQLSaturday in Houston. The event will be on January 29th, 2011 at the Bammel Church of Christ.

Co Chairs

Jonathan Gardner (Blog|Twitter)

Malik Al-Amin (Blog|Twitter)

Nancy Hidy Wilson (Twitter)

Information

We have created a Twitter account @SQLSatHou to distribute information. Please use the hastag #SQLSat57 when discussing the event and it will show up on the official SQLSaturday site.

The official SQLSaturday #57, Houston website is here.

Volunteers

We are always looking for people to help out. On the registration page there is a section to volunteer. I would highly encourage everyone to do this. While my motives are a bit selfish, it is really a great way to network with other volunteers and give back. If that is not enough, check out David’s Post on Five Reasons You Should Volunteer with Your Local SQL User Group and Host a SQL Saturday

Hope to see you there!

This weekend I was in Dallas, TX with Nancy Wilson (Twitter) and Malik Al-Amin (Twitter) form the Houston Area SQL Server Users Group volunteering at SQLSaturday 35. It gave us a unique perspective and taught us some very valuable lessons for when we put on SQLSaturday in Houston.

I wanted to give a big thanks to the North Texas SQL Server Users Group, all of the presenters, and volunteers to helped make this event a success. The general consensus that I heard was that everyone took something away from the event that they were going to be able to use.

Denny Cherry (Blog|Twitter) had a great write up about SQL Saturday #33. I am going to follow a similar format for this post.

The Good

Size

I was surprised by the number of people that were registered to attend. 500 people were registered and there was a wait list for the event. The event was limited to 500 people because that would exceed the fire code for the building. In the end, everyone who showed up was able to get into the event. I don’t know the official attendance number but the last number I heard was near 400. There were 42 sessions across 7 rooms with some extra sponsor sessions held during the lunch break.

Food

I ate breakfast at the hotel as I didn’t know that there was going to be breakfast at the event. I didn’t see it mentioned but to be honest that may have been an err on my part. I didn’t read all of the latest event communication so there is a large possibility that I missed the announcement. I didn’t hear anyone complaining about the food, especially with the line for Paciugo Gelato wrapping around the entire building.

Location

SQLSaturday 35 was held at the Region 10 Education Services Center in Richardson, TX. It turned out to be a great place to hold SQLSaturday as it was designed for just such a training event. As a matter of fact, the I like it so much I am going to contact the Region 4 Education Services Center in Houston to see if we can hold a SQL Saturday there soon.

The hotel was very close to the event, approximately 2 blocks. This would be just a short walk, but with temperatures over 90 degrees I don’t think anyone did. I did not hear of anyone that attended the event from out of town say that they were staying somewhere else. What I did hear is that some people, including me, tried to check into the wrong hotel as there are two Courtyard by Marriott locations in Richardson. Instead of looking at my confirmation I relied on Google Maps to tell me where to go. Google Maps #fail.

The Could be Better

The Raffle

The prizes that were give away were fantastic. It was no secret that I wanted the iPad that was being raffled off, I wanted it bad. Execution of raffles can be problematic. The prizes displayed/discussed, the number drawn, and the recipient then had to come down to the same person that called the number out to claim their prize all while the audience clapped after every drawing. This causes prize distribution to be lengthy, hence the creation of the t-shirt canon.

I am not suggesting I have ultimate raffle solution but some great ideas were thrown around at the after party that may have merit for further evaluation.

The After Party

The location for the after party was at the same location as the speaker’s dinner the night before. The room was partitioned off from the rest of the building so it was essentially a private room just for us. There was really no advertising of the after party until the raffle at the end of the day when everyone was trying to file out of the room.

The after party can be the most valuable of the entire event. The after party is where attendees can network and talk with the speakers and volunteers, many times in a one on one conversation. Advertisement of the after party should have been throughout the course of the event.

Overall

I would say that this event was a huge success. The issues mentioned above are really secondary to the main goal of providing a day of SQL training. The team from the North Texas SQL Server Users Group really did a fantastic job putting on the event and I am looking forward to attending again when they have the next one.

I am working with Malik Al-Amin (Twitter) to put on a SQL Saturday in Houston. You can follow either of us on Twitter for more details or you can follow the Twitter account that I have set up for the event SQLSatHou.

Find a SQL Saturday event near you and get involved with your local user groups.

During a Twitter conversation today Malik Alamin (Twitter) asked about the Autogrow settings on his databases. The conversation naturally progressed to the question of how you change the default behavior and set all newly created databases to have user defined settings. Brent Ozar (Blog | Twitter) was able to point us in the right direction to find a solution.  Modifying the model database.

About the model Database

When a new database is created either by using the CREATE DATABASE statement, that mean via SSMS as well, the model database is used as a template. This means that change made to the model database settings, including tables, permissions, stored procedures, and functions will all transfer over to the newly created database.

According to the TechNet article about the model database some file sizes may look different depending on what version of SQL Server is installed.  Also, there are some things that cannot be modified in the model database. Items like CHANGE_TRACKING, ENCRYPTION, and TRUSTWORTY cannot be modified. A full list of restrictions can be found in the TechNet Article.

Example

I need to set the auto grow on my data file to Autogrow by 10% instead of 1 MB as was the default on my instance. I can run the ALTER DATABASE command or use SSMS against the model database

USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N’modeldev’, FILEGROWTH = 10%)
GO
USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N’modeldev’, FILEGROWTH = 10%)
GO

Making these changes will alter all newly created databases to have the same settings.

Note: Jorge Segarra (Blog | Twitter) pointed out that this does NOT work in a SharePoint environment.  SharePoint does not use the model database when it creates new site databases.

This is a reminder that we will have Kevin Kline (Blog | Twitter) with us discussing Top 10 Mistakes on SQL Server. I would encourage everyone in the area to come out if they can. Detailed meeting information below:

When: Tuesday, April 13, 2010 – 11:30am-1:00pm
Where: Microsoft Houston Office
2000 W. Sam Houston Pkwy. S. #350
Houston, Texas 77042-3615
Pizza and drinks provided by Idera.

LiveMeeting Link
Conference Call for audio – 1-888-320-3585 (passcode 76027128)

Topic: Top 10 Mistakes on SQL Server
Speaker: Kevin Kline, Technical Strategy Manager, Quest; Immediate Past President of PASS (blog | twitter)

If you are planning to attend in person, please RSVP to (Nancy.Wilson @ sqlpass.org) by Monday, April 12.