Zero to Hero with PowerShell and SQL Server–Precon at DevDataDay

{dev = data} DAY Birmingham 2016Are you looking to learn about PowerShell? Do you use SQL Server? Then boy have I got a bargain for you!

I’m doing a precon for our upcoming devdataday event. The precon will take place on Friday, August 19th, the day before devdataday.

This is designed to be a course for the PowerShell novice. No prior knowledge required! I’ll start the day with an introduction to PowerShell, basic usage and cmdlets. Next we’ll dive into programming scripts in PowerShell, starting with the basics of loops, conditional logic, and functions. We’ll move into advanced topics, including creating your own reusable modules and testing them.
The real meat of the day comes next, learning how to interact with SQL Server from PowerShell. Both the SQL Provider and SMO (SQL Management Objects) DLL library will be covered in full. Most importantly you’ll learn how to read the online SMO library documentation and how to convert the examples into PowerShell syntax. The day will culminate by building a reusable module for working with many SQL Server common tasks, such as T-SQL code generation, or performing health checks on your servers.
The day will wrap up with a look at Pester, the new open source PowerShell testing tool. You’ll see how to test all the code you generated during the day. Don’t be left behind, learn how to leverage the power of PowerShell in your SQL Server environment.

To register, just go to https://devdatadaypowershellprecon.eventbrite.com/

Simple Way to Fix SQL Server Page Level Corruption

I’ve decided to try an experiment, and allow the occasional guest blogger. This will give new bloggers additional exposure, and draw eyes to both our sites.

Our first guest blogger is Andrew Jackson. Andrew is a SQL DBA and SQL Server blogger too. He likes to share about SQL Server and the problems related to it as well as their solution, handling database related user queries, server or database maintenance, database management etc.

You can find Andrew online at:

Facebook: https://www.facebook.com/people/Andrew-Jackson/100008825676608

Linkedin: https://www.linkedin.com/in/jackson-andrew-401147a5

Twitter: https://twitter.com/jacksonandrew32

Editor & Blog Contributor at: http://www.sqlmvp.org/

Without further delay, here is Andrew’s first contribution, Simple Way to Fix SQL Server Page Level Corruption.


 

Database corruption is the worst situation for any Database Administrator. The SQL database stores very crucial data, the occurrence of corruption make it inaccessible which causes huddle in workflow in any organization or business. Whenever the corruption issues are discovered, it must be resolved on time by the admin before the issue spread through the data infrastructure. Therefore, it is very necessary to take proper steps to protect the data from such damage. This article explains the causes, troubleshooting, and fixing SQL server page level corruption.

Page Level Corruption

Pages are the most basic unit of data stored in database, all .mdf or .ndf files in a database are logically divided into pages numbered from 0 to n. Whenever the pages of database file are infected by the corruption, DBA will repair each damaged page in isolation. Repairing and restoring the few pages individually is faster than repairing the entire file.

Causes:

· Hardware malfunction, most of database corruption occurs due to hardware failure.

· Issues in SQL server itself, sometimes when there are internal issues in SQL server results corruption

· Unplanned shut-down when database is opened

· Virus intrusion

· Updating the SQL server in newer version also results corruption

How to Fix Page Level Corruption

Moreover, Admin have their eventual plans to tackle SQL Server database corruption issue, there are strategies like replication, database monitoring, disaster recovery plans etc. However, there are the conditions when admin do not have such solution. In that case, admin need a solution to handle this problem, In SQL 2005 and later version there is a feature called Page Checksum that creates checksum value to detect the scale of the damage in database.

If the problem in SQL database is unreadable by SQL server, then it requires the complete restoration of the database. On other hand if damage is not that severe and lesser pages are infected then DBA can use DBCC utility.

DBCC CHECKDB Command for Handling Page Level Corruption

DBCC CHECKDB is a command that fix the corruption issue in SQL database. It fixes the inconsistency in SQL server database by performing Database Consistency Checks.

It is a combination of DBCC CHECKCATALOG, CHECKALLOC and DBCCCHECKTABLE.

The syntax of DBCC CHECKDB command is given below:

clip_image003

Other Possible Solutions

There is also an alternative solution to handle Page level corruption in SQL database, user can opt a third party tool namely that can easily tackle this corruption. It can recover highly corrupted page from both primary and secondary database. It supports all the versions of SQL Database Server. Using these defined ways; user or DBA can easily recover the page level corruption.

 


Thanks Andrew for your contribution. If you are interested in becoming a guest blogger, just email me info <@> arcanetc.com.

SQL Saturday 498 Chattanooga

Do you like PowerShell? SQL Server? Are you anywhere close to Chattanooga TN? Then don’t miss this SQL Saturday, June 25th 2016.

I’m giving two sessions (yes two for the price of one!). The first session will be PowerShell 201. It covers advanced concepts you need to know, such as debugging, remoting, security, and code signing.

The second session will educate on using PowerShell with SQL Server. We’ll cover the use of the SQL Provider (SQLPS) as well as the more advanced SQL Management Object library (SMO).

You’ll find the full schedule here, as well as links to register, directions, and the like.

http://www.sqlsaturday.com/498/Sessions/Schedule.aspx 

Best of all, my demos are already uploaded, so you can download early and play along during the presentation!

I’ll mention Chattanooga is a great vacation town, so bring your whole family. There’s a huge aquarium, discovery museum, Lookout Mountain, and tons of attractions to keep them occupied while you’re having fun at SQL Saturday.

Chattanooga SQL Saturday June 27 2015–Zero to Hero with PowerShell and SQL Server

This Saturday, June 27 2015, I will be at SQL Saturday #410 in Chattanooga, TN. I’ll be presenting a session “Zero to Hero with PowerShell and SQL Server”, in which we’ll start with the basics of PowerShell, then move into working with SQL Server via the PS SQL Provider.

This is going to be an extremely fast paced session, so you may wish to download the code samples ahead of time. I have already uploaded the samples to the event site. Just go to the Sessions menu, Schedule, and you’ll see a bit download button under the session. As of right now I’m the last session of the day, in room 219, but that is subject to change so be sure to check the schedule upon arrival.

Be warned, there is far more code in the download then we’ll be able to cover in a one hour session. It is well commented though, and you should be able to understand it after the session.

If you are looking for even more PowerShell goodness, my friend Aaron Nelson ( @sqlvariant | blog ) is doing a PowerShell for Data Professionals just after lunch. Our two sessions should work well together for those interested in using PowerShell in the world of SQL Server.

Hope to see you there!

SQL Server SSIS SSDT Error – Method Not Found: Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled

Every so often, especially when setting up a new virtual machine, at some point I get this error when working in SSIS:

 

image

 

It happens often enough that, to be honest, I mostly wanted to make this as a record to myself and friends, but I am hopeful that you will be helped as well. And to give credit where it is due, I found the original answer at Stack Exchange:

http://stackoverflow.com/questions/24745396/isvisualstudio2012proinstalled-method-not-found-error-when-running-an-ssis-pac

The steps are pretty straight forward.

1.  First, if you have Visual Studio open, close it.

2. In the classic Windows menu go to Start, All Programs, Microsoft Visual Studio 2012, Visual Studio Tools, and right click on the “Developer Command Prompt for VS2012” and pick “Run as administrator”. If you are in Windows 8 or Server 2012 or later, probably easiest to just do a search for “Developer Command Prompt for VS2012”.

3. Navigate to Visual Studio’s Private Assemblies folder by entering “CD C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies”  (If you have installed VS to another drive or folder, adjust the path accordingly).

4. Enter the command “gacutil /if Microsoft.SqlServer.Dts.Design.dll” into the command prompt.

 

image

 

Now you should be able to open your SSIS projects.

The dll is the main set of libraries for SQL Server Integration Services. For some reason, after certain Windows updates, this becomes deregistered and you have to manually add it back. Not a huge deal, but annoying if it happens often enough. As I just had it happen today after a Windows update to the Hyper-V VM I use for some of my SQL Server development, I wanted to post this as a reminder on how to correctly fix the issue.

Zero to Hero with PowerShell and SQL Server at 24 Hours of Pass

On September 9th I am co-presenting “Zero to Here with SQL Server and PowerShell” for the 24 Hours of PASS. If you’ve not heard of 24 Hours of PASS, it is 24 straight hours of online presentations. This time the sessions are a preview of the SQL PASS Summit in Seattle, WA in November.

At the PASS Summit I, along with two co-workers, am presenting a full day Pre-Con entitled Zero to Hero with PowerShell and SQL Server. I’m also doing a regular session, Make SQL Server POP with PowerShell.

The session for 24 Hours of PASS will take place at 00:00 GMT on September 10th, or for those of us in the states, September the 9th, 8 PM Eastern, 7 PM Central, 6 Mountain, or 5 Pacific. The session is titled the same as the precon, Zero to Hero with PowerShell and SQL Server. Through the preceding link you can see more about the session, get to the full schedule, and most importantly register!

Be sure to check out my co-presenters too, Bradley Ball (@SqlBalls | http://sqlballs.com ) and Jason Strate (@StrateSQL | http://www.jasonstrate.com )

PowerScripting Podcast

I just wanted to give a thanks to the guys at the PowerScripting Podcast for having me on tonight. As soon as it is released I’ll follow up with a link.

For those who came here from hearing me on the podcast, you can find more info on SQL Saturday at: http://bit.ly/sqlsat328

If you want to find out more about my sessions at the PASS Summit, you can jump to http://bit.ly/acsummit. My co-presenters for the precon are Brad Ball @sqlballs and Jason Strate @stratesql.

The Pragmatic Works webinars can be found on the company website at http://pragmaticworks.com. Just follow the Free Training on the T’s to get access to the webinars. You can search by author name (Robert Cain will get you mine) or topic.

My other training videos can be found on Pluralsight, http://pluralsight.com/training.

I also have a youtube channel with a couple of videos, https://www.youtube.com/user/arcanecode. Check out the Column Mode Editing video for a quick editing tip on making your life easier with both PowerShell and SQL Server.

Follow

Get every new post delivered to your Inbox.

Join 133 other followers