Scripting SQL Logins, Windows Logins, group membership

SQL
I'm working with AlwaysOn Availability Groups in SQ'L 2014. One of the many interesting gotchas I have with AlwaysOn is that SQL Logins on each AG node must be created with identical SIDs, otherwise after failover the database users for those SQL Logins will be orphaned. I discovered Bill Graziano's post back from 2010 where he scripts out the SQL Logins (including SID and hashed password), plus the Windows logins and server role membership for SQL 2008 server. I have adapted it for SQL 2014 SP2. Note the script was originally designed to script out the logins only if they do not exist already. I removed the 'IF NOT EXISTS' lines so it will script out all the SQL Logins and Windows Logins regardless of whether they exist. USE [master] GO /* Object: UserDefinedFunction…
Read More

Installing sqlcmd without SQL Server

SQL
I just ran into a need for sqlcmd without SQL Server. I'm on Windows Server 2016 Datacenter. I installed Microsoft® ODBC Driver 11 for SQL Server - Windows, with filename  msodbcsql.msi Installed from https://www.microsoft.com/en-us/download/details.aspx?id=36434 This is a dependency for Microsoft Command Line Utilities 11 for SQL Server, with filename MsSqlCmdLnUtils.msi  I installed from https://www.microsoft.com/en-us/download/details.aspx?id=36433 I chose the 64-bit versions of both. Restarted my console session and able to run sqlcmd without SQL Server!
Read More

Install SQL Express 2014 or 2016 from PowerShell

PowerShell, SQL
This may be useful for some folks out there. While the documentation on SQL Books online for command line installs is extensive, the examples there are limited, and the troubleshooting steps are not described clearly. You will need to download the SQL installer iso or files and mount or extract them, and locate the setup.exe. For SQL 2014 Take note that: This is installing SQL Express, a /FEATURES parameter was not required. Changes will be required if using a different distribution. This creates a default instance of SQL, change MSSQLSERVER to your desired instance name. Local Windows administrators will have SQL access by default The line of PowerShell code below that defines $params has no carriage returns between the single quotes. The results of a successful or failed install can…
Read More

My first book review! SQL Server 2014 with PowerShell v5 Cookbook

Current Events, PowerShell, SQL
Has been a very busy year for me, haven't posted lately, but was very excited to share this. I've had the opportunity to be a technical reviewer for three PowerShell books this year! One that I've really enjoyed reviewing has just been released, and the publisher Packt is offering an end of year sale for $5 eBook editions. Donabel Santos (sqlbelle.com & @sqlbelle) is a wonderful blogger, author, trainer, and Microsoft MVP. This is her second PowerShell cookbook for SQL, and it's an incredibly useful resource for learning the practical details of PowerShell, both in general and when working with SQL Server. All her examples come with code samples so you can leverage her scripts to get productive right away. You can check out the book on the Packt website. It's only $5 for a great PowerShell & SQL…
Read More

SQL 2016 CTP2 is out, with Technet Virtual Labs ready to learn!

SQL
Microsoft just released SQL 2016 CTP 2. CTP is a customer technology preview, that lets us explore new features of the product before it's released to marketing (RTM). I found it in my MSDN new downloads. Not ready to download and install it but still want to learn? Technet Virtual Labs already has SIX virtual labs you can use to explore the new features! [caption id="attachment_493" align="alignleft" width="1000"] SQL Server 2016 Virtual Labs![/caption] Just search for SQL 2016  
Read More

Scripting COMPLETE Full-Text indexes after a database restore

SQL
You may find yourself working with SQL 2000 databases, or later versions of SQL that were migrated from SQL 2000, with the Full-Text indexes stored outside the SQL data files. In this scenario, once you restore a SQL Server database, your full-text catalogs and the full-text indexes within are not restored along with your data. Note that this is not required for Full-Text indexes created in SQL 2005 and later, when the storage for Full-Text Catalogs was changed. Right-Clicking and choosing to script the full-text catalog will NOT create the indexes within the catalog. I found a useful script at humakhurshid.blog.com that scripts out the full-text catalog AND indexes. EDIT: However it misses some indexes, I found a better script at the bottom of the comment thread at this blog…
Read More

Off to SQLPASS 2013!

Learning, SQL, Uncategorized
This year I'm volunteering at SQL PASS Summit 2013 as an MCT Volunteer, to assist those interested in certifying for SQL Server 2012.  Being an MCT means keeping up with certifications, which means keeping up with tests, and since 2002 I've certified on SQL 2000,2005 and 2008. This May I achieved my MCSE: Data Platform this year, and working toward MCSE: Business Intelligence certification as well, with just one more test to go! If you're nervous about taking a Microsoft exam, just know you can do it, like thousands of other have. If you want help preparing to take a SQL Server exam, can you imagine a better place than the PASS Summit with thousands of smart SQL people there to help? Check out the PASS info page on MCP certification…
Read More