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=220.127.116.11 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 !
You want to move your Windows Azure solution Web Site from your subscriptions to the others
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@example.com and my target subscription use the firstname.lastname@example.org . 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$)