Install Windows SQL 2016 EnterPrise on Azure Portal (Cloud) step by step with screen shots

Today we are showing you about how to install SQL server 2016 Enterprise Virtual machine using Azure. Cloud server is giving facility to us host our machine on the same. Azure hosted machine speed and performance will be much better than hosted server also the Net speed on azure servers will be faster than all time.

We are creating with our Azure subscription, if you don't have an Azure subscription, you can create a free account  in just a couple of minutes.

• Login to the Azure portal site using your subscription id and password

https://portal.azure.com

• Once login to the azure portal dashboard will be coming on the page
• Now Click on new option from Virtual machines then Select server same as below screen shot:

• Choose the VM image from the marketplace or here as mentioned above

Currently we are creating Virtual SQL 2016 server (Using windows server 2012 R2 Datacenter)

• On the Hub menu, click New  and select  Virtual Machines then select Windows SQL 2016  Enterprise on Windows server 2012 R2( windows server will be 2012 now 2016 already release so you can go ahead with this also)

• On the SQL server 2016 Enterprise page, under Select a deployment model, verify that Resource Manager is selected. Click Create to create the same

Install the MS SQL server 2016 Enterprise virtual machine using MS windows 2012 R2

After you select the image, you can use Azure's default settings for most of the configuration and quickly create the virtual machine.

• Few of tips are here: On the Basics blade, enter a Name for the virtual machine. As per azure policy the name must be 1-15 characters long and it cannot contain special characters.
• User name assign which going to use Enter a User name, and a strong Password that will be used to create a local account on the VM. The local account is used to log on to and manage the VM.

• Select an existing Resource group or type the name for a new one. Type an Azure datacenter Location such as West US.
• When you are done, click OK to continue to the next section.

• Choose a VM size and then click Select to continue.

• On the Settings blade, you can change the storage and networking options. For a first virtual machine, we can generally accept the default settings. If we selected a virtual machine size that supports it, we can try out Premium Storage by selecting Premium (SSD) under Disk type. When we are done making changes, click OK.
• Specify Server settings

• Storage account
• Network.
• Subnet.
• Public IP address.
• Network security group.
• Monitoring and Availability

• Click OK to continue.

• Specify SQL Server settings

• SQL connectivity.
• SQL Port
• Storage configuration
• Automate patching
• Automated backup
• Azure Key Vault integration

• Click OK to continue.

• Review your configuration in Summary page and click OK to deploy SQL Server 2016 on Azure VM before going for finish the same

• Click Summary to review your choices. When you're done, click OK.

• While Azure creates the virtual machine, you can track the progress under Virtual Machines in the hub menu.
• Once click on create button, IT will start creating the server

• It will show the deployment running status at left corner, so now wait till the completion
• Once installation done it will completion message

• Connect to the virtual machine and log on

Thanks ☺

How to shrink the size of a transaction log (.LDF) in SQL using SQL server Management Studio 2016

Today we are writing about how to shrink the size of a transition log (.LDF) for SQL database.

It can be a cause performance issues, also it will take a lot of space up valuable disk space and consume resources when you back up. Therefore it's imperative to periodically maintain the database to keep the .ldf file under control.

Just example we have SharePoint server also we have many of content Database and PWA site database as well which will get increase always which we can control using script.

We need to follow below steps to complete the task:

• Connect the SQL server instance from SSMS
• We need expand the Databases node and expand User Databases

• Right-click the database, and click Properties, which opens the Database Properties dialog box.
• In the Select a page pane, click files.

• Now copy the file name which is ending with .LDF, and do the same for all Database log file and save in the note pad.
• Now click on Management option from SQL instance in left side and expand the same.
• Expand all Database name and right click on the same then create new Query.

• Then run below command on the new query page then run below command with log file which you have copied earlier.

USE "Sp2016_SharePoint_ConfigDB"

GO

ALTER DATABASE "SP2016_SharePoint_ConfigDB" SET RECOVERY SIMPLE

GO

ALTER DATABASE "SP2016_SharePoint_ConfigDB" SET RECOVERY FULL

GO

DBCC SHRINKFILE ("SP2016_SharePoint_ConfigDB_log",1)

• Change the highlighted and click on execute or press F5 and the see the result

• Check your log file before running and check the same after running successfully.

• After running the above SQL shrink file command output will be like below:

• Now see the content DB space.

Thanks ☺

Migrate Content Database From SharePoint 2013 to SharePoint 2016

Today we are writing about how to take content Database and restore the same to SharePoint 2016.

We are migrating SharePoint 2013 Content Database to SharePoint 2016. We have the case summary for the same, just example we have a content database on SP 2013 site which we want to migrate the same Site collection and information on new environment on SharePoint 2016.

To do this follow the below Steps:

• Before doing make sure you have Farm administrator rights with your user account.
• Login to SharePoint 2013 environment which you want migrate on SharePoint 2016. Then click on SharePoint central administrator 2013 and define which content Database you are going to migrate and find the SQL database Name as well.
• We need to verify App Management which we want to migrate from the same

To do above: Login CA>Application management>Manage Web Application> defined the Web App.

Now we need to go back to view all site collections under the Application management, and define web application and select which you have define earlier from Web Application. Below the screen shot:

• Go back to SQL server and take backup for Database and save in local system. To take full backup of Database using SQL server management Studio see below URL:

https://sharepointsolutiontips.blogspot.in/2016/11/how-to-take-full-back-up-of-database.html

• Once backup done follow below URL, we have posted recently about restore the DB.

http://sharepointsolutiontips.blogspot.com/2016/11/how-to-restore-database-from-stored.html

• Now go back to Central admin site and check Managed content database and you can see still we could not find any database on the same.
• Go attached Content database we need to run restore or mount and attach database to see the result.
• Run the following statement via SharePoint Management Shell:

Mount-SPContentDatabase "your_db_name" -DatabaseServer "your_sqlserver" -WebApplication

• See the below result how it would be: maybe it we will run all command together we will getting some errors, so we can run like below as well.

• Now go back to Central admin and select Manage content Database from Application management and Database options.

• Go back to Site collection and select view all site collection and verified all site has been created and opening successfully with all data.

• Now open the created site or migrated site and verified site is getting open.

How to Restore Database from stored backup database using SQL Server Management Studio

This topic explains how to restore a full database backup using SQL Server Management Studio. When we are restoring a database from other instance.

To restore the database we need to follow below steps:

• Copy Backup database to SQL server or any of Physical drive.
• Connect to an instance of the SQL Server Database Engine and then expand that instance.
• Right-click Databases and select Restore Database options like below:

• Restore database wizard will get open select path and select backup database with .bak and click on ok.
• On the file options select Log and Database path where you want to save.
• On the Options path leave all default or modify as your requirement and click ok start recovery the database.

• Once if you click on ok, backup database will showing on connected objected SSMS server.

See more about restore from technet:

https://msdn.microsoft.com/en-us/library/ms177429.aspx

Thanks ☺

How to take Full Back up of a Database Using SQL Server Management Studio.docx

Today we are writing about how to take full backup of a database using SQL server Management studio.

Make sure you have Database admin and backup rights to login and accessed user.

Below are the steps to configure the same:

• Open SSMS (SQL Server Management Studio) and connect the instance from where you want to back up the database.

• After connecting instance of SQL server, expand Database and select database and right click on the same then click on backup under the task options.

• Once you will click on the backup, it will take us backup wizard, we need to add destination path by clicking on add button. And add path with .bak.
• We can define the backup time also, from the backup options tab left side of backup wizard, currently we are selecting 0days to take immediate backup.

For more information check technet link:

https://msdn.microsoft.com/en-IN/library/ms187510.aspx

See below:

• Once all done click on ok, backup will get start and will save at the given path.

Thanks ☺

How to View or Change the Default Locations for Database and Log Files Using SQL studio latest version.

Today we are writing about how to change Default location for Data (.mdb) and Log file (.LDF) file in SQL server using SQL studio Management Studio. Below is screen shots is from SQL 2016 instance.

• To view Database default location Connect SQL server using SSMS.

• Once SQL server will get connect, Right click on Connected SQL server and select Properties.
• On the SQL Properties, Select Database setting option from Left side of opened wizard and verified the default location for SQL database.

• If you wish to change the same on different drive, select Locate folder the option and define the path where you want to save the same.

• From here you can change path.
• It wont take any effect some time, we need to restart SQL services from services.msc and find SQL server and restart the application then check the same.
• We can restart the SQL server services from SQL Manager as well.

Thanks 

Step by Step- How to Rename Domain Name in Windows server 2016 safely with screen shots

In this article, we are showing you how to rename Domain Name in windows server 2016 with screenshots, This we have done for our lab Environment. So before doing for your production server take a backup for important file. We prefer do not use this until unless you need it.

Refer our recent article about "Step-By-Step Installation of Windows Server 2016 with Screen shots" and "How to install Active Directory on Windows server 2016 Step by step with screenshots".

Below are the URL for above: 

Installation of Windows Server 2016 Step by Step

Installation and Configure Active Directory on Windows Server 2016 Step by Step

So we will start doing this step by step, so here we will start by login to the Domain Server where we want to change DC name.

Make a note what name you want to rename and configure for DC.

• Login to the server and verify first computer name and DC which we have currently and we will change dc Name with easy step.

Below are the screenshot for current server DC name and computer name in our lab environment.

Note: we are performing this for Server 2016 Technical Preview, The same steps will be for released version. Once it will release we will update the same with new Screenshots and new experience .

• Open server Manager, by clicking from Taskbar or it will start automatic of once system will start. Here we will select DNS from Tools option in Server Manager Dashboard.

• Click on DNS and select your server from DNS Manager and right click on server DNS then select new Zone.

Note: we must create new DNS Zone with new name which we want rename from Existing DC server. This is to make sure that after whole process successfully, your member server @ Windows clients can join to new Domain name.

• New Zone wizard will Open, click on next

• Select Primary Zone and click on next.

• On the next page, Select below option do next.

• On this page we will select forward lookup Zone

• On this wizard, it will ask for New Zone Name, so here provide the name which we want to rename from the DC server name and click next.

• On new page, select below option and as this recommend for AD and click on next.

• Once all done it will come for summary page, verify all and click on finish.

So here we have created new DNS Zone effectively, now we will proceed for next step to rename the DC name. To do this we will start from Command prompt.

• Open command prompt with administrator rights

• So we navigate to login account using below command, once we will navigate to the page. We will run Rendom /List command. This command use to generate a state file named Domainlist.xml. This file contains the current forest configuration. Once enter it will generate one .xml file on navigated page.

• Here will edit this page, and enter new DC and BIOS name from exiting and we will save this.

• Here the exiting Domain and DNS name also BIOS Name.

• We will change it to new DNS name and BIOS as we want, below are the screen shot what we have changed to : PrashantLab.com

• All done we will save and exit from opened Notepad. And we will back to command prompt, here we will enter Rendom /ShowForeset. This is to show the potential changes; this step does not make any changes.

• Above command will not change anything, to be continue we will run below command to upload changes Domainlist.xml file. Enter Rendom /Upload, This is to upload the rename file (Domainlist.xml) to the configuration directory partition on the domain controller holding the domain naming operations master role. Once all successfully we will proceed next step.

• Enter random /Prepare command and hit enter. This will verify the readiness of each DC in the forest to carry out the rename instructions. This should no error result.

• Once this command will enter it will disturb current stage of server, and It will prompt about restart the server. See below screen same prompt will come will restart the server.

• Once you’re DC Server restarted, log in using the new Domain name as administrator. And we will verify by going system properties of server and we will see the domain name has been changed. But still full computer name is with old domain name. See the below screen.

• To refresh the DNS name and Gpupdate we will run few of commands, so again open command prompt with admin rights and we will run below command. It will refresh all intradomain references and links to group policy objects.

Note: Change this command as per your name of DC new and old.

gpfixup /olddns:SPSolutions.com /newdns:Prashantlab.com

• Now we will enter below command with updates as above. In our case we have not updated BIOS Name so it wont run. We will go next command called Rendom /Clean remove references of the old domain name from AD.

gpfixup /oldnb:Prashant /newnb:Prashant

• Now the last command will be there to exit from random, Enter random /end.

• Now open, DNS manger again, by clicking server manager and tools or dns on search. Here we will verify the new created zone which we have created first time, this will have all entery same as old Zone. Refer below screen shots to know more.

• Old Zone will transfer all information to new zone.

• Next we will show how to change AD name using command prompt.

• To connect other client PC with this New DC name, make sure firewall should off and IP address should be on server IP range. First try to ping to DC name if pinging then we can connect those systems.

See more on this link: https://msdn.microsoft.com/en-us/library/bb727049.aspx

Thanks,

Hope this will help some one ☺