If you find that once you have configured FBA with SharePoint 2016 that you are unable to do the following with Office 2013 / 2016

Issues

1. Open Office Documents directly from Libraries
2. Export to Excel
3. Open with Explorer
4. Open a site with SharePoint Designer

However, if Office 2010 is installed you will be able to

1. Open Office Documents directly from Libraries
2. Export to Excel
3. Open with Explorer

Also, you will note you click export to excel on the ribbon from a list or library as soon you open the .iqy in excel it’s going to pass the List GUID along with the View GUID to _vti_bin/lists.asmx which due to authentication is going to fail and you will get stuck in an endless authentication loop. By this point you realize that Office 2013 / 2013 Client Integration with SharePoint Server 2016 does NOT work.

If you open fiddler and capture the results of the session you will find that you’re getting a 403 and X-MSDAVEXT_Error: 917656; Access+denied.+Before+opening+files+in+this+location%2c+you+must+first+browse+to+the+web+site+and+select+the+option+to+login+automatically.

There is an obscure security update for SharePoint Server 2016: June 13, 2017 that will allow you to resolve this issue. However, it doesn’t mention the issue of FBA, Office 2013 or any of the issues listed above. But by running the following PowerShell cmdlets you will be able to use Office 2016 and 2013. However, it will break your ability to use the client integration with Office 2010.

Resolution: Open the SharePoint 2016 Management Shell and at the SharePoint 2016 Management Shell command prompt, type the following commands:

$sts = Get-SPSecurityTokenServiceConfig
$sts.SuppressModernAuthForOfficeClients = $true
$sts.Update()

Restart Internet Information Services (IIS). To do this, run the following command:

iisreset /restart

Restart the SharePoint Timer Service (SPTimerV4). To do this, run the following commands:

Net Stop SPTimerV4
Net Start SPTimerV4

Run the following commands to verify that the change is made:

$sts = Get-SPSecurityTokenServiceConfig
$sts.SuppressModernAuthForOfficeClients

The last command should return True.

One of the SharePoint 2016 environments at Rackspace worked and others the SharePoint environment needed to be rebooted…

What made this more fun, is during this period a few of the clients engineers also started modifying their registry to get WebDav working…We had to reset these users registry and clear the webdavs cache for Office and delete the Keys below HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Common\Internet\Server Cache\ then restart the WebClient… Open Services and scroll down to Web Client, right click and choose restart

Reference:

• How modern authentication works for Office 2013 and Office 2016 client apps
• Enable Modern Authentication for Office 2013 on Windows devices
• Description of the security update for SharePoint Server 2016: June 13, 2017
• Making Microsoft Office to Work with WebDAV Server

Its clear that On-Premise environments are NOT getting any love as everyone has access to Microsoft Office 365 and its too easy to focus only on this clients..I’ve been hearing for years that:

On-Premise is dead, LONG LIVE ON-PREM!!!!

Cheers,

Ivan

No Comments

SharePoint Saturday returns to Southern California Pass it on to your colleagues and keep that calendar fresh. We have room for 18 more people!April 8th 2017 will mark the return of SharePoint Saturday Los Angeles. SharePoint administrators, end users, architects, developers, and other professionals that work with Microsoft SharePoint, Azure and Office 365 Technologies will meet for the SharePoint Saturday Los Angeles event on April 8th, 2017 at the Westside Conference Center Pepperdine University located at 6100 Center Drive, Los Angeles CA.  Come join us for some of the leading Engineers and MVPs for a day of this FREE one day conference. Breakfast and lunch will be provided. Register now, to reserve your space. Visit the site for more information on sponsors and speakers. http://www.spsevents.org/city/losangeles/losangeles2017

No Comments

It goes something like this…

Chrome Says “No” to SharePoint

Yesterday, I noticed that Chrome started to flag any access to SharePoint Online sites, including those for Delve and OneDrive for Business, as insecure (Figure 1). This is obviously a problem, so I reported the issue to Microsoft. I also raised the issue on Twitter to establish whether this was a common problem and received several responses that others had seen the same symptoms along with many observations as to the potential root cause. Original Post https://www.petri.com/chrome-makes-sharepoint-look-insecure

But the truth is…..    

I just tested this theory check out the time stamp in the lower right of the following screenshot…

And here is a screenshot of the full Certificate Chain using Chrome

To be fair Tony may have an editor or the Blog host may have held the post for a couple of weeks…. Though I have to say the blog post is much prettier than mine…

Cheers,

Ivan

No Comments

Everyone uses a certificate when requiring authentication on an internet facing site. However it’s surprising how many folks don’t take the time to understand SSL/TLS. Securing SSL/TLS protocols is a pretty common thing to do on any Windows Server running IIS and web applications that uses HTTPS, especially if they require some sort of compliance. It is a good idea to do this on all of your servers in your SharePoint farm, to ensure your secure connections really are secure. It’s also important to note that while I have several SharePoint 2016 environments where I have removed both TLS 1.0/1.1. However, I have not removed TLS 1.1 from the any of my SharePoint 2013 environments. However, all of my clients with SharePoint 2013 are using a HW Load Balancer like the F5 and have offloaded SSL and removed TLS 1.0/1.1 using the HW…

All Microsoft Windows devices using SSL/TLS protocols use SCHANNEL, where you have to install something like OpenSSL on Linux. You may also notice that while OpenSSL has more security vulnerabilities they tend to respond quickly to them. However, Microsoft has been disappointingly slow in updating the cryptography stack in its OS and Applications. Note: there may be flags when running SSL Lab scans against your servers that you may not be able to resolve at this time. This may also apply to the availability of the latest cipher suites as well.

All of the configuration changes to SCHANNEL are stored in the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

The first time I created a GPO to Configure SSL/TLS, and deploy to the farm. I spent a few days with Regedit and reading technet, I recommend using IISCrypto from Nartac to make the changes to ensure the process goes a smooth as possible on your first server then after reboot, exporting the SCHANNEL Key for use with a GPO to automate the deployment for all additional servers in your farm

You can use the following command to export up the SCHANNEL registry settings prior to making the changes and again after for use with the GPO, should you need to restore it: reg export HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\ SChannel-Export.reg

Known issues

There are a few gotchas when making modifications to SCHANNELL on Windows, please QA as necessary in the lab prior to deploying to production:

1. SQL Server used to require TLS 1.0, when you disabled it your SharePoint Servers would not be able to communicate with the SQL Cluster. Please review the information about the SQL updates and additional known issues using the following link TLS 1.2 support for Microsoft SQL Server, then download and install the appropriate SQL Updates. All versions prior to SQL Server 2016 require the updates regardless of Service Pack or Cumulative Update
2. Please make sure you download and installed KB3080079 if you are running a version of Windows Server prior to Windows Server 2012 or RDS/RDP will break when after disabling TLS 1.0 and rebooting. Note: If you are using IISCrypto you may see a pop like the following screenshot after reviewing TLS 1.0/1.1

   

    

3. Older clients > Windows XP and earlier may not be able to connect if they do not support the newer SSL/TLS technologies and you disable the older ones. Out of the box Windows Server is configured to be relatively compatible with older clients, which in turn makes it less secure. You can find a complete browser compatibility list here: https://en.wikipedia.org/wiki/Template:TLS/SSL_support_history_of_web_browsers
4. Qualys will ding you for supporting 1024 bit DHE groups, and will recommend DHE key exchanges be increased to 2048 bit or disabled, but 1024 is the limit on all versions of Windows prior to Windows 10 at this time.
5. Be sure to thoroughly test your applications after making any changes, mainly looking for connection failures over HTTPS. The errors will be listed in the system event log with SCHANNEL as the source

The following configuration works with most modern software (Windows Vista and newer) while providing a relatively robust SSL/TLS configuration, and earning an A ranking on Qualys’s SSL Labs tester.

IISCRYPTO

1. Download IISCrypto and apply the “Best Practices” Template
2. Use The Best Practice Template; Click Templates, Use the drop Down choose Best Practice, then click Apply
3. Disable TLS 1.0 Assuming SQL updates have been applied and KB3080079for RDS/RDP has been applied
4. Disable MD5 under Hashes enabled
5. Click Apply
6. Reboot
7. Test your site with Qualys’s SSL Labs tester

QUALYS SSLLabs Ranking

No Comments

It’s amazing sometimes when you attempt to download a tool from Microsoft and its no longer available for download and a lot of the documentation still available has links that do not work for the download.

The following is a list of tools and their commands that are included in the toolkit. This is mostly copied from the rteadme.htm that is part of every tool

AD SCP Register    

Use this tool to register or unregister a service connection point in Active Directory.

1. Open Command Prompt, by clicking Start, Run. In the Run dialog box, type cmd, and then click OK.
2. At a command prompt type the following command: ADSCPRegister.exe <Operation/> <URLtoRegister>

Using the following information to select the appropriate options for your installation:

• <Operation> specify either unregisterscp or unregisterscp

Example: ADSCPRegister.exe unregisterscp https://adrms.contoso.com/_wmcs/Certification

• registerscp – this operation registers the service connection point specified in URLtoRegister in Active Directory.
• unregisterscp – this operation unregisters the service connection point specified in URLtoRegister in Active Directory.

For <URLtoRegister>, type the URL that you want to use as the service connection point, for example, https://adrms.contoso.com/_wmcs/Certification.

3. Verify that the tool has run correctly by viewing these two objects in Active Directory:

• objectClass=container with CN=RightsManagementServices
• objectClass=serviceConnectionPoint with CN=SCP and the serviceBindingInformation attribute set to the URL specified for the URLtoRegister you provided in step 2.

Usage Notes: The service connection point (SCP) for RMS identifies the connection URL for the service to the RMS-enabled clients that are in your organization. Clients will not be able to discover RMS to request use licenses, publishing licenses, or RMS account certificates without a valid SCP. Incorrectly using this tool could result in users being unable to use Rights Management Services.

Get RMS SCP

Use this tool to validate the current service connection point registered in Active Directory

To run GetRMSP

1. Open a command prompt by clicking Start and then Run, In the Run dialog box, type cmd, and then click OK. or Windows Command Prompt(Admin). Depending on OS
2. Navigate to the location where the tool is saved (C:\Program Files (x86)\RMS SP2 Administration Toolkit\GetRMScp). At a command prompt type the following command: GetRMSCP
   

Example: GetRMSCP

IRM Check

For enterprises that are using RMS with Office System 2010/2013/2016. Use this tool to create an html-based report of the client configuration, Office version, registry keys, and other settings that impact the RMS system. If the RMS-enabled application used on the client computer is a 32-bit application, make sure to use the 32-bit version of IRMCheck in the RMS Toolkit. Similarly, if you are running a 64-bit RMS-enabled application on the client computer, make sure to use the 64-bit version of IRMCheck from the RMS Toolkit

To run IRMCheck

1. On the affected computer, launch IRMCheck.
2. Open File Explorer and GoTo C:\Program Files (x86)\RMS SP2 Administration Toolkit\IRMCheck
3. A report will be created that will give you diagnostic information specific to your Windows® Rights Management Services environment.

Example Report:

RMS Cert Analyzer

Use this tool to check the certificate chain on a given rights account certificate, view rights data and certificate information.

How to use the RMSCertAnalyzer

Using the RMSCertAnalyzer tool includes the following two tasks:

• Specify the configuration information
• Run the RMSCertAnalyzer

To specify the configuration information

1. In the location where you saved the RMSCertAnalyzer tool, use an XML editing tool (or a text editor such as Notepad) to open the RMSCertAnalyzer.exe.config file that was included with the tool.
2. Copy the contents of web.config of the licensing IIS virtual root to CertAnalyzer.exe.config.

To run the RMSCertAnalyzer

1. On the RMS server, navigate to the location where you saved the RMSCertAnalyzer tool (C:\Program Files (x86)\RMS SP2 Administration Toolkit\RMSCertAnalyzer).
2. Start the tool by double-clicking the RMSCertAnalyzer.exe application. The RMS License Analyzer window appears.
3. From file menu, open the certificate file you want to analyze. Once the license is selected, you can analyze it in the following manner. The opened file is displayed in RMS License label.

• The License Information is displayed from the License chain in edit box.
• From the Tools menu, you can select Verify License Chain to validate license signature, trust chain and display trusted domains in the view window.
• From Tools menu, you can select View Rights Data for any license. If the selected license is a publishing license, the tool will decrypt the encrypted rights data and display it in the window. If the license is any other RMS license, the tool displays rights data if the license has any.
• From the Tools menu, you can select License Information to view license data such as issued time, validity time, issuer, issued principal and trust chain from the license.

Usage notes

• License files analyzed using this tool must use UNICODE text formatting. If the license file is saved using the ASCII text format the file cannot be analyzed.
• Using this tool will not make any changes to the licenses and certificates.
• This tool can only be used on front-end RMS servers.

RMS Config Editor

Use this tool to easily view and edit data in the RMS configuration database.

To run RMS Config Editor

1. Open the folder where you saved the RMS Administration Toolkit, open the folder for RMS Config Editor, and then double-click RMSConfigEditor.exe to start the tool. The RMS Config Editor window opens.
2. In Server, type the name of the computer that hosts the RMS configuration database. If the tool is being run on that computer, select localhost.
3. Click the Go button to connect to the specified server.
4. In Database, select the RMS database you want to view from the drop-down list.
5. Click the Go button to connect to the specified database. A list of database tables appears in the left pane of the window.
6. Select a database to view by clicking the table name in the left pane. The contents of the table are displayed in the right pane.
7. If you want to modify a field value, in the right pane, click the field and type a new value.

1. To save the change to the database, click the Persist button.

Note: You must save your changes using the Persist button before you select another table. If you do not, your modifications are lost.

1. When you are done using the tool, click the Exit button.

Usage notes

• This tool can be used on either local or remote computers.
• The names of the computers and the databases that the toll connects to are retained in the drop-down lists until you exit the tool.
• If you have a previous version of this tool installed, you must remove it before installing this version.
• Although designed with the configuration database in mind, this tool can be used to view or edit other RMS databases, such as Logging and Directory Services cache.
• The intended use of this tool is for viewing and editing data that is not exposed by the RMS Server Administration interface. As such, it is limited as to the type of data that it can edit.
• This tool cannot be used to view extremely large databases.

RMS Event Viewer

Use this tool to map RMS log entries to events, enabling the logs to be viewed using the Event Viewer.

To run RMS Event Viewer

1. Open the folder where you saved the RMS Administration Toolkit, open the folder for RMS Event Viewer (C:\Program Files (x86)\RMS SP2 Administration Toolkit\RMSEventViewer), and then double-click the RMSEV.exe application to start the tool. The RMS Log Viewer window opens.
2. In Machine, type the name of the server on which you want to view logging databases by using Event Viewer.
3. In Logging Database, type the name of the SQL server that contains the source logging databases.
4. Click the Step 1: Connect to server button. This creates a connection between the servers and retrieves a list of databases from the SQL server.
5. Click the arrow next to the third drop-down box to see the list of available databases.
6. Select the database you want to view from the list and then click the Step 2: Connect to database button. A list of events is displayed in the RMS Event Log area.
7. Click an event in the RMS Event Log area to highlight it. The logging record related to the event is displayed in the RMS Logging Database area in the left pane.
8. To view the details associated with a log record, double-click the record. The details are displayed in the RMS Logging Database area in the right pane.

Usage notes

• This tool can be used on either local or remote computers.
• The names of the computers and the databases that the tool connects to are retained in the drop-down lists until you exit the tool.
• If you have a previous version of this tool installed, you must remove it before installing this version.
• Gathering events from remote computers may take a substantial amount of time.

RMS Log Analyzer

Use this tool to analyze the log file of your RMS server to track server errors, query for specific users, and other logged events.

How to run RMS Log Analyzer

To run the RMS Log Analyzer, you perform three procedures:

• Create the DRMS_Log_Admin database
• Specify the configuration list
• Run the RMS Log Analyzer

To create the DRMS Log Admin database

1. Save the RMS Log Analyzer tool on the computer running SQL Server where you wish to host the DRMS_Log_Admin database. (C:\Program Files (x86)\RMS SP2 Administration Toolkit\RMSLogAnalyzer)
2. Run the SQL Script file GenerateRMSAdmin.sql to create the database that the tool will write data to.

To specify the configuration list

1. In the location where you saved the RMS Log Analyzer tool, use an XML editing tool (or a text editor such as Notepad) to open the DBConfig.xml file that was included with the tool.
2. Add entries into the file for each of the servers running SQL Server that you want to gather RMS log data from by using the following syntax: <Server HostName=”SQL_Server_name” DatabaseName=”DRMS_Logging_hostname_subdomain_secondleveldomain_topleveldomain_port”> </Server>
3. Once all of the servers that you want to gather data from have been added to the list, save the file and exit your editing application.

To run the RMS Log Analyzer

1. On the server where you created the DRMS_Log_Admin database, navigate to the location where you saved the RMS Log Analyzer tool.
2. Open a command prompt by clicking Start, and then Run. In the Run dialog box, type cmd, and then click OK.
3. At a command prompt, navigate to the location where the tool is saved and type RMSLogAnalyzer.exe.
4. To stop RMS Log Analyzer, press CTRL+C in the RMS Log Analyzer command prompt window.

While the tool is running, you will see the following progress indicators in the command window.

During the first run of the tool, it will process up to 20,000 log entries from each database you listed in the configuration file. If your databases contain more than 20,000 log entries, repeat this step until all of the logs are processed. Subsequent runs of the tool will process 5,000 log entries.

If a processing error is encountered while processing a log entry, the logID and the exception are displayed in the command window. The database will reflect the error by creating an entry for the logID and the related data will contain the text “RMS Admin processing error.” This error will not stop the processing of subsequent log entries; the tool will continue to the next entry automatically.

If you want to automate the process of starting RMS Log Analyzer, you can configure it as a scheduled task so that Log Analyzer is started every time that the system is started.

To run RMS Log Analyzer as a scheduled task

1. On the server where you created the DRMS_Log_Admin database, click Start, click Control Panel, and then click Scheduled Tasks.
2. Double-click Add Scheduled Task, and then click Next.
3. Click Browse.
4. Navigate to the location where you saved the RMS Log Analyzer tool.
5. Type RMS LogAnalyzer for the task name, click When my computer starts, and then click Next.
6. Enter credentials of a user on the network who has at least read access to the RMS Logging database, click Next, and then click Finish.

Usage notes

• In previous versions of RMS Log Analyzer, RMS Log Analyzer had to be installed on the SQL Server. In RMS Log Analyzer Service Pack 2, a command-line option was added to pull data from remote SQL servers. To use the new command-line option, type RMSLogAnalyzer.exe /ALTDB <SQL_Server_Name> at the command prompt.
• Using this tool will not make any changes to the source RMS logging databases.
• One DRMS_Log_Admin database can be used to store any number of RMS databases so long as the full logging database names are unique.
• The Windows RMS Newsgroup is a great place to ask questions of other RMS users and find general information about other user’s experiences with RMS. You can subscribe to the RMS newsgroup from the Microsoft newsgroup server (http://go.microsoft.com/fwlink/?LinkId=74714).

RMS Queue Recovery

Use this tool to recover logged events from the MSMQ dead letter queue.

To run RMS Queue Recovery

1. Open a command prompt by clicking Start and then Run. In the Run box, type cmd, and then click OK.
2. Navigate to the location where the tool is saved. At a command prompt, type the following command, replacing the parameters with the appropriate values for your installation: LogRecoveryCmd <operation> <queue name>

The tool can perform the following operations:

When specifying the <queue name> parameter, you must use the direct format, for example, .\Private$\Drms_Queue.

Following are two examples of how to use this tool on a server with a local logging database. These examples assume that the queue name is .\private$\drms_logging .

• To resend all messages from the dead letter queue back to the logging queue, type:LogRecoveryCmd.exe resend .\private$\drms_logging

Usage notes    

• This tool must be run on the RMS server.

RMS Service Locator

Use this tool to provide a report of all the URLs that RMS uses.

To run RMS Service Locator

1. Open a command prompt by clicking Start and then Run. In the Run dialog box, type cmd, and then click OK.
2. Navigate to the location where the tool is saved (C:\Program Files (x86)\RMS SP2 Administration Toolkit\RMSServiceLocator). At a command prompt, type the following command: RMSServiceLocator

The tool will run and will display the services available for the server to enroll with.

Usage notes

• This tool must be run on the RMS server.

Download RMS SP2 Administration Toolkit – https://1drv.ms/u/s!AqPEeJPr6wHFlPAiLhfZOIS_kZ4qJw

No Comments

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

No Comments

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:

Important:

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.

Note:  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

No Comments

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

Screenshot NEW Locations

-- 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

1 Comment

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.

Event Details

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

No Comments

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

No Comments