SQL Query: Set All DBs to the Simple Recovery Model–DEV

image

 

The Attached Query

The really cool part about this query is how easy it is to modify.

The first section declares the variables, in this case there are 3.

The second section, sets the action for each of he declared variables

The last section loops through each DB using the PROC sp_MSforeachdb and runs each command,,,

Set the Recover Model to Simple, Backs up All Dbs except tempdb, and shrinks the DBs

 

 

-- DBCleanup for DEV

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)

-- Set Recovery Model to Simple except for tempdb
SET @cmd1 = 'if ''?'' <> ''tempdb'' ALTER DATABASE [?] SET RECOVERY SIMPLE'

-- Backup All Dbs Except tempdb to C:\temp
SET @cmd2 = 'if ''?'' <> ''tempdb'' backup database [?] to disk=''G:\MSSQL\Backup\?.bak'''

-- Shrink All Dbs Except tempdb 
SET @cmd3 = 'DBCC SHRINKDATABASE ([?], 5)'

-- Execute above commands
EXEC sp_MSforeachdb    @command1 = @cmd1,
                       @command2 = @cmd2,
                       @command3 = @cmd3

 

Download: SET_RecoveryModelShrinkBackup.sql

 

Ivan

del.icio.us Tags: ,

SQL Query: Set All Dbs AutoGrowth

image

Recommendations

The following are recommendations to proactively manage the growth of data and log files:

When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:

ImportantImportant:

You must factor in the performance and operations issues associated with using autogrowth. For more information, see Considerations for the “autogrow” and “autoshrink” settings in SQL Server.

Default Settings

The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in an increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.

 

Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.

NoteNote:  Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrowth as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space. or the db could be 100gb and growth would be 10gb every time the file needed space

Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.

For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.

Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space. Setting autogrowth to use MB verse %  along with changing the autogrowth MB size will create less fragmentation than using the defaults, this SQL Query makes it easier to modify a large number of Dbs.

-- Query to Set File AutoGrowth

SELECT
    DB_NAME(mf.database_id) database_name,
    mf.name logical_name,
    CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, size) / 128)) [file_size_MB],
    CASE mf.is_percent_growth
        WHEN 1 THEN 'Yes'
        ELSE 'No'
    END AS [is_percent_growth],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
        WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
    END AS [growth_in_increment_of],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(DECIMAL(20, 2), (((CONVERT(DECIMAL, size) * growth) / 100) * 8) / 1024)
        WHEN 0 THEN CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, growth) / 128))
    END AS [next_auto_growth_size_MB],
    CASE mf.max_size
        WHEN 0 THEN 'No growth is allowed'
        WHEN -1 THEN 'File will grow until the disk is full'
        ELSE CONVERT(VARCHAR, mf.max_size)

    END AS [max_size],
    physical_name
FROM sys.master_files mf
ORDER BY database_name

 

Download: Set_AutoGrowth4AllDbs.sql

 

Ivan

SQL Query: Move TempDb Files to separate LUNs

image

 

Tempdb Multiple Files

One of the important issues when hosting multiple Content Dbs with multiple terabytes data is to ensure that you have created a TempDB with additional files of the same size and once created move teach of the files to their own Volume.

Do NOT use ISCSI for SQL Dbs

In this case we are using a NetApp SAN with Fiber Chanel HBAs as iSCSI does NOT meet our SQL performance requirements. I Microsoft provides multiple whitepapers against using iSCSI for SQL Dbs. Also, I have seen performance degradation using iSCSI once the ContentDBs reach about 50GB in total size. Using Fiber Chanel I have been able to host multiple terabytes of ContentDbs without any degradation in performance.

Hardware

This server has 2 PROCS with 24 Cores, and 128GB of RAM with tempdb having 1 MDF, 2 NDF, and 1 LDF files on separate LUNS.. We used this script to move the original tempdb files to the SAN and then once provisioned to move the NDF files to separate LUNs.

NAME = Tempdb file Logical Name

FILENAME = the New path with existing filename where you want the file moved

 

 

 

Screenshot of OLD Locations

image

Screenshot NEW Locations

image
-- Query to Move tempDB

SELECT
    name,
    physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'L:\MSSQL\Data\templog.ldf');
GO

SELECT
    name,
    physical_name AS CurrentLocation,
    state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

 

Note: you will need to restart the SQL Server Instance to have the move take affect

 

Download SET_TempDBLocation.ps1

 

Ivan

Microsoft Cloud Roadshow – Los Angeles January 26–27 2016

image

Build your cloud skills in Los Angeles

Join us for a free two-day technical training event for IT professionals and developers that provides best practices and insight directly from the experts who build and run the cloud services across Office 365, Microsoft Azure, Windows 10 and more. Whether you know your way around the cloud or are just getting started, you’ll learn something new and walk away ready to apply your new skills.

Space is limited so reserve your free seat today.

image

Event Details

Map picture

Get Directions

The Venue

The Westin Bonaventure Hotel
404 South Figueroa Street
Los Angeles, CA 90071

Event Particulars

Registration: Check-in location details coming soon
Onsite navigation: The online agenda will include the latest session room schedule
Meals: Continental breakfast, lunch and coffee breaks are provided daily; snacks with beer/wine will be provided at the Ask the Experts reception

Hands-on Labs: Go beyond the session, immerse yourself in a virtual hands-on lab and experience Microsoft’s products and services first hand. Visit http://aka.ms/cloudroadshowlabs.

 

See ya there!!!!

 

Ivan

PowerShell: Run IISReset on All Servers in your farm at the same time

image

 

IIS-Reset.ps1

One of the many things scripts are good for in general  is making repetitive tasks easier and the results more consistent. PowerShell takes it to another level with its intuitive cmdlets . I find it much easier run a script from my laptop or log into a single server rather than using MSTSC to login to every server in the farm, I am working in a SharePoint environment of 25 servers, so it wouldn’t definitely be a drag..

 

 

 

PowerShell Script

<#    IIS-Reset.ps1
Run IISReset on Multiple Servers #>

#Specify servers in an array variable
[array]$servers = "Server1","Server2","Server3","Server4"

#Step through each server in the array and perform an IISRESET
foreach ($server in $servers)
{
    Write-Host "Restarting IIS on server $server..."
    IISRESET $server /noforce
    Write-Host "IIS status for server $server"
    IISRESET $server /status
}
Write-Host IIS has been restarted on all servers

Download http://1drv.ms/1ZjF889

 

Ivan

del.icio.us Tags: ,,

PowerShell: Backup your running Hyper-V Virtual Machines

If your like every one else including me its been a complete drag attempting to get clients to allow you to shut down Virtual Machines that are in production to allow you to back them up.  Windows Server 2012 R2 (a free upgrade of Windows Server 2012) can be upgraded remotely using RDP without too much risk. I competed 4 Hyper-V Hosts Friday night without issue. The upgraded is pretty fast but we had to migrate the running VMs prior to the upgrade, and it too about an hour to bring the  the new 2012 R2 Hyper-V hosts current with all Security and Hot Fixes (132 patches) .

image

 

Set-VMBackup.ps1

We have the backups  using task scheduler, since we have multiple Hyper-V hosts, we created the schedule once then exported to xml, and imported the task on all additional Hyper-V Hosts.

 

 

Also, since the VMs rarely change we keep two backups locally and the rest are moved to the SAN in this case a NetApp for two weeks and copied to tape. Note: You only need to modify the drive and root folder, if the folder does not exist it will be created.

PowerShell Script

<# Set-VMBackup.ps1 #>

#Get date string
$timestamp = Get-Date -UFormat "%Y%m%d"

#Change the Drive / Folder where the exports should be stored
$BackupPath = “D:\VMBackup\$timestamp#Export running VMs to Export path
GET-VM | where {$_.state -eq ‘running’} | Export-VM -Path $BackupPath

Download http://1drv.ms/1PPFoUg

 

Ivan

PowerShell: Upgrade WAC – your Office Web Apps Farm

Like most folks who upgrade their SharePoint 2013 farms after they have applied the latest SharePoint 2013 CUs to the SharePoint side of the environment they will usually still have 2 WAC servers and at least 3 WFM servers left to apply and configure updates. This may depend on release of the updates as the Service Bus and Workflow manager updates do not coincide with the monthly delivery of SharePoint 2013 CUs.

image

 

Upgrade-WAC.ps1

The reason for this post is to make it easy for me (not to forget) updating the WAC Servers / Office Web App Farm. Updating the Office Web App farm is somewhat unique in that you remove the farm prior to the installing the CU then create a new farm after the cumulative update installed


 

 

 

 

PowerShell Script

# Update-WAC.ps1
# Add July 2015 CU

Import-Module -Name OfficeWebApps 

# Review the Current State of the Office Web App Environment
Get-OfficeWebAppsFarm 
Get-OfficeWebAppsHost 
Get-OfficeWebAppsMachine 
cmd /c pause 

# Remove OfficeWebAppMachine prior to installing Cumulative Update
Remove-OfficeWebAppsMachine
cmd /c pause

# Install the Cummulative Update while paused

# Configure Office WebApp Farm after installing the updates
# If using HTTP remove comment below
# New-OfficeWebAppsFarm -InternalURL "http://wac.contoso.com" -AllowHTTP -EditingEnabled

New-OfficeWebAppsFarm -InternalURL "https://wac.contoso.com" -ExternalURL "https://wac.contoso.com" -CertificateName "wac.contoso.com" -EditingEnabled
cmd /c pause

# Open IE to test and ensure the New OfficeWebApps Farm is configured
$ie = New-Object -ComObject InternetExplorer.Application
$ie.Navigate("https://wac.contoso.com/hosting/discovery.ashx")
$ie.Visible = $true
If successful your browser will open and will look the example below

SNAGHTML39a1ce2e

 

Download http://1drv.ms/1JCmcKm

 

Ivan

Metalogix Content Matrix 7.3.x – Bugs

 

SharePoint 2013 and Metalogix Content Matrix

SharePoint is mission-critical and users demand availability. Content Matrix allows you to migrate SharePoint by site collection, site, list, library, business unit, or department with zero downtime. Run old and new farms in parallel and test and re-arrange as often needed. Along with the Re-Organizer feature empowers your site collection owners and users, to manage SharePoint sites, content and metadata on an ongoing basis to keep in sync with business needs. All of my clients love this tool as it makes their environments more flexible and now with Re-Organizer we are enabling the site owners to more easily manage their site structure.

 

 

 

 

Error on Installation of Content Matrix 7.3.0002

During the installation of the Online edition of Content Matrix version 7.3.0002 while installing Metalogix Extensions Web Services you will see the error "MD5 check failed for the current file to be staged, the file may be corrupt" in the screenshot to the left. However, the installation will continue and complete. Also, if you go to the location in your AppData Folder and copy the "Metalogix SharePoint Extensions Web Service Setup.msi" it will run locally without any issues. The error appears to be related to the msi not matching the online manifest

 

 

 

 

Error Copying List Views Content Matrix 7.3.xxxx

Unfortunately, the installation error is not the only issue. The infamous "Object Reference not set to an instance of an object" error occurs when you attempt to copy list views. However, if you copy the complete list all views are included in the copy. In looking at the error details it appears to be caused by a dialog box, this makes sense as it never creates the job or writes to any log.

 

 

 

 

 

image[14]

 

Error Copying Sites Lists or Library’s  Content Matrix Organizer 7.3.xxxx

The last symptom was when attempting to copy / move anything  using the Reorganize from the Widget Drop Down or the Ribbon of the list…

 

We initially found and reported these errors on 12/18/2015..

At first we thought incorrectly the error only occurred after an upgrade from an earlier version of Content Matrix, after further testing we found the issues existed in all 7.3.x versions whether installed as part of an upgrade or installed in a pristine environment. As always Metalogix Content Matrix is our favorite tool for SharePoint Migrations whether on-prem or online and Metalogix support is very helpful.  The only resolution at this time is to revert to a previous version Metalogix Content Matrix version 7.2.0017

 

 

 

 

 

Ivan

Bastille Day – July 14th, 2015 Migration is worth it!!

Windows Server 2003 & 2003 R2 Support is ending July 14, 2015

Bastille Day, Symbol of the French Revolution

What does end of support mean for you? After July 14, Microsoft will no longer issue security updates for any version of Windows Server 2003:

  • If you are still running Windows Server 2003 , you need to take steps NOW to plan and execute a migration strategy to protect your infrastructure.
  • By migrating to Windows Server 2012 R2, Microsoft Azure or Office 365, you can achieve concrete benefits, including improved performance, reduced maintenance requirements, and increased agility and speed of response to the business.
  • Extended support for Windows Server 2003 will end on July 14, 2015, and customers are once again looking at the final months of a generous Windows product support cycle. Recommendations for customers using Windows Server 2003 include the following:
  • Customers that go beyond the termination of extended support place themselves at risks and potentially in a regulatory noncompliance situation. Even if regulatory compliance is not a concern, the security improvements that Windows Server 2012 R2 provides are worth adopting if just to help defend against industrial espionage.

 Key issues if you are still supporting Windows Server 2003 after July 14th 2015:

  • Lack of patches/updates/non-security fixes. No-cost, non-security-related update support terminated on July 13, 2010. However, support for non-security-related updates was available on a for-fee basis to customers that felt it was important to continue to have access to fixes that could help their system run optimally and perform well.
  • Elimination of security fixes. Customers see security fixes as being among the most critical fixes for their installed servers. These fixes will no longer be delivered to customers for their Windows Server 2003 servers, regardless of how severe a given issue may be. This may be less of a problem with many aging Windows Server 2003 applications, mainly because the applications still in use are increasingly likely to be inward facing rather than outward facing.
  • Lack of support. Customers no longer have the ability to contact Microsoft for technical support in the event of a server problem. This becomes particularly important when a system experiences an outage and customers are unable to restore the system and recover data and applications from the stalled machine.
  • Application support challenges. Application ISVs dislike having a complex support matrix and typically support current versions along with a finite number of earlier editions of the product. For most ISVs, an 11 -year-old application is probably already past its rational support life cycle, and in most cases, these application ISVs are about to discontinue or have already discontinued support for aging operating system environments such as Windows Server 2003.

Regulatory Compliance :

  • Customers in regulated industries or handling regulated data, including healthcare and payment card industry (PCI) data, may find that they are out of compliance, which could mean fines or being cut off from key trading partners that seek to protect their own regulatory compliance status.
  • Inability to leverage modern cloud options from Microsoft and other vendors. Windows Server 2003 can run on virtually every hypervisor in the market, but that does not mean it is an equal player in these modern deployment scenarios. For example, Windows Server 2003 installations cannot be re-hosted in a Microsoft Azure environment, unless it is a 64-bit image, but the vast majority of Windows Server 2003 installations are 32-bit solutions. So even if customers bring the 32-bit image to the Azure cloud, they cannot continue using that operating system instance. When spinning up new infrastructure-as-a-service (IaaS) instances in Azure, Microsoft provides catalog images only for 64-bit instances of Windows Server 2012 R2.

Bottom Line:

Security vulnerabilities could arise for which no protection is possible. Attackers who exploit these openings could gain control of systems based on Windows Server 2003. Then use the compromised system to launch attacks from within the data center against other, newer systems to capture and relay data from the network to the attacker outside and introduce false transactions or tamper with legitimate business activities. If such an attack were to take place, it may be impossible or impractical to stop it from succeeding and from being repeated, since the code vulnerability inside the OS will not be patched. Business functions dependent upon the system running under Windows Server 2003 may be unexpectedly subject to complete loss of access to the functionality of the system if it has to be shut down due to compromise. Alternatively, lack of viable alternatives for the business function may dictate that the system remain in operation despite compromises, thus endangering other systems that are not based on Windows Server 2003.

Wow 13 Years who would have thought…

If you have NOT decommissioned your Windows Server 2003 Servers then you should purchase an Extended Sup[port agreement for these servers and actively be in the process of moving to new Hardware and Software… You can use the Windows Server 2003 Migration Planning Assistant which will walk you through a migration assessment and help you choose a Microsoft partner if you need help in the migration and decommissioning of your old hardware.

 

Ivan

First Ever Live Office Bug Bash To Be Held On July 15, 2015 Report Bugs And Win Cool Prizes From the Microsoft Office Team

image

Mark your calendar for July 15 from 10am – 1pm PDT.

You can win Power chargers, USB slap bands, pre-paid run-me tags, universal USB adaptors, t-shirts, hats, stylus pens, Office O365 Pro Plus subscriptions with 25 seats, and lots more by reporting bugs.

GETTING STARTED

Join the public O365 Network

Go to https://www.yammer.com/itpronetwork#/Threads/index?type=algo  and join the O365 Network

After Tuesday, July 14 search for the “Office 16 Bug Bash – July 15” Group. (NOTE: this group will not be public until after this date and requests will not be approved)

 

Beginning on July 15 @ 10am PDT, post your issue using one of the appropriate hashtags (aka Topics) below:

  • Outlook, Excel, Word, PowerPoint, OneNote, Access, Visio, Project, Publisher, Setup, Localization, Install, General, Activation

Custom Testing Activities

Real Time Co-Authoring in Word – Load Testing – 10:00 – 1:00 pm PDT

This feature enables customers who are collaborating on a document to see text changes others are making as well as the location of their cursor within the document. Changes are displayed automatically as people use and update the document. This capability will be available when working in documents stored on OneDrive for Business and Office 365 SharePoint sites.

Localization Testing for European & Middle Eastern Languages. 10:00 – 1:00 pm PDT  and 4:00 – 6:00 pm PDT

Do you use localized versions of Office 16? Do you feel the language quality of the latest builds is good or do you feel it needs some improvement? Well now is your chance to let us know. You will be able to report localization issues in Office 16 for your language so that we can address them before it ships.

All we need during this time is for you to give us the following information:

  1. Office Product
  2. Office Version
  3. Steps to reproduce the issue
  4. Screenshot(s) of the problem
  5. Current offending translation
  6. Suggested improved translation

HOUSE RULES

  1. This is focused on bugs only. If you have already reported an issue and it’s been closed as “Won’t Fix” or “By Design” do not report it again at this event.
  2. Each post/issue must be tagged (aka Topics) with at least one Topic (See Getting Started)
  3. Create a new thread for each issue
  4. DO NOT respond to a thread unless you are adding relevant information to that specific issue
  5. DO NOT initiate any private IMs with MS staff. We will get to your post as soon as possible.
  6. General forum rules and etiquette apply. Please play nice in the sandbox or we’ll have to send a note home to your mother.

FAQ

Q: Who can join this Bug Bash?

A: Anyone with a valid Office 365 subscription that is running the Office 16 Preview bits. Click here to install the preview.

Q: Is this event under NDA or other disclosure restrictions?

A: No.

Q: Can I submit Android, Universal, Mac, or phone issues during this event?

A: No, this event is focused strictly on the desktop bits.

Q: How do I win some of the cool prizes?

A: Find and report a great bug during the bash. Prizes will be awarded based on severity and impact of the issues as determined by Microsoft staff. Prizes will only be awarded during the hours of the event.

Q: What are some of the cool prizes I can win?

A: Power chargers, USB slap bands, pre-paid run-me tags, universal USB adaptors, t-shirts, hats, stylus pens, Office O365 Pro Plus subscriptions with 25 seats, and lots more great stuff!

 

See ya onsite!!

 

Ivan

Next Page »