1. Отваря се защитната стена. От входящи правила се избира добавяне на правило…
2. Добавя се име на порт и се извършват настройките. След приключване резултата изглежда така:
Накрая се извършва тест дали порта е отворен от сайта http://www.ping.eu
Виж също:
Въведение
Експерти в индустрията и ИТ мениджърите признават, че водеща причина за високи разходи и ограничената отзивчивост към бизнес нуждите са въведените системи, разработени през годините предоставящи информационни услуги, които са кръвоносната система на бизнеса. Тези системи имат високи експлоатационни разходи, не се възползват от днешната по-евтина изчислителни среди и трудно да се поддържат, променят и разширяват. Не е изненада че много отраслови изследвания показват, че модернизирането на наследените приложения е в приоритетите на Изпълнителните директори. Възможностите за такава модернизация включват нов дизайн на ИТ архитектурата, ново разработване, възможности за изнасяне на обслужването или реконструкция, аутсорсинг и отнемане, закупуването на пакети и автоматизирани миграция на съществуващи приложения за съвременните технологии за програмиране, които поддържат широк спектър от варианти за внедряване.
Стойността на старите приложения
Бизнес приложения, разработени в продължение на много години са интегрирали такава голяма част от бизнес знания и отразяват толкова различни практики, че е справедливо да се каже, че бизнес данните и приложенията са цифрови изображения на бизнес процесите. Дори ако погледнем стари и непривлекателни приложения, стойността им в подкрепа на бизнес операциите не може да се отрече. Компаниите са инвестирали значителни средство в тяхното развитие, често в стотици милиони долари. Въпреки че тези приложения са голям актив, те могат да предоставят пречки по отношение на иновациите, гъвкавост на бизнеса и може би най-важното, цената на поддръжка и работа с тях да се окаже с времето неприемливо висока. Тези недостатъци може да достигнат неприемливи нива, че изпълнителни директори и мениджъри да започнат да виждат недостатъци при разрастване на бизнеса, Тогава ИТ директорите са изправени пред предизвикателството да търсят алтернативи.
Алтернативи за модернизация
Първите подходи за модернизация базирани на създаването на нов потребителски интерфейс върху съществуващите приложения показват недостатъци при адресирането на изискването за гъвкавост при бизнес изискванията и понижаването на цената за поддръжка и експлоатация на приложенията. IT мениджърите търсят по пълно решения чрез възможности като:
● Пълно пренаписване с използване на модерни средства
● Придобиване и настройка според нуждите чрез закупуване на готови решения
● Миграция на съществуващия код към модерна технология
Разходите, рисковете, времето за излизане на пазара и ползите от всяка на тези възможности трябва да бъдат оценени внимателно, преди ангажимент към проект за модернизация. Възможността за пълно пренаписване се разглежда често като вариант, защото тя дава най-висока степен на гъвкавост и отговаря на нуждите от гъвкавост на бизнеса. Пренаписването може да се реализира чрез използване на най-добрите практики, модулна архитектура, чист и лесно управляем код и мощни инструменти за разработка. Въпреки това, голямо начинание като пълно пренаписване на системата е вероятно да бъде подценено като предизвикателство и тези проекти често изостават от графика, надминават предвидените разходи и се доставят често с функционални недостатъци. Според прооценка на Standish Group CHAOS Обобщение 2009.32 на сто от проектите за пренаписване са успешни, 44% са оспорени (със закъснение, над бюджета, с по-малко от необходимите функции) и 24 процента се провалят.
Типичните предизвикателства на пълно пренаписване на кода включват:
● Високите разходи: С прости оценка на броя на постигането на функционалност с точково система за постигната функционалност и оценка на разходите може да се оцени, че такъв проект може да излезе
● Висока степен на риск: В проект от такъв мащаб възможността за подценяване, недоразумения и грешки е значителна и от там общия риск от провал висок.
● Бавна разработка и въвеждане: Пренаписване на приложението може да отнеме много години. Допълнително добавянето на нова функционалност забавя процеса на разработка и внедряване. Закупуване и настройване на готово решение често се счита за по-бързо и ниско рисково, но редица експерти предупреждават за скрити разходи и рискове.
Според Джудит Hurwitz.
"… Готовия софтуер всъщност не е готов.
Това е набор от инструменти, набор от шаблони и
процеси, които са свързани помежду си въз основа на
маркетинг и очакване. "
Сравнение между три възможности за модернизиране на бизнес приложение
| Пренаписване | Готов пакет | Миграция | |
| Цена | Висока | Средна | Средна/Ниска |
| Риск | Висока | Среден | Среден |
| Време | Дълго | Средно | Ниско |
| Гъвкавост | Висока | Ниска | Висока |
Готовия софтуер е за по-сложен проект
При него опасностите са сходни с пренаписването на кода плюс:
● Скрити разходи: В допълнение към разходите за лиценз, обучение на персонала и интеграция с други продукти, които са сравнително лесно да се прогнозират, компаниите могат да не успеят да отделят време и усилия, за анализ на функционалността на решението и да припознаят нужните им бизнес изисквания, да подценят разходите за обучение на потребителите и нарушаването на бизнес процесите в дейността им.
● Ограничена гъвкавост в отговор на нуждите на бизнеса:
Нямате пълен контрол, собственост и ноу-хау за работата на сорс кода намалява способността да се отговори бързо на новите изисквания от страна на бизнеса или да се реагира на конкурентни заплахи, да не говорим за пълна зависимост от доставчика на софтуера за корегиране на критични грешки, които могат да повлияят на ежедневните операции.
Третият вариант е да се мигрират ценния код в модерни технологии за програмиране с автоматизирани инструменти за преобразуване.
Това може да създаде възможност за вашия бизнес да се възползва от нови изчислителни платформи и архитектури, при запазване надеждността и функционалността на съществуващата система. Това включва много от предимствата на пълното пренаписване, но с далеч по-малко недостатъци. Този подход дава на организациите пълен контрол на новата система и дава възможност за използване на нови архитектури и инструменти, всички по-кратко време и по-ниска цена и риск, отколкото другите две възможности.
Robert L. Davis SQL Server Magazine InstantDoc ID #136381
Продукт |
За |
Против |
Оценка |
Цена |
Препоръка |
Контакт |
|
SQL Compare 9.0 |
Intuitive; easy to use; robust functionality |
Unwieldy object handling |
3 out of 5 |
Starts at $395; $595 for SQL Compare Professional |
Offers some unique features, but the other tools’ overall feature sets are superior. I recommend using one of the other tools. |
Red Gate Software • 866-997-0397 • http://www.red-gate.com\ |
| ApexSQL Diff |
Customizable; feature-rich; can change project options without rerunning the comparison |
No snapshot backup option; doesn’t support signed procedures and certificates; somewhat overloaded with features |
4 out of 5 |
$399 per user; volume discounts available |
In terms of features, ApexSQL Diff is my favorite tool out of the four. It has some basic problems, but they’re easy to work around. If robust features area must for you, I recommend using this tool. |
ApexSQL • 866-665-5500 • http://www.apexsql.com |
| xSQL Object |
Optional command-line tool; can save all your servers and databases; very readable and visually appealing reports; easy to use |
No native backup option, only a snapshot backup; big learning curve; doesn’t support certificates and signed procedures |
3.5 out of 5 |
Starts at $299 per user without the command-line tool and $399 with the command-line tool; volume discounts available |
xSQL Object is the easiest of the tools to use and has some unique features that really make it stand out. If usability is a key deciding factor for you, this is the tool I recommend. |
xSQL Software • 877-777-9775 • http://www.xsqlsoftware.com |
| Schema Compare in Visual Studio 2010 Ultimate |
Included in Visual Studio; seamless project integration; deeper object comparision than with other tools; command-line tool to programmatically compare schemas and generate change scripts; supports SQLCmd; can create a separate script for each object; fully supports signed procedurse and handles setting the Trustworthy property for the database; can compare dbschema files, databases, or database scripts |
Not available as a standalone tool; requires Visual Studio Ultimate or Premium; interface lacks some functionality |
4 out of 5 |
Contact a retail partner or licensing professional |
Visual Studio’s Schema Compare offers some unique features and comes built in to Visual Studio. If you’re already using Visual Studio 2010 Premium or Ultimate, I definitely recommend using this tool. If not, the features of this tool alone don’t justify the cost of Visual Studio, and I recommend choosing a different tool. |
Microsoft • 800-642-7676 • http://www.microsoft.com |
След тестване на всички инструменти за сравняване няма ясен победител, Всеки от тях има уникални възможности които го правят различен. Visual Studio 2010’s Schema Compare е инструмент чиито употреба може да има значение за разработката. От гледна точка на използваемостта, мой любимец е xSQL Object заради интерфейса подобен на Object Explorer и опростения дизайн. Ако трябва да избирам на база възможности първи е ApexSQL Diff. Единствения който не е чист победител в никой от категориите е SQL Compare 9.0. При избиране на инструмент трябва да определите кои от възможностите имат по-голямо значение за вас.
SQL Compare на Idera.com http://www.idera.com/Product-Video-Tour/SQL-toolbox/SQL-comparison-toolset-Overview/
от Michael Otey
С последните тласъци към облачните услуги от страна на различни доставчици е лесно да се забрави, че наистина има два начина на прилагане на изчислителни облаци. Със сигурност, големите доставчици на облачни услуги натискат по посока на публичните облачни изчисления. Въпреки това, много организации се насочват към изграждане на частни облачни услуги. Публичният облак и частния облак предлагат подобни обещания: намалени разходи за експлоатация, по-голяма еластичност на ресурсите и възможност за самообслужване. Голямата разлика е, че използването на обществения облак изисква лизинг на средства от доставчика на уеб-базирана инфраструктура. За разлика, реализацията на частен облак управлява собствената вътрешно фирменна инфраструктура. Не е изненадващо, че повечето ИТ професионалисти са далеч по-привързани към идеята на частния облак, отколкото към обществените облачни услуги, Причината е че, частния облак въздейства положително за насочване на съществуващата инфраструктура и си запазва жизненоважни аспекти на вашата инфраструктура, като производителност, достъпност и сигурност под контрола на собствения си компания.
Но ако мислите по посока частен облак, то какво той допринася за работата на SQL Server,? SQL Server не е като IIS, където натоварването може да се балансира между няколко сървъра. С други думи, вие не може да добавите допълнително на SQL Server инстанции на Вашия клъстър и да получите подобрена производителност. За SQL Server, клъстерите само предлагат подобрена наличност – не подобрена производителност. Потребителите на SQL Server са свързани с дадена база данни и тази база данни е дадена конфигурация и свойства. Макар че това може да бъде случаят, това не означава,, че можете динамично да се променят наличните изчислителна мощност за SQL Server.
Виртуализацията е ключът към изпълнение на SQL Server в частния облак. SQL Server инстанции в виртуални машини могат да бъдат динамично живо мигрирани към сървъри с по-голяма наличност на ресурси и изчислителна мощност. Освен това, ако SQL Server, например работи на VM, и физическия хост има достатъчно русурси то, възможностите динамично да увеличавате броя на виртуални процесори и RAM, са достъпни. Hyper-V и vSphere поддържат горещо добавяне на RAM и CPU. В допълнение, SQL Server има способността да се преконфигурира да приеме горещо добавените RAM и CPU, без да се изисква спиране. Миграция на живо, горещо добавяне на RAM и CPU дава на SQL Server еластичността, която той му трябва за да бъде напълно функционален компонент в частния облак.
10 неща които трябва абсолютно да направите
Въпреки популярността на метода за разработка Scrum, бързото разработване на приложения (RAD), и редица други техники за бързо разработване, вие все пак трябва да знаете защо създавате базата данни. Първото основно положение е да откриете и разберете причината поради която трябва да съществува базата данни преди да направите каквото и да е. Да кажеш, че целта на базата данни е да “съхранява записи” не е достатъчно. Трябва да разберете бизнес причината заради която базата данни трябва да съществува, преди да започне разработката. The business reason needs to map not only to the technical specifications but also to the business purpose and the business processes that this database will be supporting. For example, will it be a retail point of sale (POS) database, an HR database, or part of a customer relationship management (CRM) package that will help you track customers and manage the sales cycle? You need to know this and many more details about why you’re creating the database before you start the design process.
You can’t create a viable database without software tools, no more than you could build a house without construction tools. So, you’re going to need data modeling software. Data modeling software, also called a CASE tool, is the data modeler’s and data designer’s best friend. A good CASE tool can help you quickly create different types of data models—such as functional decompositions, system and context diagrams, business process and data flow diagrams, and entity relationship models—each of which will help you communicate to your colleagues the visual components of the proposed system you’re building. Some CASE tools have features that enable sharing, coordination, merging, and version control for design teams. Last, but certainly not least, these tools effectively document what you’re doing and, by implication, why you’re doing it.
The cost of entry into the CASE tool market is not insignificant. However, the initial investment will be paid back in terms of shortened time-to-market for database projects and increased knowledge of corporate data and processes. You can read about six different CASE tools in “Comparative Review: Sizing Up Data Modeling Software”. The comparison is based on capability and price, with some indication of how long it might take you to learn the package.
There is one thing to remember, though: No modeling tool will ever replace a person who understands how the business works. The CASE tool will only assist in creating visual representations of the business processes and structure.
Once you understand the overarching reason why you’re doing the database project and you’ve selected a tool that will help you visualize the as-is and to-be environments, you need to do a deep dive into requirements gathering. You need to understand the requirements well enough to be able to create visual representations of data, processes, and systems.
When gathering the requirements, don’t limit yourself to disk drives, virtual machines (VMs), and other technical requirements. Although you need to know the technical requirements, they aren’t pivotal to good database design. The database that you’re creating has to support business operations, so you need to interview company staff members and managers to understand how the business runs. Only then can you get a handle on the requirements that you’ll need to meet in order to create a viable database.
When you’re gathering requirements, you’re going to encounter conflicting business needs—count on it. You’ll have to wear your diplomat hat to get the parties involved to agree on some sort of compromise so that the project can move forward. For more information about gathering business and system requirements, see “Data Modeling”.
My favorite part of database design is modeling the data—that is, creating structures that will hold distinctly different data sets (entities or tables) and representing the relationships between pairs of these data sets. When you’re modeling the data, you’ll have a chance to reaffirm or correct what you found when gathering the requirements.
There’s not enough space here to do a deep dive into the details of how to model data, so I’ll point out the highlights and some constructs that you shouldn’t avoid simply because they seem a little complicated.
Point 1. Based on the answer to why you’re building the database, you need to use either transactional modeling techniques (for an OLTP database) or dimensional modeling techniques (for a relational data warehouse). In the SQL Server Magazine archive, you can find a wealth of information about how to do both types of data modeling. The Learning Path box lists some of those articles.
Point 2. You need to perform data modeling at multiple levels. You should create the following three models:
Point 3. This point is relevant for transactional data models only: You need to normalize the data and model to third normal form (3NF). Failure to model to 3NF in the early stages of design restricts your understanding of how data items interact with each other and most likely will inhibit a full understanding of the business requirements that the database must support. Once you move to later levels of design (e.g., the PDM), you can denormalize back to second normal form (2NF) if you must. Denormalizing a transactional data model isn’t recommended, however. It results in data duplication, which causes insert, update, and deletion anomalies and data synchronization problems. But if you must denormalize (for production efficiencies, perhaps), you’ll at least know exactly where the data duplication will occur and where to expect data synchronization problems. For more information about 2NF, 3NF, and data normalization, see “SQL By Design: Why You Need Database Normalization”.
Point 4. Every transactional database contains four integrities: primary key integrity, referential integrity, domain integrity, and business rules integrity. Whether these integrities are maintained will determine if your data integrity will last beyond the first data load into the database. It will also determine whether the database can support the business rules and requirements that you gathered. For more information about the four integrities, see the Learning Path box.
Point 5. Never use a printed report as a template for a table. This is a common mistake that database design novices often make. Take, for example, an invoice. An invoice contains three distinct data sets:
Yet, in production databases, I’ve seen tables named Invoice that mix components of all three data sets. This is detrimental to data integrity and confusing for the office staff. A printed report is a business requirement and one of the outcomes of your database design. You can create a view, indexed or otherwise, to model a business report, but a business report shouldn’t be used as a template for a database table.
Point 6. Don’t be afraid to include supertype and subtype entities in your design in the CDM and onward. The subtypes represent classifications or categories of the supertype, such as employees who are staff members and employees who are authors. They’re both employees, and they’re both entitled to the employee benefits package. Yet some staff members are authors and some aren’t; authors have additional relationships and attributes that the staff members don’t have. Entities are represented as subtypes when it takes more than a single word or phrase to categorize the entity.
If a category has a life of its own, with separate attributes that describe how the category looks and behaves and separate relationships with other entities, then it’s time to invoke the supertype/subtype structure. Failure to do so will inhibit a complete understanding of the data and the business rules that drive data collection.
When it comes to implementing the supertype/subtype structure, the way you’ve modeled the business requirements and the relationships will determine whether you create one or many tables in the PDM. In this example, if you create an Employee table and an Author table, you could be duplicating data for the individual who is both a staff member and an author. This duplication would lead to nonkey data redundancy within the database, which inevitably causes insert, update, and deletion anomalies, unsynchronized data, and loss of data integrity. For more information about how to properly use supertypes and subtypes, see “Implementing Supertypes and Subtypes”.
Point 7. You must look out for multivalued attributes (MVAs), both expressed and implied. In its latest rendering, Microsoft Access has introduced the MVA structure. In doing so, it has veered away from the principles of relational data modeling and has truly become a nonrelational database. I recently reviewed a real estate system for a business that wanted to upgrade from Access to SQL Server. The database schema was riddled with MVAs. For instance, the building entity had an array of attributes named Picture1, Picture2, Deed of Record, Title, and so on—all within the same table. The owner wasn’t too surprised when he was told that SQL Server wouldn’t support the MVAs because his programmers had been having problems trying to access the attributes that were stored as MVA structures.
Figure 1: An MVA that lists disks
An MVA contains a collection of values for an attribute, as shown by the list of disks (e.g., Disk1Capacity, Disk2Capacity) in the SERVER table in Figure 1. This figure shows the schema for a server room inventory database. The solution to resolving this MVA is to flatten the structure so that each disk has its own row in a newly created table named DISK_DEVICES, as Figure 2 shows. The only constraint on the number of disks that the server can have is the number of bays in the server.
Figure 2: The flattened MVA
MVAs make retrieving summary data extremely difficult. They also cause problems when inserting, updating, and deleting data in a database. You can read more about MVAs in “Multivalued Attributes”.
The whole idea of a relational database is to identify and enforce relationships between entity pairs so that data integrity, as defined in the business rules and regulations and represented in the data models, is maintained. If you have the rule “each order must be associated with a current customer,” you don’t want someone to write a program that lets a person place an order but not get a customer number. No application-level code can circumvent the rule to maintain data integrity.
The three types of relationships—one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N)—are easily transitioned from the data model to the implemented database. For information about how to do this, see “Logical Modeling”.
There are a myriad of reasons why you want to use the proper data type for an attribute. Reduced disk storage space, data accuracy, the ability to join or union two tables together, attribute synchronization with the application layer, and internationalization capability are just a few of the reasons why you want to make sure that you’re defining each attribute’s data type correctly.
Technically, using the correct data type for an attribute speaks to domain integrity, which is one of the four database integrities I mentioned previously. When enforced, domain integrity ensures that an attribute contains only the set of valid values that are relevant and meaningful to that attribute. Many data modeling software packages let you create data domains and use them throughout the model. For example, you can create a Zip+4 domain that’s defined as char(5)+’-'+char(4). As a result of using the Zip+4 domain, every zip code instance in the database is defined with the same data type, instead of some instances defined as char(), some as varchar(), and some as int. Adding a NOT NULL characteristic to the Zip+4 domain further enforces the domain integrity rule that states each occurrence of a zip code in the database must contain a value.
Implementing domain integrity in SQL Server is a manual effort if you don’t have a data modeling package that lets you create and enforce data domains. The tools at your disposal include
For additional ideas on how to enforce domain integrity, see “SQL By Design: The Four Integrities”.
When you’re building the PDM, you should include indexes. This is a bit of a chicken-and-egg situation because until you know how the data is going to be used, you can’t predict with certainty which columns will need indexing. But you’ve already gathered the requirements, so you should be able to make an intelligent guess as to which data should be indexed and how to index it. SQL Server automatically indexes (and clusters, by default) primary key constraints, but it doesn’t automatically index foreign key constraints. Therefore, the first set of indexes you want to create are those for the foreign keys in child tables. The next set would be for those columns on which you’re going to sort. In other words, you want to create indexes for columns that would be in ORDER BY clauses. From the gathered requirements, you should be able to determine which columns will be used for filtering (i.e., in the WHERE clause), so based on your understanding of how many rows are in each table, you can make an intelligent decision regarding which columns to index. For additional information about indexing, see “Indexing Dos and Don’ts”.
If you don’t already have one, adopt a naming convention for all database objects, including tables, columns, indexes, keys, named constraints and relationships, and code. Not having a naming convention can lead to errors in data retrieval and can even inhibit productivity. A database schema is rarely documented and evolves over time; an established naming convention for the objects helps modulate schema confusion. Here are a few rules for creating useful database object names:
When you choose a naming convention, remember that no one convention is perfect, but almost any naming convention is better than none at all. For additional information about naming conventions, see “Seven Deadly Sins”.
Повтаряйте след мен, “Няма да пиша вграден динамичен SQL код в моите клиентски приложения.” Правейки това базата данни попада в риск от непоследователно манипулиране с данните, неточно подаване на данни и неспазване на бизнес логиката, ограниченията и изискванията. Това поставя също така базата данни в риск от SQL проникваща атака. В случай, че не сте чули, термина SQL инжекционна атака преди, това е уязвимост в сигурността която се случва когато един програмен код за манипулиране (в нашия случай T-SQL при SQL сървъра) с данните бъде заменен с друг. Един хакер буквално може да подмени кода по време на изпълнение и да получи достъп да данните в базата. Техниката за това е широко известна и публично достъпна. За допълнителна информация относно SQL инжектирането, виж статията “SQL Injection Attacks on the Rise”.
Най-добрия начин да се избегне несъгласуваност в правилата за обработката на данните и SQL инжекционна атака е програмния код който манипулира данните да се съгранява като запазена процедура в базата данни или CLR обект. Кагата трябва да добавите или модифицирате данните, тогава извиквате подходящата запазена процедура; операцията ще се изпълни по един и същи начин всеки път щом я извикате. За въведение в CLR, виж статията “What’s the CLR and Should I Care?”.
Документирането на работата която вършите е вероятно най-нелюбимото ви занимание. Но ако вървите по основните стъпки които дискутирахме, то вие вече документирате. За сега това не е толкова лошо нали?
If you did a good job discovering the database’s purpose and its requirements, and if you were conscientious while you worked through the myriad steps of the modeling process, then your database design will likely stand up to everything that the organization can throw at it. The database will support all the business requirements and comply with all the integrity, security, and privacy rules and regulations that govern your industry. It’ll perform well and be easy to use and maintain. And as the organization evolves into different lines of business, you’ll be able to easily extend the database’s functionality and easily scale it up and out to store and serve up many times more data than it was originally designed for.
Michael Otey
SQL Server Magazine InstantDoc ID #135931
It’s no surprise that there’s a strong trend to virtualize SQL Server instances—and that the fastest growing virtualization platform is Microsoft Hyper-V. Hyper-V is included in Windows Server 2008 and Windows Server 2008 R2, which makes it easy for organizations not standardized on VMware to adopt Hyper-V for SQL Server. Keep in mind these seven important best practices for implementing SQL Server on Hyper-V.
1. Use servers that support Second Level Address Translation SQL Server databases can be very CPU and I/O intensive applications and the current crop of servers provides a feature that significantly improves the performance of virtual machines (VMs). Second Level Address Translation (SLAT) allows the hardware to take over mapping VM memory to physical memory. (Intel calls it Extended Page Tables—EPT—and AMD calls it Rapid Virtualization Indexing—RVI). Hardware mapping is significantly faster than software mapping, reduces the load on the hypervisor, and provides better scalability.
2. Upgrade to Windows Server 2008 R2 SP1 Windows Server 2008 R2 SP1 offers some really important improvements for VMs, and the most important is support for dynamic memory. Dynamic memory lets the Hyper-V server automatically increase and decrease the memory allocated to a Hyper-V VM while that VM is active. This allows for better scalability and more predictable performance for SQL Server instances running in Hyper-V VMs.
3. Use Multi-Path I/O Multi-Path I/O is a feature that’s supported by Windows Server 2008 and Windows Server 2008 R2. It lets you create multiple redundant paths between the Hyper-V hosts and the SAN storage. Multi-Path I/O is supported for both Fibre Channel and iSCSI storage and can increase VM availability and provide improved performance for SQL Server VMs by maximizing I/O throughput.
4. Install Integration Services in the SQL Server VM Hyper-V supports two types of devices in its VMs: synthetic and emulated. Synthetic devices deliver much better performance than emulated devices. However, to use synthetic devices you must install the guest Integration Services components, which, among other things, provide the device drivers that the guest OS needs to use synthetic devices.
5. Reserve 1GB of RAM for the parent partition When you’re in a server consolidation environment and running multiple workloads on a server, be sure to leave about 1GB of RAM for the Windows Server instance running in the parent partition. This ensures that that host server can run the different VM workloads without paging. If the parent partition is forced to page memory, VM performance is degraded.
6. Use Fixed VHDs for VM storage When you build a VM, you get two choices about the type of virtual hard disk (VHD) to use: dynamic or fixed. Dynamic VHDs are great for test and development scenarios because they require only the actual storage space consumed, but they don’t deliver the same performance as fixed VHDs. Fixed VHDs are still best for production. Note that pass-through disks are another option for SQL Server instances requiring the absolute maximum performance. However, pass-through disks aren’t as flexible as VHDs. They tie the VM to specific storage and provide only slightly better performance than fixed VHDs.
7. Use Separate LUNs/VHDs for Guest OS and Database and Log Storage As with a physical server, when you configure a SQL Server VM you gain performance improvements by splitting the guest OS to a separate VHD. You can also put the SQL Server database and log files on separate VHDs. The different VHDs should then be stored on different LUNs to take advantage of multiple disk spindles.
Newly attached database may become read only if Windows permissions are read only as well. Windows permissions must be changed and the SQL Database service restarted.
Стъпка 1: Разрешаване на TCP/IP
Първо трябва да се укаже на SQL Server Express да слуша съответния TCP/IP, порт.
За целта се прави следното:
1. Стартирате SQL Server Configuration Manager
2. Избира се "Protocols for SQLEXPRESS" нод
Стъпка 2: Разрешаване на TCP/IP
Щракнете дмукратно TCP/IP. Разрешете TCP/IP като метод за свъзване.
В таб “IP Addresses” в последното поле “IPAll – TCP Port” се въвежда порта. Може да изберете 1433.
Като вариант по-долу е даден пример с настройка за Hamachi IP адрес. При конфигурация с Hamachi идете на Network Connections > Advanced > Advanced Settings и подсигурете Hamachi да бъде първия адаптер в списъка.
Стъпка 3: Защитна стена
Включете защитната стена и отворете порта 1433. Чрез сайта http://www.ping.eu се убедете, че порта е отворен.
Сред настройката на мрежовите услуги и отварянето на порта следва да се рестартира SQL Server Express сервиза.
Стъпка 4: Настройка на стринга за връзката и инициализиращи параметри
Създайте файл с разширение udl и го отворете. Изберете вида на връзката както е показано в ляво. В дясно стринга за връзката трябва да укаже адреса на SQL Server.
Например : 87.126.81.245\SQLEXPRESS,1433
При инициализиращите параметри SQL Server позволява да се използват следните мрежови библиотеки при свързване.
В стринга на връзката би трябвало да се укаже типа на библиотеката. Например в този случай "dbmssocn" (без кавички) за TCP/IP Sockets Net-Library.
Стъпка 5: Име и парола
Уверете се че User ID има подходящи разрешения за да изпълнява съответните операции върху базата данни. За препоръчване е да се използва “sa” задължително с парола.
Ако инстанцията е инсталирана в Windows режим без парола използвайте команда за да поставите такава. Например:
Sqlcmd -U login “sa” –P “password” ще създаде парола “password” за “sa”.
Тествайте връзката. След това ще можете да се свържете отдалечено към SQL Express.
Следващата таблица указва валидни имена за ключовете в стринта за връзка.
Name
Application Name
The name of the application, or ‘.Net SqlClient Data Provider’ if no application name is provided.
AttachDBFilename -or- extended properties -or-
Initial File Name
The name of the primary file, including the full path name, of an attachable database.
The database name must be specified with the keyword ‘database’.
Connect Timeout
Connection Timeout 15
The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Current Language
The SQL Server Language record name.
Data Source -or- Server -or- Address -or- Addr -or-
Network Address
The name or network address of the instance of SQL Server to which to connect.
Encrypt ‘false’
When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no.
Initial Catalog -or- Database
The name of the database.
Integrated Security -or- Trusted_Connection ‘false’
When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.
Network Library -or- Net ‘dbmssocn’
The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory) and dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP).
The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, "." or "(local)"), shared memory is used.
Packet Size 8192
Size in bytes of the network packets used to communicate with an instance of SQL Server.
Password -or- Pwd
The password for the SQL Server account logging on (Not recommended. To maintain a high level of security, it is strongly recommended that you use the Integrated Security or Trusted_Connection keyword instead.).
Persist Security Info
‘false’
When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no.
User ID
The SQL Server login account (Not recommended. To maintain a high level of security, it is strongly recommended that you use the Integrated Security or Trusted_Connection keyword instead.).
Workstation ID
the local computer name
The name of the workstation connecting to SQL Server.
Виж също:
Solving SQL Server Connection Problems
Windows 2003 Terminal Services
Конфигуриране на порт 1433 за работа с SQL Server
SQL Server 2005 Index Best Practices
Written By: Tim Ford — 10/6/2009 — 7 comments — print — free stuff — 
 …try SQL Backup pro for faster, smaller, more robust backups. |
Problem
In continuing with our series of tips on Best Practices for SQL Server I’m turning my sights on Maintenance. Specifically in this tip we will be discussing Index maintenance: when, if, why, and how are questions that will be addressed. Many tips here at MSSQLTips.com are devoted to just this topic and most of the detailed steps on how to perform index maintenance are going to be links to tips we’ve previously published. I will also not be presenting what index fragmentation is. If you are looking for information on either topic please refer to the Next Steps section below. What we will focus on is the Best Practices associated with indexing maintenance as a whole. With that understanding, let’s proceed!
Solution
As a SQL Server DBA since the waning days of Microsoft SQL Server 6.5 I’ve been exposed to a wide assortment of SQL instances of not only my creation, but from developers and vendors working without the oversight of a qualified Database Administrator. I’ve read through more database installation documents than I could possibly quantify and I’ve seen some requirements that would make even the coolest lose their composure. Some of the issues I’ve run across are the direct blame of ignorance, laziness, oversight, or hubris on the part of the individual with their hands on the keyboard or vendor developing the requirements document. In a few cases I have to lay the blame at the feet of Microsoft themselves, though only in-so-far that their design has given false impressions as to what the standards are or should be in terms of maintenance. This last comment should be explained by a true story that I played a role in not too long ago.
I installed a database required as the data repository for an application our company purchased from a vendor we’ve purchased many such products from in the past. The SQL requirements questionnaire we had the vendor complete before installation stated that the product could be hosted on a shared environment – meaning we did not need to dedicate a SQL Server instance strictly for this single database. We made a home for it on one of the nodes of our enterprise cluster without any issues and then went about our business. About a month later the users complained of some issue (it’s not pertinent to the discussion here so I will not be delving further) that they were concerned may have been caused by a database issue. The vendor’s support department was contacted and in turn I was engaged to answer a few questions about the SQL environment. The vendor wanted to be assured that I was performing index maintenance once a week. I informed them that I was not, but that I was performing this task nightly, where required to keep index fragmentation in check. It was at this point when one of the most absurd statements about standards and best practices was ever made to me. The tech support individual I spoke with, who just happened to be the vendor’s DBA, stated that was unacceptable and that I need to perform this task weekly, on Sundays at 1:00 am, just as Microsoft requires. If that sounds vaguely familiar to you it is because that is the default value for the schedule associated with the Reorganize data and index pages action in the original SQL 7.0 and SQL 2000 Maintenance Plans. The vendor "Expert" was citing this metric as the standard set forth by Microsoft for when index maintenance should be performed and how frequently it should take place.
So please fellow SQL Server Professionals, do not rely on the Maintenance Plans in SQL Server. They are adequate for a small company who may not have a dedicated IT department or for a home installation of SQL Server. Any scheduled maintenance is better than none at all. If you’re taking the time to read this however, then you most-likely are in a position or in an organization that has outgrown the usefulness of Maintenance Plans.
So when do you remediate your index fragmentation? Like I mentioned above, you do so when the need arises, but on a scheduled basis and off hours if possible. I have a SQL Agent job that runs against each of the databases on each of my instances once per day. It identifies which indexes are fragmented over 15%. It then rebuilds those that are encountering fragmentation in excess of 30% and reorganizes those with fragmentation between 15%-30%. It disregards any small indexes (less than 30 pages). How do I accomplish this? Primarily through the sys.dm_db_index_physical_stats Dynamic Management Function. What follows is a simple query that I use to identify those indexes that meet the criteria I just spelled out for the current database:
SELECT DB_NAME(SDDIPS.[database_id]) AS [database_name], |
The sys.dm_db_index_physical_stats DMF accepts 5 parameters: database_id, object_id, index_id, partition_number, and mode. I limited the results to just the current database (courtesy of the DB_ID() function. I then filtered the results to just those indexes whose fragmentation met the limits I was interested in and whose size were greater than 30 pages. Furthermore, I excluded heaps (tables without clustered indexes) from the result set. I only showed you this query so that the examples in the links below made more sense. For you will see that there are many options available for creating your own index maintenance scripts, yet the core functionality comes down to querying sys.dm_db_index_physical_stats, making note of which indexes meet the criteria for your index fragmentation watch list, and then generating and executing ad-hoc ALTER INDEX statements for rebuilding or reorganizing your indexes accordingly.
In terms of distilling the process of index maintenance down to a list of best practices this is what I recommend:
