Edi Wang

Microsoft MVP for Azure

SQL Server

PowerShell to Enable SQL Server Express Remote Access

SQL Server Express is a free edition of Microsoft's SQL Server, which is a relational database management system (RDBMS). However, remote access is not enabled by default on this SKU. To access a SQL Server Express instance from the network, we have to do a few steps. There's plenty of guide on the internet telling you how to enable remote access via GUI, but I would like to do it from PowerShell. …
SQL Server PowerShell

Automated Deployment Script with Azure CLI and PowerShell Core

My blog project, "Moonglade", is optimized for running on Microsoft Azure. It can combine up to 14 Azure services together for a full deployment, manually setup is difficult and takes time. I chose Azure CLI with PowerShell Core to write a deployment script that enables a quick on-board experience to setup Moonglade in 10 minutes with minimal Azure Services. Let's see how it is done. …
SQL Server Azure App Service

Import Data to Azure Storage Table from SQL Server

Recently, there was a demand to switch data storage from SQL Server database to Azure Storage Table. However, neither SSMS nor Azure Portal provide a direct import function. You may now consider writing a tool to import data, but actually it isn't necessary. I still managed to do it within mouse clicks. But the first thing to warn everyone is that relational databases like SQL Server are not …
SQL Server Azure Storage

SQL Server Export Data Tier Application via Command Line

We can use SSMS to export data-tier application for an SQL Server database. I've been using this method for several years on my local machine. However, to make things work under automated environments, like CI/CD environments, or some automated scheduled tasks. We usually need to do it under the command line.  Microsoft has provided a cross-platform command-line tool that can import/export DAC: …
SQL Server DAC

Merge Data for N-N Relationship Tables in T-SQL

Recently, I have been organizing my blog data and needed to perform an operation to merge article categories. On my blog, articles and categories have a many-to-many relationship. That is, one article can belong to multiple categories, and one category can contain multiple articles. This is a very typical many-to-many relationship, and I use a many-to-many table with a composite primary key to associate this data. What I need to do is move articles from the "DotNetBeginner" category to the "CSharpAndDotNet" category. However, since some of the articles originally in the "DotNetBeginner" category also belong to "CSharpAndDotNet", directly updating the association table would result in duplicate composite keys, which would cause issues.
SQL SQL Server

图解:如何将SQL Azure数据库备份到本地SQL Server

用了两天SQL Azure后,发现SQL Azure数据库没有办法做传统意义上的“bak”备份,但我们确实很需要能够随时将数据库备份到本地,以更灵活的使用。所以今天就开荒了一下操作方法,并分享给大家。这样一来,我们既可以把SQL Server迁移到SQL Azure,又可以反过来把SQL Azure撸回SQL Server,这是一个比较完整的装逼过程,妹子们一定会赞不绝口。 步骤如下: 1. 启动SSMS2012,在目标SQL Azure数据库上点右键,选择“Export Data-tier Application”。 2. 在Export Settings选项卡里制定本地的备份路径,现在已经是“bacpac”拓展名的了。。。 3. 然后点击Next,确认后就向导就开始撸了。 4. 撸完以后会显示Operation Complete的消息,现在本地备份文件已经创建完成了。 5. 在 …
SQL Server SQL Azure Azure

Store and Read Images via SQL Server in ASP.NET MVC 3

First of all, I don't recommend storing images in the database, especially in a web project. Because file systems can provide static file access and get many benefits such as performance. Storing images in the database has certain limits. But if you must store images into SQL Server, here's how to do it.  SQL Image files are in fact, binary data. SQL Server provides us the image type for …
ASP.NET MVC SQL Server

博客数据库升级手记(二):新数据库的建立及简单数据的导入

前面一片文章讲了如何将Access数据库直接导入SQL Server。但导入完成后,是强烈建议不要使用的。因为里面的object命名都与sql默认的不符,许多T-SQL行为也很诡异,所以我需要重新建立一个数据库,并且把旧的数据导入到新的数据库中。 新数据库的设计 对于新的数据库,这恰好是一次重构和升级的机会,我可以将4年前不合理的表结构设计重构一下(4年前也就是我第一次做这个博客的时候,菜鸟一只)。新的数据库中,博客子系统的设计如下(其实还有很多别的表,但不是给博客主业务用的,以后再讨论): 而旧数据库仅仅只有3张表:Blog、Category、Comment。 对于Category,没有太大的改动,仅仅是增加一个”Name”字段,用来做MVC的路由,而”DisplayName”的功能则和原先数据库是一样的,表示这个分类在页面上显示的名称。 Comment表增加了Email字段,是可选的 …
SQL SQL Server

博客数据库升级手记(一):将Access数据库迁移到SQL Server

本人使用的网站空间送了个200M的SQL Server2008 R2数据库,我不用白不用。所以上周花了很多时间把网站从Access和XML数据存储上迁移到了SQL Server数据库,顺便也升级了一下架构,目前博客子系统已基本完成迁移,最近将逐个迁移页面集系统、留言等模块。 在迁移的过程中,有许多值得记录的经验,在实际项目中非常实用,所以记录下来以备参考。 首先,我的迁移思路是:数据的迁移尽量不通过程序去做,应当采用SQL Server去实现。那么第一步我就得把Access数据库迁移到SQL Server上,这样才能在SQL Server中进行一致的管理和迁移到新的数据库上。 我原来的数据库是Access2007格式的(格式没有太大的关系,2003也可以这样操作)。在Access 2010中的操作方法如下: 1. 在Database Tools选项卡下选择“SQL Server”: 2. …
ACCESS SQL Server

在SQL Server完整版上部署ASP.NET Membership

平时做练习的时候,用的Membership虽然是SQL方式的,但版本是SQL Express,连接的是网站App_Data文件夹里的mdf文件。如果要把同样的Membership部署到SQL完整版上是会爆的。比如你开发的时候自己用的Express,部署到你的主机服务商时,人家当然用的付费购买的完整版。这样就会爆。网上查了些资料,说Membership是SQL Express独有的功能,我不相信。所以自己开荒了一下,并且成功了。分享给大家。 首先,连接网站App_Data目录的mdf文件,确实只有Express版可以。所以完整版的SQL Server必须在数据库引擎里建立数据库,不要放在网站目录下。 建完数据库以后,打开VS2010的命令行环境,运行aspnet_regsql,你会看到下面这个界面。 选择第一项,即在SQL Server里配置Application Services,然后点
ASP.NET Membership SQL Server