If you have SQL Server database for a long time and you have database on the cloud also like SQL Azure or SQL Server RDS. You might want to understand how the data can help define, redefine, or build your business. Data driven decision or DDD for short is a way to make a business justification based on the data. DDD can be applied by using Business Intelligence platform such as SQL Server and Microsoft Power BI. SQL Server is used to collect, store, manage, and report the data insight. While Microsoft Power Bi helps you to design, develop, and visualize your data into a dashboard.
Power Bi or SQL Server BI
IF you have various data source THAN you can use Power BI (Premium version).
IF you have SQL Server data source THAN you can use SQL Server BI Platform
On this article, we will discuss the SQL Server BI Platform4
SQL Server BI Platform
SQL Server BI platform is Business Intelligence platform before the cloud era happens. What a good of SQL Server BI Platform is you can manage all of the BI process in your on-premises infrastructure. This is somewhat difference with Power BI premium that need Power BI services that should in the cloud. Beside of that. SQL server BI platform is a complete solution. You will have end-to-end BI service with SQL Server BI Platform. In contrast, Power BI doesn't manage the storage management or database engine except a storage that embedded in .pbix file.
Today you can run the SQL Server BI Platform workload in the cloud. For example, you can run the entire workload in VM or you can use data analytics managed services with Azure Synapse Analytics, Azure Analysis Services, and many more. Moving to cloud like Azure is a plus, because you can manage the grow of your data linearly and you don't need to manage underlaying infrastructure, patching, or updating.
If you start from scratch, I recommend you build your solution in the cloud. It is cost effective and easier to manage
Getting Started with BI Platform in On-Premises Environment
Imagine you have SQL Server database in your organization. How do you start your BI platform journey? What should you install? An how to setup the BI platform right away? You should understand what you need to install first. This documentation tell the BI component in SQL Server Install Business Intelligence Features - SQL Server | Microsoft Docs
//
SQL Server Analysis Services. This service will help you to analysis the data in memory, in multidimensional storage, or in pivot model.
Data Quality Services. It will help you to measure, to perform data cleaning, and integration. This tool is included in Integration services.
Integration service. It will help you to do extract, transform, and load into your SQL Server
Master data services. It will help you to centralize and to improve consistency of your organization data. It will eliminate the redundancy and inconsistency of the data.
Reporting serves. It will help you to generate, render, and store the report for you database
How it Works
Let's make it simple by using a simple example. Imagine, you have lot of database that related with academic stuff such as student performance, library database, courses, lecturer, and many more.
Create the database just like what it should be with SQL Server
Use Master Data Service to find the understand the similar data in your databases.
Use data quality services to make sure the data in health state
Use integration services to integrate your database into data warehouse or big data
Use analysis service to analysis the result of integration services.
Use the reporting service to visualize and to create the report of the database
As a bonus you can use Power BI to create report and dashboard
Microsoft Offer in BI Platform
You can see the BI platform offer by Microsoft
//
The Fun Fact about the data
When we build anything, we use data. Start from structured data, unstructured data, and semi-structured data we store the data to retrieve it as information and knowledge. Despite of the data usage, we know that the data in our life is growing. And when we can't store the data in the local storage the cloud is the answer. The question is how we store and manage the data in the cloud. This article will discuss how we store and analyze the data in the cloud era. You can read the data concept here
The Data Store
You can store the data in two types relational data or non-relational data.
In non-relational data you will have Azure Cosmos DB, File, Blob, and many more. You can learn more here
In relational data you will have the power of SQL Azure, as well as MySQL, Maria DB and any others database. You can learn more here.
If you need high volume transaction without than the Non-relational data is for you. However, for small and tight relation between data you need the relational database such as SQL Server. You can learn more the consideration here.
The Data Analytics
medianet_width = "600";
medianet_height = "250";
medianet_crid = "858385152";
medianet_versionId = "3111299";
After the data is stored, you can analyze the data for more useful manner. This step knowns as analytics. According to Microsoft they have several products which are?
Azure Data Factory who take any data and convert it into format that you need. The ETL process heavily happen in this Azure Data Factory
Azure Data Lake who store raw data to ready to retrieve as fast as it can. Azure Data Lake is the main storage for Azure Data Factory
Azure Databricks is a tool to provide big data processing, streaming, and machine learning. It can use data lake as a data source
Azure Synapse Analytics is an analytics engine. It is designed to process large amounts of data very quickly. Azure Synapse Analytics supports two computational models: SQL pools and Spark pools.
Azure Analysis Services enables you to build tabular models to support online analytical processing (OLAP) queries. You can combine data from multiple sources from the data lake, cosmos DB, and off course SQL Azure
Azure HDInsight is a big data processing tool based on well-known platform Hadoop.
You can learn more about analytics here. After you have analytics you can pull it into dashboard or report by using Power BI.
medianet_width = "600";
medianet_height = "250";
medianet_crid = "858385152";
medianet_versionId = "3111299";
Problem
You have an old database (i.e. SQL Server 2012) from the production. After that, you restore it into SQL Server 2016. After your work, you restore it into your production server to SQL Server 2012. Your restore fail.
medianet_width = "600";
medianet_height = "250";
medianet_crid = "858385152";
medianet_versionId = "3111299";
Causes
The SQL Server doesn't support the downgrade version. After you place into a new version, the backup is upgraded into a new version. Therefore, there is no way to restore into low version.
Possible Solutions
Aligning SQL Server version between your production server and development server
This is the most preferred way when you only manage one project with the same database version. If you work for multiple projects with the different SQL Server version, it is not recommended.
Generate Scripts
This is the most preferred way when you have access to the scripting tools. You will the detailed step here
Using Data-Tier Application
This is the recommended way if you have access to SQL Server Management Studio or using SQL Azure. It supports the 2008 version or higher. You can find the detailed step here
Using Third-Party Tools
You will have to find many tools such as ApexSQL Diff or ApexSQL Data Diff, You will find the detailed step here
Which method that you like the most? Please comments.
medianet_width = "600";
medianet_height = "250";
medianet_crid = "858385152";
medianet_versionId = "3111299";
As a part, of our commitment to create a local video tutorial on Visual Studio Technology. We created a beginner tutorial for ASP.NET MVC with Visual Studio 2015. There are 8 videos on the plan. You can visit YouTube to join the Visual Studio Indonesia YouTube Page. Or enjoying the video below
Building an aplication that use SQL Server means that we give the SQL Server an authority to store and manage the data. In order to make sure that the data always available and secure, we need to take care the configuration that will help the development team to make sure the system and secure and available. Let’s start the tips Tips 1. Physical Security Put the server on a room with sufficient security (locked), secure from power surge, and of course have a backup power. Limit the access with external pheriperals such as external hard drive, usb and others. If you want to backup consider to use backup tape, secure external hard rive, or even cloud storage. Please appendix about recommended hard drive to store the application. Tips 2. Operating System Security We need to configure our system for SQL Server cimmunication such as TCP / IP Communication and Maintenance plan. Choose the right roles for your Windows Server 2012 Configuring database engine firewall access Configuring others sql server service firewall access Activating Windows Update for Important update only. It makes your operating system focuses to deliver the good bandwidth performance rather than just download the huge package update. You can also turn it off and wait for service pack, but it’s not recommended. Tips 3. SQL Server Security SQL server has a lot features that related security. I recommends you to do several installation and configuration below: If your application is built for .NET and run on a same network, we recommend you to disable user authenication using user name and password. it means you just need to use Windows Authentication. However, if your application work across domain or even internet, you should activate it. Turn the sql browser off. It makes the other client application can’t scan and can’t find your sql server except you give them the server name or the IP. Use TCP IP address to access the server. Please consider to never use the named pipe with default name, You should changes the server name and use TCP IP binding for external access. Move the default location of the database files when you install the database. It is good idea if you move the database location into the others location. There are plenty of security features for SQL Server such as authorization, encryption, port access. Furthermore, you can see the detail here Tips 4. Application Security In order to make your application and SQL server secure you should consider following actions: Validate any inputs on your application to prevent SQL Injection. Using Stored Procedure is recommended. Limited access to SQL Server through one account for one application. It’s bad idea to create a lot SQL Server users for one application. Consider use only one users with least previllage. Never put server information when your codes is error. Some of developers, give the information about server name or database name when the errors happen. Never put the detail of your server on your screen. Use connection string builder, to ensure the sensitive data such as password is not stored on plain text on your web,config. Here are the sample codes Tips 5. Social Engineering Security This is the most effective security to make your application bullet proof. the tips are Strong password for your SQL Server access. Changes password at least two months for server access. isolate your server, open the RD access through local lan only. That’s it, if you have others idea, please put through a comment.
On this article, we will show you several tips to prepare and to plan data aware application with SQL Server 2014. The tips should be considered if you want to make your application evolve with the database technology as well as technology trend like a cloud. Tips 1 – Avoid Create a SQL Login Directly as the user It is not a good idea to create an user that has direct access to sql server. It will be great if your application uses Windows Authenticaon (if the application on the same machine) or SQL Authentication with only several user role. Never put end user as a sql login database. It will make you harder to maintenance your database. Tips 2 – Avoid Views on the database Views not only will make your database consumes more memory, it will also make your database wont be compatible with the cloud environment like Azure. So drop your Views, use your Stored Procedure instead. Tips 3- Never Shrink Your Database if less than 10 GB If you think that shrinking database will help your database performance. Think it again!, shrink a database will make your database highly fragmented. Please consider shrink the database if larger than 10 GB and you follow the shrink method with the rebuilding index. Tips 4. Creating a maintenance Plan Fixing database is the last option, please create maintenance plan to make sure your database work on optimal situation. Take a look the Maintenance Plan tips here Tips 5. Stored Procedure is recommended way If you have intensive data transaction, please think to use SP rather than direct access via ADO.NET, LINQ2SQL, or ORM. Tips 6. Backup your database to the cloud You can backup your database to the cloud to make sure your backup is well stored. we will discuss this backup model on the next post.
Background Upgrading is easy when we talk about one level upgrade. For example, upgrading from 2012 R2 to 2014 will be seemless. However if you upgrade from SQL Server 2000 to SQL Server 2014, you will need some tips to make the database run smoothly. This article will guide you to do step-by-step approaches. Patching your SQL Server 2000 to the latest service pack (i.e. SP4( Backup your SQL Server 2000 Database from your original machine Install SQL Server 2008 R2 into other machine Restore your backup from SQL Server 2000 to the SQL Server 2008 R2 Backup your SQL Server 2008 R2 database into a .bak file. You can set the compatibility backup to SQL Server 2008. Create a new database on SQL Server 2014 and restore to the that data. Please do overwrite and cut the tail log to make the database clean. Advanced Scenario The above step is sufficient if you upgrade a simple database. However, If you have complex schema rather than dbo schema. For example, a schema based on username access. you should also script the other object. In order to do that, follow this step Open your SQL Server Management Studio Right click and generate script Click next to continue the step. On this step, Select the database roles and schema. Confirm it with next On the next step, select save to file and click advanced set true for script login, script object ;evel permission, and script owner. Next to generate script and restore the script on the upgraded database. That’s it. If you have mpre question, please feel free to drop the comment below.
Problem As an Admin, you want your life easier by creating automatic task on your database server. However, you don’t have a good best practices how to create a good maintenance Solution Preparation Phase In order to create a better maintenance plan you should have at least Two hard disk, the first hard disk is for transactional and the second hard disk work as a backup. Having one hard disk and doing a backup on the same hard disk is somewhat strange An email account that broadcast every update from the maintenance plan on SQL Server 2014. This email address should have SMTP access. Email recipient list that will receive a report from your server Activate your sql server agent (Agent XP) and Database Email Executing Phase SQL Server already has a maintenance plan since 2000. This table will help you how to create better SQL Server 2014 maintenance plan Task Small and Medium Size Database / Transaction Big size database / Transaction Reason Shrink database NEVER MANUAL it makes your database highly fragmented. after this action please do rebuild index Full backup Daily Monthly or Weekly use another hard drive to do this kind of backup such as upload it to azure Differential backup Hourly Weekly or Daily Transactional log Optional Hourly Rebuild index Monthly Weekly best combined before full backup Reorganize Index and Update statictic Optional Weekly or Optional Optional if you already do rebuild index Cleanup maintenance task Optional Weekly or Monthly References Some good books that can you use for this purpose
Problem You uninstall SQL Server 2012 and install a new SQL Server 2014 for your development machine. However after the installation of SQL Server 2014 you get an error like below Could not load file or assembly Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0 And you can’t add the data connection on Visual Studio 2013 Cause This is because you remove the entire installation of SQL server 2012. Visual Studio 2013 need the installation of some of SQL Server 2012 library Solution Simply download the latest package of this library to fix the problem Microsoft SQL Server System CLR Types Microsoft SQL Server 2012 Management Objects Microsoft SQL Server 2012 Native Client After you download the package, please close the Visual Studio and do the installation. You done !
Problem
You want to move your Windows Azure solution Web Site from your subscriptions to the others
Solution
Solution A. Make Sure it has same service administrator
If you have a same service administrator email between your source subscription and the target subscription, you just contact the support to move the web for you. For example, my subscription A used my
[email protected] and my target subscription use the
[email protected] . if the condition exists just contact Azure Support Here
Solution B. Move your Web Manually
This solution has several steps but it worth to try if you don’t have same account between services administrator.
Configure SQL Server so that you can access it through SQL Server Management Studio, you can do that by following these steps
Connecting your SQL Azure using SQL Server Management Studio. This tutorial shows you how to do that
Select your database, select the context menu, select tasks, and then copy the database to the azure SQL database. Make sure you are using SQL Server 2012 management studio and both SQL Azure is allowed to connect from your IP Address
Open your website configuration and select dashboard, in the right pane, you can see the FTP and the credentials. if you forgot the password of your FTP just select. Reset your deployment Credentials
Use an application like FileZilla or others FTP client to connect to the server using the login that you get from the source dashboard. Download the /wwwroot folder to yours local file
After finished, open the web.config and configure the new connection string from your target database. Be sure to changes and option that related with the servers
Now create a new website site in your target subscriptions and connect to the FTP services using your FTP client
Transferring your files, and your web site is migrated
That’s it, if you want to learn azure more you can grab several good book like below (and it less than 15$)